filtered include generates invalid SQL with First() but not with ToList()

[thinkOfaNumber]

File a bug

When I use a filtered include and then First() on the parent query, EF Core 5 generates invalid SQL. I am scaffolding from a db-first project, however I reproduced this on a code-first project as well, which I've detailed below. My repro is here: https://github.com/thinkOfaNumber/efcore-5-test (I will spend a few minutes making it smaller).

I have recently posted the same here: https://stackoverflow.com/questions/69880136/invalid-column-name-when-using-ef-core-filtered-includes

This seems a similar-but-different problem to this bug: #8823

Include your code

The simplified idea is that a device has:

• many attributes (since removed from my github repo as it's unnecessary)
• many histories representing changes to the device over time
  • each history entry has an optional location

IOW you can move a device around to locations (or no location) and keep track of that over time.

The code-first model I came up with to simulate this is as follows:

    public class ApiContext : DbContext
    {
        public ApiContext(DbContextOptions<ApiContext> options) : base(options) { }

        public DbSet<Device> Devices { get; set; }
        public DbSet<History> Histories { get; set; }
        public DbSet<Location> Locations { get; set; }
    }

    public class Device
    {
        public int DeviceId { get; set; }
        public string DeviceName { get; set; }

        public List<History> Histories { get; } = new List<History>();
        public List<Attribute> Attributes { get; } = new List<Attribute>();
    }

    public class History
    {
        public int HistoryId { get; set; }
        public DateTime DateFrom { get; set; }
        public string State { get; set; }

        public int DeviceId { get; set; }
        public Device Device { get; set; }

        public int? LocationId { get; set; }
        public Location Location { get; set; }
    }

    public class Attribute
    {
        public int AttributeId { get; set; }
        public string Name { get; set; }

        public int DeviceId { get; set; }
        public Device Device { get; set; }
    }

    public class Location
    {
        public int LocationId { get; set; }
        public string LocationName { get; set; }

        public List<History> Histories { get; } = new List<History>();
    }

Running the following query to select all devices works fine. I'm using a filtered include to only select the most recent history for this "view":

    var devices = _apiContext.Devices.AsNoTracking()
        .Include(d => d.Histories.OrderByDescending(h => h.DateFrom).Take(1))
        .ThenInclude(h => h.Location)
        .Include(d => d.Attributes)
        .Select(d => d.ToModel()).ToList();

that works fine, however when I try and select only one device by ID using the same includes:

    var device = _apiContext.Devices.AsNoTracking()
        .Include(d => d.Histories.OrderByDescending(h => h.DateFrom).Take(1))
        .ThenInclude(h => h.Location)
        .Include(d => d.Attributes)
        .First(d => d.DeviceId == deviceId)
        .ToModel();

I get the following error:

	Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'LocationId'.
	Invalid column name 'HistoryId'.
	Invalid column name 'DateFrom'.
	Invalid column name 'LocationId'.
	Invalid column name 'State'.
	   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
	   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
	   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
	   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
	   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
	   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
	   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
	   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
	   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
	   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
	   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
	   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
	   at System.Data.Common.DbCommand.ExecuteReader()
	   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
	   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
	   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
	   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
	   at System.Linq.Enumerable.Single[TSource](IEnumerable`1 source)
	   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
	   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
	   at System.Linq.Queryable.First[TSource](IQueryable`1 source, Expression`1 predicate)
	   at efcore_test.App.PrintSingleDevice(Int32 deviceId) in C:\Users\Iain\projects\efcore-5-bug\efcore-test\App.cs:line 44
	   at efcore_test.Program.<>c__DisplayClass1_0.<Main>b__4(App app) in C:\Users\Iain\projects\efcore-5-bug\efcore-test\Program.cs:line 28
	   at efcore_test.Program.RunInScope(IServiceProvider serviceProvider, Action`1 method) in C:\Users\Iain\projects\efcore-5-bug\efcore-test\Program.cs:line 35
	   at efcore_test.Program.Main(String[] args) in C:\Users\Iain\projects\efcore-5-bug\efcore-test\Program.cs:line 28
	ClientConnectionId:1418edb2-0889-4f4d-9554-85344c9a35a9
	Error Number:207,State:1,Class:16

For completeness, ToModel() is just an extension method to return a POCO.

Include verbose output

The debug output for queries is as follows:

.First(d => d.DeviceId == deviceId) debug output:

info: 9/11/2021 10:59:40.350 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Entity Framework Core 5.0.12 initialized 'ApiContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
dbug: 9/11/2021 10:59:40.718 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression: 
      'DbSet<Device>()
          .AsNoTracking()
          .Include(d => d.Histories
              .OrderByDescending(h => h.DateFrom)
              .Take(1))
          .ThenInclude(h => h.Location)
          .Include(d => d.Attributes)
          .First(d => d.DeviceId == __deviceId_0)'
dbug: 9/11/2021 10:59:40.721 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'Device.Histories'.
dbug: 9/11/2021 10:59:40.724 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'Device.Attributes'.
dbug: 9/11/2021 10:59:40.729 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'History.Location'.
warn: 9/11/2021 10:59:40.738 RelationalEventId.MultipleCollectionIncludeWarning[20504] (Microsoft.EntityFrameworkCore.Query) 
      Compiling a query which loads related collections for more than one collection navigation either via 'Include' or through projection but no 'QuerySplittingBehavior' has been configured. By default Entity Framework will use 'QuerySplittingBehavior.SingleQuery' which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'
dbug: 9/11/2021 10:59:40.752 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression: 
      'queryContext => new SingleQueryingEnumerable<Device>(
          (RelationalQueryContext)queryContext, 
          RelationalCommandCache.SelectExpression(
              Projection Mapping:
              SELECT t.DeviceId, t.DeviceName, t1.HistoryId, t1.DateFrom, t1.DeviceId, t1.LocationId, t1.State, t1.LocationId0, t1.LocationName, a.AttributeId, a.DeviceId, a.Name
              FROM Projection Mapping:
              (
                  SELECT TOP(1) d.DeviceId, d.DeviceName
                  FROM Devices AS d
                  WHERE d.DeviceId == @__deviceId_0
              ) AS t
              OUTER APPLY Projection Mapping:
              (
                  SELECT t.HistoryId, t.DateFrom, t.DeviceId, t.LocationId, t.State, l.LocationId AS LocationId0, l.LocationName
                  FROM Projection Mapping:
                  (
                      SELECT TOP(1) h.HistoryId, h.DateFrom, h.DeviceId, h.LocationId, h.State
                      FROM Histories AS h
                      WHERE (t.DeviceId != NULL) && (t.DeviceId == h.DeviceId)
                      ORDER BY h.DateFrom DESC
                  ) AS t0
                  LEFT JOIN Locations AS l ON t.LocationId == l.LocationId
              ) AS t1
              LEFT JOIN Attribute AS a ON t.DeviceId == a.DeviceId
              ORDER BY t.DeviceId ASC, t1.DateFrom DESC, t1.HistoryId ASC, t1.LocationId0 ASC, a.AttributeId ASC), 
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, Device>, 
          efcore_test.Data.ApiContext, 
          False, 
          False
      )
          .Single()'
