Efcore: Erroneous warning "The Include operation for navigation was ignored"

[mguinness]

Steps to reproduce

Attached is ConsoleApp2.zip which demonstrates the issue.

The issue

When running the following code, the warning "The Include operation for navigation: 'c.State' was ignored because the target navigation is not reachable in the final query results." is shown, but it makes no sense as the navigation is performed and the referenced field is included in the results.

var customers = ctx.Customers.Include(c => c.State).Select(c => new { c.CustomerName, c.State.StateName }).ToList();

Below is the debugging output:

dbug: ConsoleApp2.Program[0]
      Starting application
dbug: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[2]
      Compiling query model: 'from Customers c in {value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ConsoleApp2.Models.Customers]) => Include([c].State)} select new <>f__AnonymousType0`2(CustomerName = [c].CustomerName, StateName = [c].State.StateName)'
dbug: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[3]
      Optimized query model: 'from Customers c in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ConsoleApp2.Models.Customers]) join States c.State in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[ConsoleApp2.Models.States]) on IIF(([c] != null), Property([c], "StateId"), null) equals Convert(Property([c.State], "StateId")) into IEnumerable`1 c.State_group from States c.State in {[c.State_group] => DefaultIfEmpty()} select new <>f__AnonymousType0`2(CustomerName = [c].CustomerName, StateName = IIF(([c.State] != null), [c.State].StateName, null))'
warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[8]
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[8]
      The LINQ expression 'DefaultIfEmpty()' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[8]
      The LINQ expression 'from States c.State in {[c.State_group] => DefaultIfEmpty()}' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
warn: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[6]
      The Include operation for navigation: 'c.State' was ignored because the target navigation is not reachable in the final query results. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'CoreEventId.IncludeIgnoredWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.
