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

Question

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!

Answer 1

What about:

from product in Products.Include("Skus.PriceHistory")
where product.Skus.PriceHistory.FinancialPeriodID == 244
select product

Include already performs all necessary tasks to fill navigation properties so additional joins for where condition are not needed.

Answer 2

Maybe projection can do this trick?
Take a look at this post.

Answer 3

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