Entity Framework Eager Loading Filter

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 =开发者_C百科 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?

---

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

---

here is a solution that will give your 'rows' to match your request by using left joins instead of eager loading, and including parent rows where no child rows exist for the filters

var query = from product in Products
                join child_ in ChildProducts on product equals child_.Product into child_join
                from child in child_join.DefaultIfEmpty()
                join tier_ in PriceTiers on child equals tier_.ChildProduct into tier_join
                from tier in tier_join.DefaultIfEmpty()
                where product.Display && product.Category.ID == 1
                where child == null || child.Display
                where tier == null || tier.IsActive
                select new {product, child, tier};