Allow fetching navigation properties when using projections via Include(...)

[davidroth]

In EF6 it is a known limitation that the Include("...") methods will be ignored as soon as the query contains a projection.
According to the following issue this behaviour is by design: https://connect.microsoft.com/VisualStudio/feedback/details/347543/entity-framework-eager-loading-not-working-in-some-projection-scenarios

Generally I agreee that this behaviour is "the right" one and makes perfect sense for many use cases.
However in our application it is a real limitation for some scenarios, so I am going to explain why "Include with Projections" would make sense for us.

We are currently developing a very large LOB application (CRM) for a customer using EF6. This application has the following characteristics:

1. It is really large. In the final stage there will be about 400-600 entities.
2. For each entity we have at least one datagrid and a CRUD-form.
3. We have about ~ 300 users, but only some dozens working concurrently with the application
4. The app is developed,used,deployed etc. by the it-department of the company, no external consumers etc.

Especially because of combination of 1+2+4 we are avoiding DTOS and are-reusing our entities everywhere possible. In a large project with so many entities, DTOs add a remarkable level of (extra) complexity and work (code) to do and this is something we want to avoid.

See also: "Pros and Cons of Data Transfer objects": http://msdn.microsoft.com/en-us/magazine/ee236638.aspx

In this rich-client application, we are heavily using a third party grid component from DevExpress. The nice thing about this grid is that it allows you to just pass an IQueryable source to the grid, and based on this source the grid allows you to perform all sort of operations. That means that grouping, filtering, sorting and all of these operations, even in combination, are possible for the end-user, and there is zero application code required. So this is a very powerful story.

This huge flexibility for the end-user is one of the key-feature of this application.
So let me explain how this generic displaying of entities in grids usually works.

Lets consider the following simple Entity:

public class Customer
{ 
  public string Name { get; set; } 
  // Possibly lots of other properties

  public Country Country { get; set; }
  public User CreationUser { get; set; }
  // Possibly lots of other navigation properties
}

In the grid, this would be displayed the following way:

[Name] | [Country.Name] | [CreationUser.Name]

With the Include(...) extension method this is fairly easy. For each Navigation-Property which is currently visible in the grid, we dynamically generate an include for this property.
So if the user is viewing the customer grid and only has the "Country" Navigation-Property enabled, we can dynamically generate the following query:

ctx.Set<Customer>()
 .Include("Country"); // Note this is added dynamically, depending if this column is visible in the grid

As soon as the user adds the "CreationUser" column to the grid the query get modified to:

ctx.Set<Customer>()
 .Include("Country")
 .Include("CreationUser"); 

This allows us to use our entities in Grids, without creating a DTO object and manually projecting each property. Considering the huge amount of entities we have this saves lots of lots of duplicate code (not only properties, but also attributes etc.)

However, sometimes there are scenarios where we need additional data in our grids which cannot be retrieved from a property (e.x query the comment count for an entity).

If this is the case, we have to create a List-Model and specify the additional properties:

public class CustomerListModel
{
   public int CommentCount { get; set; }
   public Customer Customer { get; set; }
}

In the grid this would be displayed the following way:

[Customer.Name] | [Customer.Country.Name] | [Customer.CreationUser.Name] | [CommentCount]

Now, ideally, I would like to write the query this way:

ctx.Set<Customer>()
 .Include("Country") // Note this can be added dynamically, depending if this column is visible in the grid
 .Include("CreationUser") // Note this can be added dynamically, depending if this column is visible in the grid
 .Select(x => new CustomerListModel()
 { 
     CommentCount = (from cmt in ctx.Set<Comment>() where ..... select cmt).Count(),
     Customer = x.Customer,
 });

However, because Include(..) statements are ignored as soon as Projections are used, the Country and CreationUser Navigation-Property is no longer included within the query.
A way to make this work atm is by adding all Navigation-Properties of the Customer entity to the projecting list model.

public class CustomerListModel
{
   public int CommentCount { get; set; }
   public Customer Customer { get; set; }

   public Country Country { get; set; } // Property of Customer
   public User CreationUser { get; set; } // Property of Customer
}

and then include it in the projection:

 .Select(x => new CustomerListModel()
 { 
     CommentCount = (from cmt in ctx.Set<Comment>() where ..... select cmt).Count(),
     Customer = x.Customer,
     Country = x.Customer.Country,
     CreationUser = x.Customer.CreationUser,
     ... Lots of more navigation properties available!!
 });

This has a lots of drawbacks:

• It is hacky. I need to specify all navigation properties on my ListModel just to workaround an EF limitation
• It is a lot of boilerplate code and I need to sync my ListModel with the Navigation-Properties of my entity.
• The dynamic include is no longer possible since the assignment is hard-coded in the projection. As mentioned earlier, for performance reasons, we only include Navigation-Properties which are visible in the grid (the user can add/remove these at runtime). (Usually only a small handfull of Navigation-Properties are actually visible. This combines great code-reuse with good real-life performance in our scenario.)

Hopefully I could make my point why we would really benefit from being able to include navigation-properties when using projections.

Maybe EF7 could provide an API for forced Navigation-Property includes?

Probably EF7 could just provide an overload to force includes?

ctx.Set<Customer>()
.Include(x => x.Country, IncludeOption.WithProjections)

What are your thoughts? I would be really happy to get some feedback. Is it possible to consider this requirement in your design of EF7 to make it possible?

looking forward,
best regards,
David

[divega]

I followed up on this discussion item that had been sitting on my plate for a while and I confirmed with @anpete that Include() in EF7 works as @davidroth described (and he is now adding a test to prove it).

I haven't been able to track in which exact milestone Include() made it into the code so leaving the closure to @rowanmiller and @anpete.

[divega]

Closing as Beta4 (should be close enough).

[BobbyTable]

@divega sorry if this isnt the right place to ask, but am I reading this issue correctly? I should be able to use Include() and then project to an object and the execution will only include the projected columns/properites? (this is the issue i am facing: http://stackoverflow.com/questions/35316939/ef7-projection-doesnt-eager-load-collections)

[rowanmiller]

@deadZedd I replied on StackOverflow... but this is the feature you want to avoid the n+1 queries #4007.