This should be a simple task that I am having problems coding.
Here is a simplified depiction of the scenario:
I have two tables:
Listing
-ListingId (PK)
ListingKeyword
-ListingId (FK on Listing.ListingId)
-KeywordId
-ListingKeywordId (PK - not really necessary)
They are related as Listing[1-M]ListingKeyword
I want to filter on Listing by ListingKeyword.KeywordId in an "AND" fashion.
So a simple example would be get all Listings with a (KeywordId = 1 And KeywordId = 2)
For some reason my code below will not work. If I do "AddWithOr" it will work as you would expect, BUT if I do with "AddWithAnd", no filter matches a query with 2 or more parameters.
Here is the code (simplified):
DataAccessAdapter adapter = new DataAccessAdapter(true);
EntityCollection<ListingEntity> listings = new EntityCollection<ListingEntity>(new ListingEntityFactory());
RelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(ListingEntity.Relations.ListingKeywordEntityUsingListingId);
PredicateExpression listingFilter = new PredicateExpression();
listingFilter.AddWithAnd(ListingKeywordFields.KeywordId == 1);
listingFilter.AddWithAnd(ListingKeywordFields.KeywordId == 2); //Problem here!
bucket.PredicateExpression.Add(listingFilter);
adapter.FetchEntityCollection(listings, bucket, 0);
So this will not work, even though there are Listings which match KeywordId = 1 and 2 it will display nothing. If I do "AddWithOr" it will display all listings that match.
My only guess is that I must do something different with the way I create the relations, perhaps the order is incorrect?
I figure there must be a simple solution to this as tag/keyword filtering is maybe the most common requirement of web/database applications.
Many thanks for any help!