Using Northwind as an analogy, I am basically trying to find all Orders that have Pickles AND Peanuts on them.

The code I'm using is (filterItems is an ArrayList of Item IDs):

bucket.Relations.Add(OrderEntity.Relations.OrderItemEntityUsingOrderId);
bucket.Relations.Add(OrderItemEntity.Relations.ItemEntityUsingItemId);

filter = new PredicateExpression();

foreach (string item in filterItems) {

    if (filter.Count == 0) {
        filter.Add(ItemFields.ItemId == item);
    }
    else {
        filter.AddWithAnd(ItemFields.ItemId == item);
    }
}

if (bucket.PredicateExpression.Count == 0) {
    bucket.PredicateExpression.Add(filter);
}
else {
    bucket.PredicateExpression.AddWithAnd(filter);
}

I have Orders with these two Items on them but the query is not returning any results. If I change filter.AddWithAnd to filter.AddWithOr, of course I get results. But I'm trying to get Orders with BOTH items on them.

Is there something that I'm fundamentally doing wrong to find all Orders with Item A AND Item B?

Thanks,

Jay

Hey Jay,

Couple of notes:

1. Your code basically translates to: find all orders that have items which are both pickles and peanuts. Since an item can't be both a pickle and a peanut, you will never get any results from this query.

2. I don't think you need any of your if/then logic. In the first case, you can just always use the "Add" method, as it should default to "AddWithAnd". In the second case, an empty (but instantiated) predicate expression will simply be ignored by the framework.

To answer your main question--I believe you need to create two FieldCompareSet Predicates and "AND" them together.

The SQL you are trying to produce is roughly (or exactly ):

DECLARE
    @product1 int,
    @product2 int

SELECT @product1 = 11
SELECT @product2 = 42

SELECT 
    * 
FROM 
    [Orders]
WHERE 
    Orderid IN (SELECT OrderId FROM [Order Details] WHERE ProductId = @product1)
    AND
    Orderid IN (SELECT OrderId FROM [Order Details] WHERE ProductId = @product2)

This should be relatively straightforward with field compare set predicates once you get the hang of them.

HTH,

Phil

Thanks for the help Phil. On note 1, I think I knew that but I didn't know how to fix it. Thanks for the info in Note 2, I've made some changes.