linq-to-sql combine .any expression

Question

I need to filter out parent by property value of child collection. I am doing something like this:

  var results = (from c in db.Customers where  
  c.Orders.Any(o => o.Status = (int)Status.Ordered)
  select c;

It's fine but now I need to filter by 2 values, i.e. take all parent records that have any chilren records that have BOTH values:

  var results = (from c in db.Customers where  
  c.Orders.Any(o => o.Status == (int)Status.Ordered) && (o.Status == (int).Shipped)) 
  select c;

Trying something obvious like this doesn't work.

Answer 1

Of course it doesn't work. You're filtering by orders that satisfy an impossible condition. You're saying: "get me all orders whose status equals Ordered AND equals Shipped at the same time". It's no wonder that there are no such orders. :-)

If you want to get all customers that have both Ordered and Shipped orders, you have no choice but to use Any twice:

var results = (from c in db.Customers where  
    c.Orders.Any(o => o.Status == (int)Status.Ordered) ) 
    &&
    c.Orders.Any(o => o.Status == (int)Status.Shipped) ) 
    select c;