Entity Framework Eager Loading Filter
I have a simple query I want to do like this:
1) Products
have ChildProducts
which have PriceTiers
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
- ChildProduct 1
- Product 1
- ChildProduct 1
- PriceTier 2
- ChildProduct 1
Ideal results:
Set MergeOption
to anything other than NoTracking
to get these results:
- Product 1
- ChildProduct 1
- PriceTier 1
- PriceTier 2
- ChildProduct 1
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};
精彩评论