mysql - EF .NET Core 5 异常中的 LINQ 表达式“OUTER APPLY 投影映射”
问题描述
从 .NET Core 2.x 迁移到 .NET Core 5.0 后,我们面临着这个问题。
错误:(添加为代码以提高可读性)
The LINQ expression 'OUTER APPLY Projection Mapping:
(
SELECT e0.Id, e0.FirstName, e0.MiddleName, e0.LastName
FROM Employees AS e0
WHERE (((e0.Status != 4) && EXISTS (
Projection Mapping:
SELECT 1
FROM FunctionRoles AS f0
WHERE t.Id == f0.SchoolId)) && (e0.FunctionRoleId == (Projection Mapping:
EmptyProjectionMember -> 0
SELECT TOP(1) f1.Id
FROM FunctionRoles AS f1
WHERE (t.Id == f1.SchoolId) && (f1.Name == 'Manager')))) && (t.Id == e0.SchoolId)
) AS t0' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
代码部分:
using (var dbContext = _contextProvider.CreateContext())
{
var school = dbContext.Set<Domain.Model.School>()
.Where(s => s.Id == schoolId)
.Select(s => new SchoolSummaryDto
{
//... Some other properties
DocumentTemplates = s.DocumentTemplates != null ? s.DocumentTemplates.Select(a => new DocumentTemplateDto
{ Id = a.Id, Description = a.Description, SchoolId = a.SchoolId, FileName = a.FileName, DocumentTemplateTypeId = a.DocumentTemplateTypeId }).ToList() : new List<DocumentTemplateDto>(),
// This below chunk is causing problem.
Signers = s.Employees != null ? s.Employees.AsEnumerable().Where(
e => e.Status != PersistentStatusEnum.Removed &&
e.FunctionRoleId == s.FunctionRoles.AsEnumerable().Single(
b => b.Name == FunctionRolesEnum.Manager.ToString()).Id).AsEnumerable().Select(
a => new NameValueType { Id = a.Id, Name = string.Format("{0} {1} {2}", a.FirstName, a.MiddleName, a.LastName) }).ToList() : new List<NameValueType>(),
// .. Error chunk ends here
ContactPerson = s.ContactPerson,
Email = s.Email,
PhoneNumber = s.PhoneNumber,
SchoolId = s.Id,
SchoolName = s.Name,
Website = s.Website,
IsEnabled = s.IsEnabled,
IsRegistered = s.IsRegistered
}).FirstOrDefault();
}
我尝试了什么:
根据这些 Microsoft 链接,不支持Breaking Changes和Queryable projection,我尝试并AsEnumerable()
相应地应用了更改,如您在上面看到的。
现在需要做哪些改变?
图书馆和环境:
- 数据库 => MySql
- 库 => Pomelo.EntityFrameworkCore.MySql (5.0.0-alpha.2) Nuget 链接
我感觉这个 MySql 库导致问题或EF Core 5 重大更改。
编辑1:
public class FunctionRole:AuditableEntity
{
public string Name { get; set; }
public string Description { get; set; }
public Guid SchoolId { get; set; }
public virtual School School { get; set; }
}
public class School:AuditableEntity
{
public bool IsRegistered { get; set; }
public bool IsEnabled { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public string Website { get; set; }
public string PhoneNumber { get; set; }
public string ContactPerson { get; set; }
public string ActivationCode { get; set; }
public virtual ICollection<FunctionRole> FunctionRoles { get; set; }
public virtual ICollection<DocumentTemplate> DocumentTemplates { get; set; }
}
解决方案
我认为它适用于 EF 2.x,因为它在客户端默默地评估这个查询。
考虑重写查询:
var query =
from s in dbContext.Set<Domain.Model.School>()
where s.Id == schoolId
from r in s.FunctionRoles
.Where(b => b.Name == FunctionRolesEnum.Manager.ToString())
.Take(1).DefaultIfEmpty()
select new SchoolSummaryDto
{
//... Some other properties
DocumentTemplates = s.DocumentTemplates
.Select(a => new DocumentTemplateDto
{
Id = a.Id,
Description = a.Description,
SchoolId = a.SchoolId,
FileName = a.FileName,
DocumentTemplateTypeId = a.DocumentTemplateTypeId
})
.ToList(),
Signers = s.Employees
.Where(e => e.Status != PersistentStatusEnum.Removed
&& e.FunctionRoleId == r.Id)
.Select(a => new NameValueType
{
Id = a.Id,
Name = a.FirstName + " " + a.MiddleName + " " + a.LastName
})
.ToList(),
ContactPerson = s.ContactPerson,
Email = s.Email,
PhoneNumber = s.PhoneNumber,
SchoolId = s.Id,
SchoolName = s.Name,
Website = s.Website,
IsEnabled = s.IsEnabled,
IsRegistered = s.IsRegistered
};
var school = query.FirstOrDefault();
您不必检查空值,AsEnumerable
不需要,string.Format
不能翻译为 SQL。
推荐阅读
- angular6 - 未捕获的错误:无法解析组件的所有参数:([object Object]?)
- tensorflow - 如何在张量流对象检测中使用指数学习率?
- r - 在 R 中确定列表组件的类
- c++ - 无法获取进程 ID 4 (ntoskrnl.exe) 的可执行路径
- swift - Segue 删除 tabBarItem.badgeValue
- python - 使用 Python 获取隐藏文件的文件统计信息(大小、创建时间...)
- matlab - 线性回归码
- angularjs - 如何根据AngularJS中的按钮选择显示重复下拉
- java - int 数据类型如何存储 oracle 的 NUMBER(10) 字段?
- javascript - 有没有办法使用 ramda 将参数传递给 JavaScript 中的谓词?