Why doesn't this join work with Entity Framework v4?

Question

I use Entity Framework v4 (code-only). If I do

var result = from person in context.People.Include("Cars")
             select new { person, person.Houses.Count, bar, foo, etc };

then result.First().Cars is null. If I do this way, it works, but I need Houses.Count:

var result = from person in context.People.Include("Cars")
             select person;

It seems Entity Framework v4 doesn't work well with object initializers?

The solution for now is this:

var result = from person in context.People.Include("Cars").Include("Houses")
             select person;

But performance is ridiculous, I need to bring House row!

Answer 1

A shot in the dark..

Does:

var results = from person in context.Person
              let count = person.Houses.Count()
              select new {person, count };

Work? Honestly though, I thought your first query should work.

Answer 2

var result = from person in context.People.Include("Cars")
             select new {  
                 Person = person, 
                 Cars = person.Cars,
                 HouseCount = person.Houses.Count()
             };

Redundant, sure, but Person.Cars will be populated, now.

Answer 3

http://connect.microsoft.com/VisualStudio/feedback/details/527749/join-bug-at-ef4-code-only

Hello Felipe,

When Include is applied to a query, it injects additional “query span” information to the query that is then used later on, when the query is actually executed. The query span information is used to internally rewrite the query so that the entities that can be reached through the specified navigation properties are also brought from the database and materialized.

When other query operators are applied after Include, we try to preserve this query span information. However, the query span information specified in Include will not flow over a query operator that changes the result type of the query.

For instance, In a query like this: var result = from person in context.People.Include("Cars") select new { person, person.Houses.Count, bar, foo, etc };

The result type is changing because of the projection operation (select) at the end and the final result type of the query is no longer the original entity type over which Include was applied, so the query span information is lost. A common recommendation to avoid this is to always apply Include at the end:

var result = 
    from person in context.People
    select new { person, person.Houses.Count, bar, foo, etc };
var resultWithCars =
    result.Include("Cars");

In this case, though, the outcome is that Include will throw an exception at runtime (as opposed to fail silently) because query span cannot really be applied to an anonymous type.

All the behavior described above is by design, but since it is not possible for Include to throw an exception in many cases, we have found this to be a common source of confusion for customers.

If you want to do a projection into an anonymous type like this, and you want to obtain the Cars property, there is actually a very easy way. Simply include the Cars property in the projection, like this:

var result = 
    from person in context.People
    select new { person, person.Cars, person.Houses.Count, bar,

foo, etc };

We are currently considering some improvements to the query span API for future versions that might address issues like this, so your feedback is very appreciated. But since we are not planning improvements for the current release and we keep future improvements in our internal backlog, I will proceed to resolve the current work item.

Thanks, Diego Vega Entity Framework Team