Efcore: Behaviour of ignoring includes/thenincludes

[ole1986]

When using Entity Framework Core (v1.1.0) with Linq and GroupBy clause I noted that the ThenInclude method does not properly receive the objects

Steps to reproduce

```c#
// NOT WORKING EXAMPLE
var query = context.Permission
.Include(p => p.User).ThenInclude(p => p.Group)
.Where(p => p.UserID = 1)
.GroupBy(p = p.User)
.Select(p => new Permission() { User = p.Key, [...] } )
/* p.Key is the User object */

```c#
// WORKING EXAMPLE
var query = context.Permission
                     .Include(p => p.User).ThenInclude(p => p.Group)
                     .Where(p => p.UserID = 1)
                     Select(p => new Permission() { User = p.Key, [...] } ) 
                     /* p.Key is the User object */

``c# // WORKAROUND using GroupBy afterToList()`
var query = context.Permission
.Include(p => p.User).ThenInclude(p => p.Group)
.Where(p => p.UserID = 1)
.ToList() /* run the statement before grouping /
.GroupBy(p = p.User)
.Select(p => new Permission() { User = p.Key, [...] } )
/ p.Key is the User object */

### Details

so, I assume the the `IQueryable<Permission>` later contains a list or IEnumarable of Permission objects with the User object (which works so far) and also the Group included into the User.

**But the Group is always empty when accessing it**

```c#
// run the statement and fetch the Group (which is null but should not)
// BTW: the SQL statement does not contain the Group table at this point according to SQL Server Profiler

var group = query.ToList().ElementAt(0).User.Group;
if(group == null) 
    Debug.WriteLine("I am NULL")

Without the GroupBy of course it is working

Further technical details

NetCore: 1.1.0
EF Core version: 1.1.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2015

[rowanmiller]

@ole1986 could you share the actual code for the query that works (the code listed wouldn't compile)... guessing it's just a copy/paste error from the non-working sample 😄

[ole1986]

attached you can find a working solution (using AspNetCore) containing three test scenarios

You may need to change the appsettings.json with a proper database connection string.
all the important line are located in the Startup.cs (LINE 50-107)

EF-Issue7424.zip

UDATED: Removed bin and obj folder from archive

[smitpatel]

At present the way include works is, if the final projection is materializing the starting entity then include will populate navigation properties. But if that is not the case then include will bring only stuff which is being projected out. For queries like
db.As.Include(a => a.Bs).ThenInclude(b => Cs).ToList()
will have A & B & C populated but for queries like
db.As.Include(a => a.Bs).ThenInclude(b => Cs).Select(a => a.Bs).ToList()
ThenInclude is ignored since is it not directly projected out.

For the reported issue,
In case 1, Group does not appear directly in final projection so it is not populated.
but for case 2, since root entity is being materialized it will be populated.
Case 3 in repro code works since, all the related entities are already loaded in the context and navigations will be fixed up locally.

Removing milestone to discuss what should be behavior of Include & ThenInclude
@anpete @maumar

[jnm2]

The mental model is that you've marked that path to include. It's somewhat surprising to stop including b.Cs.

It's extremely handy to be able to use includes when the final projection is not the original entity.

[rowanmiller]

Just a side note, the current behavior is documented in the Ignored Includes section of https://docs.microsoft.com/en-us/ef/core/querying/related-data#eager-loading.

[jnm2]

Huh, weird that you can link right to the header https://docs.microsoft.com/en-us/ef/core/querying/related-data#ignored-includes but the page doesn't show that.

[jnm2]

I still live in hope that one day I'll be able to select an entity + some calculations in one projection and have working includes on the entity.

[rowanmiller]

Closing as this is expected behavior. If you take over creation of entities, then EF doesn't play a role and will not do includes etc.

[jnm2]

If I include an entity in a projection, that should not be seen as me taking over its creation. It should be created the same as if it was not wrapped in a projection.

[anpete]

@jnm2 Please feel free to create a separate bug for that feature, ideally with some examples of where it is useful.

[smitpatel]

@anpete - Is this something we should consider?

[stevetayloruk]

+1

[yringler]

This would be useful for any case where you have a table with a ton of columns and want

1. Only to load a few columns
2. Only to make one sql query

In other words:

• Include() gives the ability to optimize the number of queries.
• Anonymous types give the ability to optimize the amount of data returned.
  These are not mutually exclusive goals, and I find it strange that the documentation canonizes this as expected behaviour, and not as a logical requirement which was unfortunately not implemented yet.

[rgoliveira]

I'm currently working on optimizing the number of queries executed to generate a report -- trying to ideally run only a single huge one --, and EF is ignoring several of my .Includes, even though I'm projecting the root entity inside an anonymous object. I needed to load all these related entities into the context to avoid extra queries further down the road. Not sure how to work around this.

Edit: I'm actually running EF6, but this seems to be the same problem.

[ole1986]

Doesnt it makes sense (especially when you have huge queries) to run them in parallel anyway?

[rgoliveira]

Depending on your needs, yes. That's the workaround I'm writing at the moment. It might even be faster, though I'm still not convinced that EF is doing something expected, since I'm explicitly saying "include these related entities in the query" and then when I run a .Load() it simply won't populate some of the navigation properties.

In my case, I disabled lazy loading to find extra queries more easily, and a few properties that I've Included ended up null, crashing the app. This doesn't look right (at all) to me.

[rgoliveira]

Ok, so I found out the problem with my code.

Assume that I have an IQueryable grid with some filters already applied to it.

This doesn't work:

grid.Join(EntityA.Include(a => a.EntityB), g => g.IdA, a => a.Id, (g, a) => a).LoadAsync();

The SQL for the above statement will have an inner join for EntityA, _but not for EntityB_.

To fix this, I had to put the Include after the Join's projection:

grid.Join(EntityA, g => g.IdA, a => a.Id, (g, a) => a)
    .Include(a => a.EntityB)
    .LoadAsync();

This will generate a query with inner joins for both EntityA and EntityB, as expected.

For me, both ways make sense, though I can see why they would result in different SQL queries.

[markusschaber]

Even in my case (nested GroupJoin and Join), there's neither a warning nor an exception that the .Include(...) is ignored.

Pulling the .Include(...).ThenInclude(...) out from the Join into the GroupJoin level fails with an InvalidOperationException("System.InvalidOperationException: 'The Include operation 'Include("role.Permissions.Target")' is not supported. 'role' must be a navigation property defined on an entity type.'").

Pulling it out in the top level also fails with a similar exception.

And I need GroupJoins here, as the builtin User, Role and UserRole types do not have Navigation Properties. (Why?)

Apart from that bug, the documentation should clearly state the workaround (pull the .Include()s out of the projection).

Additionally, how to generate the .Include for the IEnumerables generated by GroupJoin or GroupBy?