Include() is ignored in a subquery

Question

Hi there,

 

I have the following scenario:

Customer 1-n Address (e.g. customer can have multiple addresses).

Address n-n Person (e.g. address can have mutliple people in it but also each person can be assigned to mutliple addresses). There is an AddressPerson table/entity linking both.

 

Address has IsDefault (bit) property.

 

Given a customerId, I'm trying to get the customer info, the default address and all the people at that address.

 

var customer =

(from c in db.Customer

where c.Id = customerId

let defaultAddress = (from a in db.Address.Include("AddressPersons.Person")

where a.IsDefault && a.CustomerId == c.Id select c).FirstOrDefault()

select new CustomerContainer

{

Customer = c,

DefaultAddress = defaultAddress

}).ToList();

 

I get back a list of CustomerContainer, with both Customer and DefaultAddress populated. However, DefaultAddress.AddressPersons collection is always empty even though there are both AddressPerson and Person records corresponding to that Address. It seems the Include() statement is ignored.

 

Is this behavior by design or is my query wrong?

 

Please advise,

Dash.

Answer 1

try to use lazy loading:

 

if(!product.dettail.IsLoaded()){

                                               product.detail.Load();

                        }

 

read about http://blogs.msdn.com/jkowalski/archive/2008/05/12/transparent-lazy-loading-for-entity-framework-part-1.aspx

Answer 2

Yes Include is ignored in sub queries but I think (not tested) that you can simulate it like this:

Code Snippet

var customer = 

(from cust in

   (from c in db.Customer

    where c.Id = customerId

    let defaultAddress = (from a in db.Address

                          where a.IsDefault && a.CustomerId == c.Id

      select c).FirstOrDefault()

    select new

    {

        Customer = c,

   DefaultAddress = defaultAddress,

   AddressPersons = from ap in defaultAddress.AddressPersons

                    select new {AP = ap, P = ap.Person}

    }).AsEnumerable()

 select new CustomerContainer {Customer = cust.Customer, DefaultAddress = cust.DefaultAddress}).ToList();

 

 

Answer 3

Hello Dash,

I would also recommend trying the approach Matthieu’s suggests. In general, LINQ to Entities will load from the database whatever information the query asks for in the outermost projection. Since you are already projecting “CustomerContainer” objects from the query, this is just pushing it further to also explicitly ask for the Persons associated with the DefaultAddress.

I think your question warrants an explanation on the behavior of Include:

It may appear that Include could be specified in many different locations in a query. As a matter of fact, the compiler will only complain if the call cannot be statically resolved, therefore you could try placing Include anywhere ObjectQuery is available.

However, at runtime, Include makes sense only in certain cases:

-          Include only applies to items in the query results: objects that are projected at the outermost operation in the query, for instance, the last Select() operation (in your query, you tried to apply Include to a subquery)

-          The type of the results has to be an entity type (not the case in your query)

-          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

Most times, if any of the conditions above is not fulfilled, the span information specified in Include will be ignored.

While a query of the following form will usually work:

Code Snippet

var query = from entity in context.MyEntitySet.Include(“NavProperty”)
            where entity.ConditionProp == someValue
            select entity;

 

 

In general, it is often clearer to express it like this:

Code Snippet

var query = from entity in context.MyEntitySet
            where entity.ConditionProp == someValue
            select entity;

var queryWithSpan = ((ObjectQuery)query).Include(“NavProperty”);

 

 

When Include is applied at the latest opportunity like this, not only the code will express more clearly the true intent of Include (include a navigation path in the query results), but Entity Framework will be able to detect more cases in which Include cannot be applied, and it will throw an exception rather than silently ignore the call.

The downside is that the casting can quickly become quite cumbersome. For that reason, EFExtensions defines an extension method on IQueryable that allows the last line to be written like this:

Code Snippet

var queryWithSpan = query.Include(“NavProperty”);

 

 

Hope this helps,

Diego

 

Answer 4

Is this advice still applicable - I am trying this with the following query

var results = from p in context.Person

select new { person = p, stuff = p.Member};

and it doesn't Include the Member reference on the Person objects and in fact stuff is also null as well - which seems to be contrary to the advice seen here and on this thread where it seems that doing it this way with anonymous types should force eager loading in the query - http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/3f39e481-3c72-4abe-80b7-c92427747011/   .  I am a proponent of Entity Framework in general and am excited about where they are going, but am extremely disappointed with how messed up with how Include is handled in V1 - it really hamstrings how you use LINQ to Entities - I read the explanation as well and feel that the team just missed it on this - from the explanation it sounds like the team was worrying about performance and trying to do an optimization for you instead of usability - transparently dropping Includes is maddening