c# - EF Core Linq to SQLite 无法翻译,适用于 SQL Server
问题描述
我有一个 linq 表达式,它在生产数据库上运行良好,但在测试上下文的内存数据库中的 SQLite 上引发错误。我得到的错误说:
The LINQ expression (EntityShaperExpression:
EntityType: Item
ValueBufferExpression:
(ProjectionBindingExpression: Inner)
IsNullable: True ).Price * (Nullable<decimal>)(decimal)(EntityShaperExpression:
EntityType: ISItem
ValueBufferExpression:
(ProjectionBindingExpression: Outer)
IsNullable: False ).Qty' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
linq 表达式:
var locationsQuery = context.DbContext.Locations
.Include(x => x.Check)
.Include(x => x.Scan)
.Include(x => x.EScan)
.ThenInclude(es => es!.Items)
.ThenInclude(isi => isi.Item)
.Where(x => x.ProjectId == query.ProjectId)
.Select(x => x);
然后我有一个投影:
LocationId = entity.Id,
LHA = entity.LHA,
Zone = entity.Zone,
Area = entity.Area,
LocationState = $"DB.{nameof(LocationState)}.{entity.State.ToString()}",
CheckUserId = entity.Check != null ? entity.Check.ScanUserId : (int?)null,
ScanUserId = entity.Scan != null ? entity.Scan.ScanUserId : (int?)null,
CheckUserName = entity.Check != null ? entity.Check.ScanUser.Name : null,
ScanUserName = entity.Scan != null ? entity.Scan.ScanUser.Name : null,
SumPrice = entity.EffectiveScan != null // This cannot be evaluated
? entity.EScan.Items
.Where(x => x.Item != null)
.Sum(x => x.Item!.Price * (decimal)x.Qty)
: null,
SumQty = entity.EScan != null
? entity.EScan.Items
.Sum(x => x.Qty)
: (double?)null
如果我删除 SumPrice 计算,它会按预期工作(就像在生产系统上一样)。我可以对此查询做些什么以在 SqlServer 和 SQLite In memory db 上同样工作?
解决方案
您可能会在生产环境中将代码与 sqllite 以外的某些数据库一起使用。您可能不想根据对开发数据库的依赖来更改代码。在您的上下文类中,在 OnModelCreating 方法中添加以下代码段并从您的代码中删除强制转换。
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
if (Database.ProviderName == "Microsoft.EntityFrameworkCore.Sqlite")
{
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
var properties = entityType.ClrType.GetProperties().Where(p => p.PropertyType == typeof(decimal));
var dateTimeProperties = entityType.ClrType.GetProperties()
.Where(p => p.PropertyType == typeof(DateTimeOffset));
foreach (var property in properties)
{
modelBuilder.Entity(entityType.Name).Property(property.Name).HasConversion<double>();
}
foreach (var property in dateTimeProperties)
{
modelBuilder.Entity(entityType.Name).Property(property.Name)
.HasConversion(new DateTimeOffsetToBinaryConverter());
}
}
}
}
推荐阅读
- python - 如何有效地计算多个列表的共现计数?
- amazon-ec2 - 尽管命令正在 AWS EC2 实例上的终端上运行,但 Cron 作业无法正常工作
- c++ - 用于从 Java 移植到 C++ 的文本字符串的 3 路基数快速排序
- python - FFMPEG:向视频添加多个垂直堆叠的绘图文本
- javascript - Javascript 24 小时到 12 小时
- java - 使用 Java Stream 从文件中实例化对象
- python - 如何在值内转义分隔符
- android-studio - Error when downloading a file with url into internal storage Android Studio
- gmail-api - 为什么我的员工在通过 PipelineDeals 发送电子邮件时收到此消息?
- powerquery - Power Query:查找组内的百分位数