How to filter Include in ObjectQuery

Question

Background:
ObjectQuery<Customer> myCollection = myEntityFrameworkContext.Customer.Include("Order.OrderDetail");

(Each Customer has a collection of Order objects and each Order has a collection of OrderDetail objects)

Question
How can I filter the result or change the query in a way that only those resultsets are returned where OrderDetail with the productId = 2.

something like this: myEntityFrameworkContext.Customer.Include("Order.OrderDetails").Where("it.Order.OrderDetail.productId = 2")

Any help is much appreciated.

Answer 1

I found the solution myself :)

myEntityFrameworkContext.Customer.Include("Order.OrderDetails").Where(customer => customer.Order.Any(order => order.OrderDetail.Any(orderDetail => orderDetail.ProductId ==2)));

Answer 2

Well, I just noticed a problem with this.

Customer is filtered but other tables are returned entirely! (not filtered)

Any idea?

Answer 3

I think this is a bug with the Entity Framework or I don't know how to do it.

Anyway, the only way I found is to change the Object Query direction.

The "many" side of the relationship must come first always:

myEntityFrameworkContext.OrderDetail.Include("Order.Customer").Where(orderDetail.ProductId ==2);

Now only the Order and Customer are returned which are associated with the selected OrderDetail.

Now again I have to Convert the data to the format I need which means Customer.Order.OrderDetail not OrderDetail.Order.Customer