dbug: 9/11/2021 10:59:40.757 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command) 
      Creating DbCommand for 'ExecuteReader'.
dbug: 9/11/2021 10:59:40.759 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (2ms).
dbug: 9/11/2021 10:59:40.762 RelationalEventId.ConnectionOpening[20000] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opening connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 10:59:40.764 RelationalEventId.ConnectionOpened[20001] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opened connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 10:59:40.766 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[@__deviceId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[DeviceId], [t].[DeviceName], [t1].[HistoryId], [t1].[DateFrom], [t1].[DeviceId], [t1].[LocationId], [t1].[State], [t1].[LocationId0], [t1].[LocationName], [a].[AttributeId], [a].[DeviceId], [a].[Name]
      FROM (
          SELECT TOP(1) [d].[DeviceId], [d].[DeviceName]
          FROM [Devices] AS [d]
          WHERE [d].[DeviceId] = @__deviceId_0
      ) AS [t]
      OUTER APPLY (
          SELECT [t].[HistoryId], [t].[DateFrom], [t].[DeviceId], [t].[LocationId], [t].[State], [l].[LocationId] AS [LocationId0], [l].[LocationName]
          FROM (
              SELECT TOP(1) [h].[HistoryId], [h].[DateFrom], [h].[DeviceId], [h].[LocationId], [h].[State]
              FROM [Histories] AS [h]
              WHERE [t].[DeviceId] = [h].[DeviceId]
              ORDER BY [h].[DateFrom] DESC
          ) AS [t0]
          LEFT JOIN [Locations] AS [l] ON [t].[LocationId] = [l].[LocationId]
      ) AS [t1]
      LEFT JOIN [Attribute] AS [a] ON [t].[DeviceId] = [a].[DeviceId]
      ORDER BY [t].[DeviceId], [t1].[DateFrom] DESC, [t1].[HistoryId], [t1].[LocationId0], [a].[AttributeId]
fail: 9/11/2021 10:59:40.779 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command) 
      Failed executing DbCommand (11ms) [Parameters=[@__deviceId_0='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SELECT [t].[DeviceId], [t].[DeviceName], [t1].[HistoryId], [t1].[DateFrom], [t1].[DeviceId], [t1].[LocationId], [t1].[State], [t1].[LocationId0], [t1].[LocationName], [a].[AttributeId], [a].[DeviceId], [a].[Name]
      FROM (
          SELECT TOP(1) [d].[DeviceId], [d].[DeviceName]
          FROM [Devices] AS [d]
          WHERE [d].[DeviceId] = @__deviceId_0
      ) AS [t]
      OUTER APPLY (
          SELECT [t].[HistoryId], [t].[DateFrom], [t].[DeviceId], [t].[LocationId], [t].[State], [l].[LocationId] AS [LocationId0], [l].[LocationName]
          FROM (
              SELECT TOP(1) [h].[HistoryId], [h].[DateFrom], [h].[DeviceId], [h].[LocationId], [h].[State]
              FROM [Histories] AS [h]
              WHERE [t].[DeviceId] = [h].[DeviceId]
              ORDER BY [h].[DateFrom] DESC
          ) AS [t0]
          LEFT JOIN [Locations] AS [l] ON [t].[LocationId] = [l].[LocationId]
      ) AS [t1]
      LEFT JOIN [Attribute] AS [a] ON [t].[DeviceId] = [a].[DeviceId]
      ORDER BY [t].[DeviceId], [t1].[DateFrom] DESC, [t1].[HistoryId], [t1].[LocationId0], [a].[AttributeId]
dbug: 9/11/2021 10:59:40.781 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closing connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 10:59:40.783 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closed connection to database 'API_DEV' on server '.\SQLSERVER2019'.
'efcore-test.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.11\System.Diagnostics.StackTrace.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'efcore-test.exe' (CoreCLR: clrhost): Loaded 'C:\Program Files\dotnet\shared\Microsoft.NETCore.App\5.0.11\System.Reflection.Metadata.dll'. Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
fail: 9/11/2021 10:59:40.852 CoreEventId.QueryIterationFailed[10100] (Microsoft.EntityFrameworkCore.Query) 
      An exception occurred while iterating over the results of a query for context type 'efcore_test.Data.ApiContext'.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Invalid column name 'LocationId'.
      Invalid column name 'HistoryId'.
      Invalid column name 'DateFrom'.
      Invalid column name 'LocationId'.
      Invalid column name 'State'.
         at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
         at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
         at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
         at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
         at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
         at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
         at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
         at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
      ClientConnectionId:5342bd4b-1566-41be-b3f8-1958bd9aecbb
      Error Number:207,State:1,Class:16
Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in Microsoft.EntityFrameworkCore.Relational.dll
An unhandled exception of type 'Microsoft.Data.SqlClient.SqlException' occurred in Microsoft.EntityFrameworkCore.Relational.dll
Invalid column name 'LocationId'.
Invalid column name 'HistoryId'.
Invalid column name 'DateFrom'.
Invalid column name 'LocationId'.
Invalid column name 'State'.

And the successful .Select(d => d.ToModel()).ToList(); output:

info: 9/11/2021 11:02:22.346 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Entity Framework Core 5.0.12 initialized 'ApiContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None
dbug: 9/11/2021 11:02:22.680 CoreEventId.QueryCompilationStarting[10111] (Microsoft.EntityFrameworkCore.Query) 
      Compiling query expression: 
      'DbSet<Device>()
          .AsNoTracking()
          .Include(d => d.Histories
              .OrderByDescending(h => h.DateFrom)
              .Take(1))
          .ThenInclude(h => h.Location)
          .Include(d => d.Attributes)
          .Select(d => d
              .ToModel())'
dbug: 9/11/2021 11:02:22.700 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'Device.Histories'.
dbug: 9/11/2021 11:02:22.705 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'Device.Attributes'.
dbug: 9/11/2021 11:02:22.718 CoreEventId.NavigationBaseIncluded[10112] (Microsoft.EntityFrameworkCore.Query) 
      Including navigation: 'History.Location'.
warn: 9/11/2021 11:02:22.803 RelationalEventId.MultipleCollectionIncludeWarning[20504] (Microsoft.EntityFrameworkCore.Query) 
      Compiling a query which loads related collections for more than one collection navigation either via 'Include' or through projection but no 'QuerySplittingBehavior' has been configured. By default Entity Framework will use 'QuerySplittingBehavior.SingleQuery' which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'
dbug: 9/11/2021 11:02:22.831 CoreEventId.QueryExecutionPlanned[10107] (Microsoft.EntityFrameworkCore.Query) 
      Generated query execution expression: 
      'queryContext => new SingleQueryingEnumerable<Device>(
          (RelationalQueryContext)queryContext, 
          RelationalCommandCache.SelectExpression(
              Projection Mapping:
              SELECT d.DeviceId, d.DeviceName, t0.HistoryId, t0.DateFrom, t0.DeviceId, t0.LocationId, t0.State, t0.LocationId0, t0.LocationName, a.AttributeId, a.DeviceId, a.Name
              FROM Devices AS d
              OUTER APPLY Projection Mapping:
              (
                  SELECT t.HistoryId, t.DateFrom, t.DeviceId, t.LocationId, t.State, l.LocationId AS LocationId0, l.LocationName
                  FROM Projection Mapping:
                  (
                      SELECT TOP(1) h.HistoryId, h.DateFrom, h.DeviceId, h.LocationId, h.State
                      FROM Histories AS h
                      WHERE (d.DeviceId != NULL) && (d.DeviceId == h.DeviceId)
                      ORDER BY h.DateFrom DESC
                  ) AS t
                  LEFT JOIN Locations AS l ON t.LocationId == l.LocationId
              ) AS t0
              LEFT JOIN Attribute AS a ON d.DeviceId == a.DeviceId
              ORDER BY d.DeviceId ASC, t0.DateFrom DESC, t0.HistoryId ASC, t0.LocationId0 ASC, a.AttributeId ASC), 
          Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, Device>, 
          efcore_test.Data.ApiContext, 
          False, 
          False
      )'
dbug: 9/11/2021 11:02:22.846 RelationalEventId.CommandCreating[20103] (Microsoft.EntityFrameworkCore.Database.Command) 
      Creating DbCommand for 'ExecuteReader'.
dbug: 9/11/2021 11:02:22.848 RelationalEventId.CommandCreated[20104] (Microsoft.EntityFrameworkCore.Database.Command) 
      Created DbCommand for 'ExecuteReader' (1ms).
dbug: 9/11/2021 11:02:22.850 RelationalEventId.ConnectionOpening[20000] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opening connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 11:02:22.852 RelationalEventId.ConnectionOpened[20001] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Opened connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 11:02:22.854 RelationalEventId.CommandExecuting[20100] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[DeviceId], [d].[DeviceName], [t0].[HistoryId], [t0].[DateFrom], [t0].[DeviceId], [t0].[LocationId], [t0].[State], [t0].[LocationId0], [t0].[LocationName], [a].[AttributeId], [a].[DeviceId], [a].[Name]
      FROM [Devices] AS [d]
      OUTER APPLY (
          SELECT [t].[HistoryId], [t].[DateFrom], [t].[DeviceId], [t].[LocationId], [t].[State], [l].[LocationId] AS [LocationId0], [l].[LocationName]
          FROM (
              SELECT TOP(1) [h].[HistoryId], [h].[DateFrom], [h].[DeviceId], [h].[LocationId], [h].[State]
              FROM [Histories] AS [h]
              WHERE [d].[DeviceId] = [h].[DeviceId]
              ORDER BY [h].[DateFrom] DESC
          ) AS [t]
          LEFT JOIN [Locations] AS [l] ON [t].[LocationId] = [l].[LocationId]
      ) AS [t0]
      LEFT JOIN [Attribute] AS [a] ON [d].[DeviceId] = [a].[DeviceId]
      ORDER BY [d].[DeviceId], [t0].[DateFrom] DESC, [t0].[HistoryId], [t0].[LocationId0], [a].[AttributeId]
info: 9/11/2021 11:02:22.862 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [d].[DeviceId], [d].[DeviceName], [t0].[HistoryId], [t0].[DateFrom], [t0].[DeviceId], [t0].[LocationId], [t0].[State], [t0].[LocationId0], [t0].[LocationName], [a].[AttributeId], [a].[DeviceId], [a].[Name]
      FROM [Devices] AS [d]
      OUTER APPLY (
          SELECT [t].[HistoryId], [t].[DateFrom], [t].[DeviceId], [t].[LocationId], [t].[State], [l].[LocationId] AS [LocationId0], [l].[LocationName]
          FROM (
              SELECT TOP(1) [h].[HistoryId], [h].[DateFrom], [h].[DeviceId], [h].[LocationId], [h].[State]
              FROM [Histories] AS [h]
              WHERE [d].[DeviceId] = [h].[DeviceId]
              ORDER BY [h].[DateFrom] DESC
          ) AS [t]
          LEFT JOIN [Locations] AS [l] ON [t].[LocationId] = [l].[LocationId]
      ) AS [t0]
      LEFT JOIN [Attribute] AS [a] ON [d].[DeviceId] = [a].[DeviceId]
      ORDER BY [d].[DeviceId], [t0].[DateFrom] DESC, [t0].[HistoryId], [t0].[LocationId0], [a].[AttributeId]
dbug: 9/11/2021 11:02:22.874 RelationalEventId.DataReaderDisposing[20300] (Microsoft.EntityFrameworkCore.Database.Command) 
      A data reader was disposed.
dbug: 9/11/2021 11:02:22.876 RelationalEventId.ConnectionClosing[20002] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closing connection to database 'API_DEV' on server '.\SQLSERVER2019'.
dbug: 9/11/2021 11:02:22.878 RelationalEventId.ConnectionClosed[20003] (Microsoft.EntityFrameworkCore.Database.Connection) 
      Closed connection to database 'API_DEV' on server '.\SQLSERVER2019'.

Include provider and version information

EF Core version: 5.0.12
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 Home 21H1 19043.1288
IDE: Microsoft Visual Studio Community 2019 Version 16.11.5

[maumar]

likely fixed by #24491

[thinkOfaNumber]

thanks, they all seem like 6.0.0 fixes, any knowledge of this being fixed for EF core 5.x?

[roji]

@thinkOfaNumber there is little chance of the fixes getting backported to 5.0 at this point; the change is non-trivial and would risky breaking other things, and with 6.0 now out, 5.0 will be out of support in 6 months anyway. I'd highly recommend considering upgrading to 6.0, which is a long-term support release (3 years).

[thinkOfaNumber]

I will eventually upgrade to ef-core 6 but that requires .net core 6 which I don't quite have access to yet in production. Plus there are a bunch of breaking changes I have to get through.

So if filtered includes with eager loading was never and is never going to be supported in 5.x there should at least be a note in the documentation stating as much.

[thinkOfaNumber]

Hi @ajcvickers, which bug is this a duplicate of? Thanks.

[smitpatel]

Duplicate of #17337 for the root cause.

[thinkOfaNumber]

Thanks @smitpatel

It looks like #17337 is fixed in 6.x? So does that mean this bug won't be fixed in the (albeit small) remaining support period for efcore 5?

If that's the case I highly recommend this needs to be detailed in the documentation, as 5.x is still an officially supported version.

Would I open an issue here for the documentation or how would I go about that?

thanks

[smitpatel]

The bug won't be fixed in 5.0 release. There are several scenarios with filtered include which works correctly in 5.0 release. Writing in documentation that it is not supported is misleading and incorrect. Given LINQ query has infinite permutation possible, we don't expect every single scenario to work in every release. We strive to make work and document if things don't work for major scenarios where multiple people are running into issue. This issue doesn't fall into such case (yet) and documentation relating to this will provide negative value (apart from the issue how would you explain customer that what doesn't work and how can they relate to it when they are writing queries from scratch). It is different from someone running into same issue and trying to figure out the root cause. This issue will serve purpose of figuring out for future customers if they run into same issue. If we see a lot of customers hitting this, we will consider documenting something.

[thinkOfaNumber]

I disagree with almost everything you're saying. I'm not asking filtered includes to be marked as "not supported" in general! However you have a major limitation with it, and noting it could save hours of trying and testing, looking for bugs, stack overflow, etc. It seems you're trying to hide it. I get that software has bugs, I get that you have another major release which makes 5.x obsolete, but is sweeping it under the carpet the correct ethos?

Given LINQ query has infinite permutation possible, we don't expect every single scenario to work in every release.

Really? generating invalid SQL is ok? I've never been about to do that in many years of using EF... Not to say it's never been possible but I've never come across it till now.

We strive to make work and document if things don't work for major scenarios

I think it's a pretty major scenario that is documented to be able to be used like this. The documentation literally has the orderby ... skip example in it. Are you saying adding .First() instead of .ToList() is not a major scenario that many people will try?

where multiple people are running into issue. This issue doesn't fall into such case (yet)

You're making this too easy for me! This is a duplicate of #17337 which has these other issues mentioned by those non-existent other people finding the same or similar problems: #18738 #19763 #19947 #17809. I didn't even try but if I did I'm sure I could find more on the web in general.

and documentation relating to this will provide negative value

Saving developers time by not making them hunt around on stack overflow, search and raise bugs, expect the feature to work, this is all negative value?

(apart from the issue how would you explain customer that what doesn't work and how can they relate to it when they are writing queries from scratch).

Easily. Perhaps under this paragraph:

Supported operations are: Where, OrderBy, OrderByDescending, ThenBy, ThenByDescending, Skip, and Take.

Add

While this can be used for returning multiple entities with .ToList(); a bug with Entity Framework (version) means that you can't use this with single entity results such as .Single() or .First(). For more information etc. etc.

Or alternatively you could put it under the final .ThenInclude(post => post.Tags.OrderBy(postTag => postTag.TagId).Skip(3)).ToList() code example.

I don't even care if you don't use the word "bug".

You (docs.microsoft.com) have the capacity to mark documentation as relevant to specific versions, and I see this a lot, so this need not be even mentioned in the 6.x documentation.

That's probably enough said by me, I don't need to argue this forever. I appreciate you reading this far. Thanks.