Entity Framework will not load related entity when using .Include unless the context knows about the related entity

Question

Using Entity Framework 6.1.x with the default User (ApplicationUser) model that is included when starting a basic Asp.NET MVC project. Related to the question are two more entities: Customer and Organization (both work in progress). Below is the Customer class.

public class Customer
{
    public Customer()
    {
    }

    public int Id { get; set; }

    public string UniqueId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Email { get; set; }

    public Organization Organization { get; set; }
}

Organization can for all purposes be considered a minimal entity with a single Id property.

The following code will not include Organization in the customers collection:

var customers = this.context.Customers.Include(c => c.Organization)
    .Join(this.context.Users.Where(u => u.Id == userId), c => c.Organization.Id, u => u.Organization.Id, (c, u) => c)
    .ToList();

However, it will work when I either add virtual to the Customer.Organization property, or if I let the context know about the organizations by running this.context.Organizations.ToList().

Finally, based on this post that talks about how .Include() will not work if the query changes shape after .Include() has been issued, I wrote the following code:

var customers = this.context.Customers
    .Join(this.context.Users.Where(u => u.Id == userId), c => c.Organization.Id, u => u.Organization.Id, (c, u) => c)
    .AsQueryable<Customer>().Include(c => c.Organization)
    .ToList();

The last code snippet works, so I guess I can use that.

However, it is not as pretty as the first code, but more importantly, I really want to understand why the first code does not work. Is it because of "the shape of the query" is changing? If so, how come it works when I let the context know about the organizations before calling the query?

Answer 1

Lazy Loading

Adding virtual keyword (with the Configuration.ProxyCreationEnabled = true) will enable lazy loading.

When the context is still alive, any programmatically access to navigation property will automatically load the reference if it hasn't been loaded yet.

Local Data

Calling context.Organizations.ToList() will load all organizations from database to memory (in this case Local data). And since it is loaded in the same context, when Local data is updated, all references that are being tracked will be updated too.

Include Limitation

To make your initial code works, you need to change the join result from

(c, u) => c

to

(c, u) => new { Customer = c, Organization = c.Organization }

but the customers result will change into anonymous type collection that contains Customer property. To return the Customer, you need to have additional code like.

.ToList().Select(c => c.Customer).ToList();

The first query will look like.

var customers = this.context.Customers.Include(c => c.Organization)
    .Join(this.context.Users.Where(u => u.Id == userId), c => c.Organization.Id, u => u.Organization.Id, (c, u) => new { Customer = c, Organization = c.Organization })
    .ToList().Select(c => c.Customer).ToList();

The article that you gave was written by the guy who answered this question.