Invalid column name when using EF Core filtered includes-CodePudding

I came across this error when modifying a DB first project (using fluent migrator) and scaffolding the EF context to generate models. I have reproduced it by making a code-first simplification. This means that I can't accept answers that suggest modifying the annotations or fluent configuration, because this will be deleted and recreated on the next migration and scaffold.

The simplified idea is that a device has:

many attributes
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

I can't figure out why this is working for a number of rows but not working for a single row.

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

I'm not even sure where to start looking, ideas welcome!

Edit

bug report: https://github.com/dotnet/efcore/issues/26585
repro: https://github.com/thinkOfaNumber/efcore-5-test

CodePudding user response：

Looks like you have hit EF Core query translation bug, so I would suggest to seek/report it to EF Core GitHub issue tracker.

From what I can tell, it's caused by "pushing down" the root query as subquery because of the Take operator (which is basically what First method is using in the second case). This somehow messes up the generated subquery aliases and leads to invalid SQL.

It can be seen by comparing the generated SQL for the first query

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]

and for the second (or just inserting .Where(d => d.DeviceId == deviceId).Take(1) before Select in the first):

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]

Note the usage of [t] in the first SELECT [t].[HistoryId]... inside the OUTER APPLY, which in the fist query is alias to the inner Histories subquery in FROM clause, while in second it is alias to the outer Devices subquery, which of couse have no columns mentioned in the error message. Apparently in the second case [t0] should have been used.

Since it is a bug, you have to wait it to be fixed. Until then, the workaround I could suggest is to explicitly execute row limiting operator (First) outside of the EF Core query context, e.g.

var device = _apiContext.Devices.AsNoTracking()
    .Include(d => d.Histories.OrderByDescending(h => h.DateFrom).Take(1))
    .ThenInclude(h => h.Location)
    .Include(d => d.Attributes)
    .Where(d => d.DeviceId == deviceId) // instead of .First(d => d.DeviceId == deviceId)
    .AsEnumerable() // switch to client evaluation (LINQ to Objects context)
    .First() // and execute `First` here
    .ToModel();