Menu
I have a solution where I have created self tracking entities using the RTM templates. I have split the entities and context between 2 projects so that I can reuse the type definitions as I plan to run client/server via WCF.
One of my service methods is required to return a graph of "Product" objects with child objects of "ProductSku" and these in turn have child objects of "ProductPrice". The selection criteria will be on the "Name" property of the "Product" object, and the "FinancialPeriodID" property of the "ProductPriceObject". For now, I am not including the name in the search, but I am having problems bringing back the graph.
If I simply perform the following query (note, this syntax is taken from LinqPad rather than the actual application code)...
from product in Products.Include("Skus.PriceHistory")
select product
... then I am able to retrieve the full object graph for the items that I require, of course at this point there is no filter.
If instead, I introduce the filter as follows...
from product in Products.Include("Skus.PriceHistory")
join sku in ProductSkus on product.ID equals sku.ProductID
join price in ProductPrices on sku.ID equals price.ProductSkuID
where price.FinancialPeriodID == 244
select product
... what I am expecting to get back is the "Product" objects, the child "ProductSku" objects (which are in the "Skus" collection of the "Product") and their "ProductPrice" objects (which are in the "PriceHistory" collection of the "ProductSku") - but I only get back the "Product" objects, the "Skus" collection is empty.
I have also tried coding the query as ...
from product in Products.Include("Skus.PriceHistory")
from sku in product.Skus
from price in sku.PriceHistory
where price.FinancialPeriodID == 244
select product
... but this makes no difference either.
Clearly, I must be doing something wrong. Can anybody shed any light on what that something is as I have been at this for some hours now going around in circles!
551
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.
While lazy loading delays the initialization of a resource, eager loading initializes or loads a resource as soon as the code is executed. Eager loading also involves pre-loading related entities referenced by a resource.
Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed.
Lazy loading will produce several SQL calls while Eager loading may load data with one "more heavy" call (with joins/subqueries). For example, If there is a high ping between your web and sql servers you would go with Eager loading instead of loading related items 1-by-1 with lazy Loading.
Maybe projection can do this trick?
Take a look at Linq filter collection with EF
194
Edit:
What about:
from product in Products.Include("Skus.PriceHistory")
where product.Skus.Any(s => s.PriceHistory.Any(p => p.FinancialPeriodID == 244))
select product
Include already performs all necessary tasks to fill navigation properties so additional joins for where condition are not needed. What is even more important any manual join or projection will change the shape of the query and Include will not be used.
Also beware that where condition filters only products. It will not filter data loaded by Include - you will get all products with at least one sku having price history with financial period id 244 but those products will have all skus and price histories loaded. EF currently does not support filtering on include. If you need filtered relations as well you have to execute separate queries to get them.
39
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
