8

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!

Improve this question
0

4 Answers 4

Reset to default
1

Maybe projection can do this trick?

Take a look at Linq filter collection with EF

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, but I really want to be minimising the number of object types that I am passing around. I already have a perfectly good "Product" type, I just want the eager loading to work properly so that the child objects are populated.
1

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.

Improve this answer

2 Comments

Sorry, "Skus" and "PriceHistory" are both collections, so it is not possible to navigate all the way down the path in a single statement. I fear there is no option but to include the joins using either the join or the from as shown in the two examples. Thanks anyway.
This might not have helped @MartinRobins, but it helped me out in my own project! +1 This syntax worked for me (perhaps totally different setup than original question, I don't know...)
0

Having Include is not a guarantee to have eager loading and it could be ignored silently because of the following reason. It is better explained in this thread. You can manually select the table you like to load e.g.

var result = from product in Products.Include("Skus.PriceHistory") from sku in product.Skus from price in sku.PriceHistory where price.FinancialPeriodID == 244 select new { p=product, pricehistory = product.Skus.PriceHistory}; var products = results.select(pph => pph.product);

https://social.msdn.microsoft.com/Forums/en-US/76bf1f22-7674-4e1e-85d3-68d29404e8db/include-is-ignored-in-a-subquery?forum=adodotnetentityframework

  • Include only applies to items in the query results: objects that are projected at the outermost operation in the query.
  • The type of the results has to be an entity type.
  • The query cannot contain operations that change the type of the result between Include and the outermost operation (i.e. a GroupBy() or a Select() operation that changes the result type)
  • The parameter taken by Include is a dot-delimited path of navigation properties that have to be navigable from an instance of the type returned at the outermost operation
Improve this answer

Comments

-1

Self-tracking entities are not enabled to perform lazy loading. That's why collection are not empty with default entity generation and not with STE. In fact your Include never loads related entities if you use them on the query. Now your L2E query is not correct. I think that you want to do something like this:

from p in (from product in Products select new { Product = product, Skus = from sku in product.Skus select new { Sku = sku, Prices = from price in sku.Prices where price.FinancialPeriodID == 244 select price } }).AsEnumerable() select p.Product;

Hope that helps

Matthieu

Improve this answer

2 Comments

I am not trying to use lazy loading; I am trying to use eager loading. If I remove the where clause, or base it only on the top level object, I get all of the objects properly. It is only when I use the where clause based on properties of the child objects that the "Include" gets ignored.
Hi Martin, have you found a solution, I have the same problem

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.