LINQ Query Using Navigation Properties Creates Multiple SELECT Statements

Question

I have a POCO Domain Entity class that contains convenient methods for navigating to related records. I am using the AdventureWorks2008R2 database to demonstrate what I'm trying to accomplish. All of these queries can be run in LINQPad to monitor the generated SQL statements.

SalesOrderHeaders.Where(s => s.SalesOrderID == 43659) .Single().SalesOrderDetails 

This statement creates 2 SQL statements. One for writing SalesOrderHeader and one for getting SalesOrderDetails. Now consider this statement, which will go to an additional related table:

 SalesOrderHeaders.Where(s => s.SalesOrderID == 43659) .Single().SalesOrderDetails.Select(s => s.SpecialOfferProduct) 

After receiving one SalesOrderHeader record, the domain class will contain a convenience property similar to this:

 public IQueryable<SpecialOfferProduct> SpecialProducts { get { return SalesOrderDetails.Where(sod => sod.OrderQty > 3) .Select(s => s.SpecialOfferProduct) .AsQueryable(); } } 

This statement creates several SELECT statements: one for each entry in SpecialOfferProduct. My question is: why don't navigation properties produce a single SELECT statement? This is a huge performance issue because it generates a lot of unnecessary chatter. I could use LINQ SQL syntax, but this is only when creating the original query using the repository. In this case, I have an instance of the SalesOrderHeader object and I do not have access to the Context or repository inside the class. Is there a way to get it to create a single SELECT statement using JOINs?

If this is not the case, I was thinking of creating an additional method in my repository to populate these properties. The problem is that I have 2 steps: 1 to retrieve the SalesOrderHeader object, and then to populate additional properties with the appropriate LINQ statement, which will force the JOIN syntax.

Answer 1

As the comment says, you need the Include method:

 SalesOrderHeaders.Include(s => s.SalesOrderDetails .Select(d => d.SpecialOfferProduct)) .Where(s => s.SalesOrderID == 43659) .Single().SalesOrderDetails 

this will combine the required data (in SQL) and add navigation properties.

Note that you cannot use syntax like

 .Include(s => s.SalesOrderDetails.Where(sod => sod.OrderQty > 3) .Select(d => d.SpecialOfferProduct)) 

Which would seem to partially populate SalesOrderDetails . Requests for changes to the EF team to implement this, but so far this has not been done.

Another point is that it is useless to return SpecialProducts as IQueryable , because subsequent queries in the collection will not be translated into SQL anyway. You can access a property only in operations inside memory, and not in linq-to-enitites queries (EF cant translates the property into SQL).