LINQ表达式无法转换求助：EF分组查询投影报错排查

问题描述 #

我正在编写以下LINQ查询：

locations = from s in this._dbContext.Sublocations.AsNoTracking()
            join w in this._dbContext.WOWorkCenterAssignments.AsNoTracking() on s.ID equals w.ID                                
            group w by s.LocationID
            into g
            select new EAMSubSite
            {
                  LocationId = g.Key,
                  Workcenters = g.Select(x => new WorkcenterInfo { Id = x.ID, Name = x.Name }).ToList()
            };

运行时出现如下错误：

The LINQ expression '(GroupByShaperExpression: KeySelector: (s.LocationID),  ElementSelector:(EntityShaperExpression: 
    EntityType: WorkCenterAssignment
    ValueBufferExpression: 
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False ) )
    .Select(x => new WorkcenterInfo{ 
        Id = x.ID, 
        Name = x.Name 
    }
    )' 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.

解决方法 #

方式1：提前投影字段后分组（推荐） #

EF Core无法直接将分组后对实体的投影转换为SQL，可在join后先投影出所需字段，再进行分组和最终转换：

locations = from s in this._dbContext.Sublocations.AsNoTracking()
            join w in this._dbContext.WOWorkCenterAssignments.AsNoTracking() on s.ID equals w.ID
            // 提前投影需要的字段，避免直接操作实体
            select new 
            {
                LocationID = s.LocationID,
                WorkcenterId = w.ID,
                WorkcenterName = w.Name
            }
            group new { WorkcenterId = w.ID, WorkcenterName = w.Name } by LocationID into g
            select new EAMSubSite
            {
                LocationId = g.Key,
                Workcenters = g.Select(x => new WorkcenterInfo { Id = x.WorkcenterId, Name = x.WorkcenterName }).ToList()
            };

方式2：显式启用客户端评估 #

若数据量较小，可将join后的结果拉到客户端内存中再处理，但此方法会加载更多数据到内存，需谨慎使用：

locations = (from s in this._dbContext.Sublocations.AsNoTracking()
             join w in this._dbContext.WOWorkCenterAssignments.AsNoTracking() on s.ID equals w.ID
             select new 
             {
                 LocationID = s.LocationID,
                 WorkcenterId = w.ID,
                 WorkcenterName = w.Name
             })
            // 切换到客户端评估
            .AsEnumerable()
            .GroupBy(item => item.LocationID)
            .Select(g => new EAMSubSite
            {
                LocationId = g.Key,
                Workcenters = g.Select(item => new WorkcenterInfo { Id = item.WorkcenterId, Name = item.WorkcenterName }).ToList()
            });

关键原因 #

原查询中分组的是完整的WorkCenterAssignment实体，EF Core无法将分组后对该实体的投影转换为对应的SQL语句。提前投影字段后，分组操作的是简单匿名类型，EF Core能正确生成SQL完成后续转换。

内容的提问来源于stack exchange，提问作者StarLord