Entity Framework Eager Loading Filter

Question

I have a simple query I want to do like this:

1) Products have ChildProducts which have PriceTiers
2) I want to get all the Products that have a Category with a ID of 1 and Display = true.
3) I want to then include all the ChildProducts that have Display = true.
4) And then include the PriceTiers that have IsActive = true.

From what I have read, EF does not support Eager Loading with filters, so the following will not work:

ProductRepository.Query.IncludeCollection(Function(x) x.ChildProducts.Where(Function(y) y.Display).Select(Function(z) z.PriceTiers.Where(Function(q) q.IsActive))).Where(Function(x) x.Categories.Any(Function(y) y.ID = ID)))

Any suggestions?

Answer 1

Start from the bottom up, meaning, apply filter on the PriceTier object and its parents, and include its parents (C# sorry, but hopefully you get the point):

repository.PriceTiers
  .Include("ChildProduct.Product") // eager load parents
  .Where(priceTier => 
    priceTier.IsActive &&
    priceTier.ChildProduct.Display &&
    priceTier.ChildProduct.Product.ID == 1 &&
    priceTier.ChildProduct.Product.Display)
  .AsEnumerable() // execute SQL statement
  .Select(priceTier => 
    priceTier.ChildProduct.Product) // return products rather than price tiers

(Note: priceTier => in C# is the same as Function(priceTier) in VB.NET)

MergeOption should ideally be set to something other than NoTracking when executing the query. Otherwise, EF will not ensure that an object that appears multiple times in the result set of the query is only materialized once, such as a Product or ChildProduct:

Unwanted results: PriceTier 1 and 2 have the same parents, but the parents have been materialized multiple times - once for each PriceTier.

• Product 1
  • ChildProduct 1
    • PriceTier 1
• Product 1
  • ChildProduct 1
    • PriceTier 2

Ideal results: Set MergeOption to anything other than NoTracking to get these results:

• Product 1
  • ChildProduct 1
    • PriceTier 1
    • PriceTier 2