Entity Framework 4: Eager Loading (Include) with filters using Self Tracking Entities

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 Linq开发者_JAVA百科Pad 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!

Maybe projection can do this trick?

Take a look at Linq filter collection with EF

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.

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

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