sql-server - 使用 EF Core 过滤包含时的列名无效
问题描述
我在修改 DB 第一个项目(使用 fluent 迁移器)和搭建 EF 上下文以生成模型时遇到了这个错误。我通过进行代码优先的简化来复制它。这意味着我不能接受建议修改注释或流畅配置的答案,因为这将在下一次迁移和脚手架时被删除并重新创建。
简化的想法是设备具有:
- 许多属性
- 表示设备随时间变化的许多历史记录
- 每个历史条目都有一个可选位置
IOW 您可以将设备移动到某个位置(或没有位置)并随着时间的推移对其进行跟踪。
我想出的用于模拟的代码优先模型如下:
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>();
}
运行以下查询以选择所有设备可以正常工作。我正在使用过滤的包含仅选择此“视图”的最新历史记录:
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();
效果很好,但是当我尝试使用相同的 ID 仅选择一个设备时,包括:
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();
我收到以下错误:
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
我不明白为什么这适用于多行但不适用于单行。
为了完整起见,ToModel()
这只是返回 POCO 的扩展方法。
我什至不确定从哪里开始寻找,欢迎提出想法!
编辑
解决方案
更新:该错误已在 EF Core 6.0 中修复,因此下一个仅适用于 EF Core 5.0。
看起来您遇到了 EF Core 5.0 查询翻译错误,因此我建议您将其搜索/报告给 EF Core GitHub 问题跟踪器。
据我所知,这是由于Take
运算符将根查询“下推”为子查询引起的(这基本上First
是第二种情况下使用的方法)。这会以某种方式混淆生成的子查询别名并导致无效的 SQL。
对比第一次查询生成的SQL可以看出
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]
对于第二个(或只是在第一个.Where(d => d.DeviceId == deviceId).Take(1)
之前插入):Select
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]
请注意在第一个查询中的用法,[t]
在第一个查询中是内部SELECT [t].[HistoryId]...
子查询 in子句的别名,而在第二个查询中是外部子查询的别名,这当然没有在错误消息中提到的列。显然在第二种情况下应该使用。OUTER APPLY
Histories
FROM
Devices
[t0]
由于它是一个错误,因此您必须等待它被修复。在那之前,我可以建议的解决方法是First
在 EF Core 查询上下文之外显式执行行限制运算符 (),例如
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();
推荐阅读
- swift - 添加第一个元素后,字典中的 Swift Set 值不会增长
- flutter - Flutter:语音命令打开页面
- r - 情节在一页上有几个小图
- python - 确定一个值是否在字典的列表值中
- java - 使用 Itext7 读取文件时重复文本
- maven - 在 Maven 中有一个空的父项目是最佳实践吗?
- api - 如何在 Dell Boomi 中创建一个进程,该进程将从一个数据库中获取数据,然后将数据发送到 SaaS
- android - CMake:Android Studio 原生活动项目在项目根目录外找不到第三方库
- python - 尝试将日期字符串转换为纪元格式。在特定环境中未考虑时区
- kubernetes - 有没有办法禁用 nginx-ingress-controller 默认后端并使用静态页面来处理错误(4xx 和 5xx)?