Menu
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?
239
Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method. For example, the queries below will load blogs and all the posts related to each blog. Include is an extension method in the System.
Lazy loading in Entity Framework is the default phenomenon that happens for loading and accessing the related entities. However, eager loading is referred to the practice of force-loading all these relations.
Entity Framework Classic Include The Include method lets you add related entities to the query result. In EF Classic, the Include method no longer returns an IQueryable but instead an IncludeDbQuery that allows you to chain multiple related objects to the query result by using the AlsoInclude and ThenInclude methods.
Lazy loading means delaying the loading of related data, until you specifically request for it. When using POCO entity types, lazy loading is achieved by creating instances of derived proxy types and then overriding virtual properties to add the loading hook.
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.
Ideal results:
Set MergeOption to anything other than NoTracking to get these results:
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
