EF .NET Core 5异常中的LINQ表达式'OUTER APPLY投影映射 [英] The LINQ expression 'OUTER APPLY Projection Mapping in EF .NET Core 5 Exception

问题描述

从.NET Core 2.x迁移到.NET Core 5.0之后，我们面临着这个问题.

After migrating from .NET Core 2.x to .NET Core 5.0, we are facing this problem.

错误:(已添加为CODE，以提高可读性)

Error: (Added as a CODE for better readability)

 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链接，不支持可查询的投影，如上所示，我尝试并相应地应用了 AsEnumerable()更改.

What I tried: As per these Microsoft links, Breaking Changes and Queryable projection not supported, I tried and applied changes AsEnumerable() accordingly as you can see above.

现在需要进行哪些更改?

What changes are required now?

图书馆与环境:

1. 数据库=>MySql的
2. 库=>Pomelo.EntityFrameworkCore.MySql(5.0.0-alpha.2)我感觉到这个MySql库正在引起问题，或者是 EF Core 5造成了重大变化.

   I sense either this MySql library is causing issue or EF Core 5 breaking changes.

   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，因为它在客户端静默评估此查询.

   I think it works with EF 2.x, because it silently evaluates this query on the client side.

   考虑重写查询:

    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();
   

   您不必检查null，不需要 AsEnumerable ， string.Format 不能翻译成SQL.

   You don't have to check for nulls, AsEnumerable is not needed, string.Format can be not translatable to the SQL.

   这篇关于EF .NET Core 5异常中的LINQ表达式'OUTER APPLY投影映射的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！