Filtering Aggregate root entity and child entity by a property on the child entity

Hope that someone out there can help with this!

I'll give an example based on the standard Order-->OrderLine-->Product rather than the actual situation to make it easier to explain!

Basically, I want to run a query that returns all orders for which there is an order line containing a TV. Simple enough:

IEnumerable<Ord开发者_高级运维er> orders;
          using (var context = new DataContext())
          {
              var source =
                  context.Orders.Include("OrderLines").Include(
                      "OrderLines.Product");

              orders= source.Where(o => o.OrderLines.Where(ol => ol.Product.Name == "TV")).ToList();
          }
          return orders;

This works in the sense that I get the correct collection of Order entities, but when I use look at each Order's collection of OrderLines it contains all OrderLines not just those containing at TV.

Hope that makes sense.

Thanks in advance for any help.

I does make sense in that the query is fulfilling your original criteria "to return all orders for which there is an order line containing a TV", each order will of course have all the orderlines. The filter is only being used to select the Orders, not the OrderLines.

To retrieve just the OrderLines containing TV from an Order you'd use the filter again, thus:

var OrderLinesWithTV = order.OrderLines.Where(ol => ol.Product.Name == "TV");

The main point is to know if you need to keep (or not) a reference to the order header in the filtered lines. I.e. do you want the list of all the orders with a TV, and more precisely only their TV lines ? or do you want all the TV lines nevermind their order header ?

You seem to prefer the first option. Then the best solution would certainly be

var relevantOrders = orders.Where(order => order.OrderLines.Any(ol => ol.Product.Name == "TV"))

to get the relevant orders, and then, for each order in relevantOrders :

order.OrderLines.Where(ol => ol.Product.Name == "TV")

to consider only the TV lines.

Other techniques would result in a loss of information or force you to build a new orders collection similar to the initial one but double-filtered on the headers and on the lines, which seems fairly bad as far as elegance and performance is concerned.