dbug: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[5]
      TRACKED: False
      (QueryContext queryContext) => IEnumerable<<>f__AnonymousType0<string, string>> _Select(
          source: IEnumerable<TransparentIdentifier<TransparentIdentifier<Customers, IEnumerable<States>>, States>> _SelectMany(
              source: IEnumerable<TransparentIdentifier<Customers, IEnumerable<States>>> _GroupJoin(
                  queryContext: (RelationalQueryContext) queryContext,
                  source: IEnumerable<ValueBuffer> _Query(
                      queryContext: queryContext,
                      shaperCommandContext: SelectExpression:
                          SELECT "c"."CustomerId", "c"."CustomerName", "c"."StateId", "c.State"."StateId", "c.State"."Abbreviation", "c.State"."StateName"
                          FROM "Customers" AS "c"
                          LEFT JOIN "States" AS "c.State" ON "c"."StateId" = "c.State"."StateId"
                          ORDER BY "c"."StateId"
                      ,
                      queryIndex: default(System.Nullable`1[System.Int32])
                  )
                  ,
                  outerShaper: BufferedEntityShaper<Customers>,
                  innerShaper: BufferedOffsetEntityShaper<States>,
                  innerKeySelector: (States c.State) => (Nullable<long>) long GetValue(
                      queryContext: queryContext,
                      entity: c.State,
                      property: StateId
                  )
                  ,
                  resultSelector: (Customers c | IEnumerable<States> c.State_group) => TransparentIdentifier<Customers, IEnumerable<States>> CreateTransparentIdentifier(
                      outer: c,
                      inner: c.State_group
                  )
                  ,
                  outerGroupJoinInclude: default(Internal.GroupJoinInclude),
                  innerGroupJoinInclude: default(Internal.GroupJoinInclude)
              )
              ,
              collectionSelector: (TransparentIdentifier<Customers, IEnumerable<States>> t0) => IEnumerable<States> DefaultIfEmpty(
                  source: t0.Inner
              )
              ,
              resultSelector: (TransparentIdentifier<Customers, IEnumerable<States>> t0 | States c.State) => TransparentIdentifier<TransparentIdentifier<Customers, IEnumerable<States>>, States> CreateTransparentIdentifier(
                  outer: t0,
                  inner: c.State
              )
          )
          ,
          selector: (TransparentIdentifier<TransparentIdentifier<Customers, IEnumerable<States>>, States> t1) => new <>f__AnonymousType0<string, string>(
              t1.Outer.Outer.CustomerName,
              t1.Inner != null ? t1.Inner.StateName : null
          )
      )

dbug: Microsoft.EntityFrameworkCore.Storage.Internal.SqliteRelationalConnection[3]
      Opening connection to database 'main' on server '..\..\..\test.s3db'.
info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "c"."CustomerId", "c"."CustomerName", "c"."StateId", "c.State"."StateId", "c.State"."Abbreviation", "c.State"."StateName"
      FROM "Customers" AS "c"
      LEFT JOIN "States" AS "c.State" ON "c"."StateId" = "c.State"."StateId"
      ORDER BY "c"."StateId"
dbug: Microsoft.EntityFrameworkCore.Storage.Internal.SqliteRelationalConnection[4]
      Closing connection to database 'main' on server 'C:\Projects\ConsoleApp2\test.s3db'.

Further technical details

EF Core version: 1.0
Operating system: Windows 10
Visual Studio version: VS 2015

[divega]

@mguinness You only need to use Include() when the query returns entities and you want an additional navigation to be traversed and populated. E.g. for your model, the Include() in this query brings states into memory alongside the customers:

``` C#
var customersWithState = ctx.Customers.Include(c => c.State).Select(c => c).ToList();

Or for short:
``` C#
var customersWithState = ctx.Customers.Include(c => c.State).ToList();

In your query you are explicitly projecting individual scalar properties c.CustomerName and c.State.StateName. The State navigation property is traversed explicitly to retrieve the StateName and neither states nor customers are loaded into memory. Hence the call to Include() is superfluous.

[divega]

I am going to leave this bug open however because there are a few unexpected (for me) things showing up in the query plan. I want to make sure this is a query we have optimized further in 1.1.

[divega]

SQL output for 1.1 is as follows (with or without the call to Include()):

SELECT "c"."CustomerId", "c"."CustomerName", "c"."StateId", "c.State"."StateId", "c.State"."Abbreviation", "c.State"."StateName"
FROM "Customers" AS "c"
LEFT JOIN "States" AS "c.State" ON "c"."StateId" = "c.State"."StateId"
ORDER BY "c"."StateId"

[mguinness]

@divega Thanks for explaining, that helps my understanding. It appears that I wasn't the only one caught out regarding this (see issue https://github.com/aspnet/EntityFramework/issues/7144). Maybe a minor text change to the warning would help?

The Include operation for navigation: 'c.State' is unnecessary and
was ignored because the target navigation is not reachable in the final query results. 

[rowanmiller]

Split off a new issue to improve the docs - https://github.com/aspnet/EntityFramework/issues/7162

[mguinness]

@divega I did try removing the Include() when running the following query, but ran into another issue:

var customersWithState = ctx.Customers.Where(c => c.State.Abbreviation == "WA").ToList();

SELECT "c"."CustomerId", "c"."CustomerName", "c"."StateId", "c.State"."StateId", "c.State"."Abbreviation", "c.State"."StateName"
FROM "Customers" AS "c"
LEFT JOIN "States" AS "c.State" ON "c"."StateId" = "c.State"."StateId"
ORDER BY "c"."StateId"

Got warning The LINQ expression '(IIF(([c.State] != null), [c.State].Abbreviation, null) == "WA")' could not be translated and will be evaluated locally.

I would have expected the following SQL, do I need to compose the query differently?

SELECT "c"."CustomerId", "c"."CustomerName", "c"."StateId", "c.State"."StateId", "c.State"."Abbreviation", "c.State"."StateName"
FROM "Customers" AS "c"
LEFT JOIN "States" AS "c.State" ON "c"."StateId" = "c.State"."StateId"
WHERE "c.State"."Abbreviation" = 'WA'
ORDER BY "c"."StateId"

[kgKevGomez]

I think this is still an issue. I'm getting the erroneous warning by doing the following:

Steps to reproduce

c# //Simplified code... IQueryable<ProductRequest> data = dataContext.ProductRequests .Include(r => r.Customer) .Where(c => !c.Deleted); //Other dynamic filters... return Ok(data.ToList());

The navigation property is not actually ignored. I get the desired output which means I can see the customer object serialized in the final payload.

The issue

I just want the 'customer' property to be populated when each item is serialized, so I added the .Include(r => r.Customer) since I'm not doing a custom select.

So I should see the warning only if I do .Select(c => new { ...). and I don't use properties that belong to the included navigation property

Further technical details

Microsoft.AspNetCore.All 2.0.5
Windows 10
Visual Studio Code 1.20.1

@divega @rowanmiller Can this issue be re-opened ?

[smitpatel]

@kgKevGomez - I ran above query and did not get any warning that include was ignored. Please file a new issue with repro code showing the issue you are seeing.