Filtering Linq to Entity on Lazy Loading 3 levels Nested Query

Question

Assuming a Entity Framework, in a LazyLoading context.

We have 3 entities:

• Product(which has many Order Details)
• OrderDetails(which has many Details)
• Detail

The following query brings all Products with Name=Books. And to each of these products loads all the OrderDetails which OrderDetail.Quantity>5.

var query = anEntityManager.Products.Where(p => p.Name == "Books")
.Select(p => new { Product = p, OrderDetails = p.OrderDetails.Where(od => od.Quantity >       5) });
var results = query.ToList();
var products = results.Select( x => x.Product);

My Problem is that the Details of each OrderDetail are NOT being retrieved from DB. How can I make an Include in this query so Details are also loaded from DB in the same query?

Answer 1

I think you need to extend your projection:

var query = anEntityManager.Products.Where(p => p.Name == "Books")
    .Select(p => new
    {
        Product = p,
        OrderDetails = p.OrderDetails.Where(od => od.Quantity > 5),
        Details = p.OrderDetails.Where(od => od.Quantity > 5)
                                .SelectMany(od => od.Details)
    });
var results = query.ToList();
var products = results.Select( x => x.Product);

Using Include in a projection is not supported, so this (a bit ugly) code is the only way I know of to get the result in one database query.

You can probably also use Select instead of SelectMany (Details would then be an IEnumerable<IEnumerable<Detail>> instead of a flat IEnumerable<Detail>) because you are throwing away the projected properties anyway - except the Product property.