LINQ Query Using Navigation Properties Generates Multiple SELECT Statements

c# entity-framework linq linq-to-entities sql

Question

I have a POCO Domain Entity class that contains convenience methods to navigate to related records. I am using the AdventureWorks2008R2 database to demonstrate what I am attempting to accomplish. All of these queries can be run in LINQPad to observe the SQL statements generated.

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

This statement produces 2 SQL statements. One for the SalesOrderHeader record and one to retrieve the SalesOrderDetails. Now consider this statement that navigates to an additional related table:

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

After retrieving a single SalesOrderHeader record, the domain class would contain a convenience property like this:

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

This statement produces multiple SELECT statements: one for each record in SpecialOfferProduct. My question is: why don't the 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 that is only when making the original query using the Repository. In this case I have an instance of a SalesOrderHeader object and don't have access to the Context or the Repository inside the class. Is there way to force it to create a single SELECT statement using JOINs?

If there is not a way to do it I was thinking of creating an additional method in my Repository to populate these properties. The problem is then I have 2 steps: 1 to retrieve the SalesOrderHeader object, then another to populate the additional properties using the appropriate LINQ statement that would force the JOIN syntax.

1
4
8/24/2013 3:30:50 AM

Accepted Answer

As said in the comment you need the Include method:

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

this will join the required data (in SQL) and populate the navigation properties.

Note, however, that you can't use syntax like

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

Which seemingly would partly populate SalesOrderDetails. There are change requests to the EF team to implement this, but so far, this hasn't been done.

Another side note is that it's useless to return SpecialProducts as IQueryable because subsequent queries on the collection won't get translated to SQL anyway. You can only access the property in in-memory statements in the first place, not in linq-to-enitites queries (EF cant translate the property into SQL).

2
8/24/2013 11:27:10 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow