.NET 3.1中EF Core GroupBy后First()无法翻译的问题求助

问题背景 #

在.NET 3.1环境下使用Entity Framework Core编写分组查询时，将Child实体按LrpId、CountryId、FunderId分组后，投影到LrpReportDTO时调用group.First()获取关联实体属性，抛出LINQ表达式无法翻译的错误。

实体模型（简化） #

public partial class Child : Entity<int>, IAggregateRoot
{
    public Lrp Lrp { get; set; }
    public int LrpId { get; set; }
    public Country Country { get; set; }
    public int CountryId { get; set; }
    public Product Product { get; set; }
    public int? ProductId { get; set; }
    public Funder Funder { get; set; }
    public int? FunderId { get; set; }
    public ChildStatus? Status { get; set; }
    public ICollection<ChildSupporterLink> ChildSupporterLinks { get; set; }
}

public class Product {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Lrp {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Funder {
    public int Id { get; set; }
    public string Name { get; set; }
}

DTO定义 #

public class LrpReportDTO
{
    public int Id { get; set; }
    public FunderDto Funder { get; set; }
    public ProductDto Product { get; set; }
    public LrpDto Lrp { get; set; }
    public EnumValueDto Status { get; set; }
    public int Count { get; set; }
}
public class FunderDto
{
    public int? Id { get; set; }
    public string Name { get; set; }
}
public class ProductDto
{
    public int? Id { get; set; }
    public string Name { get; set; }
}
public class LrpDto
{
    public int?   Id   { get; set; }
    public string Name { get; set; }
}
public class EnumValueDto
{
    public string Value { get; set; }
    public string Name { get; set; }
}

原查询代码 #

public async Task<List<LrpReportDTO>> GetGroupedLrpReportData(Expression<Func<Child, bool>> filter, CancellationToken cancellationToken)
{
    return await DbContext.Set<Child>()
        .Where(filter)
        .GroupBy(c => new { c.LrpId, c.CountryId, c.FunderId })
        .Select(group => new LrpReportDTO
        {
            Id = group.Key.LrpId,
            Funder = new FunderDto
            {
                Id = group.Key.FunderId,
                Name = group.First().Funder.Name
            },
            Product = new ProductDto
            {
                Id = group.First().Product.Id,
                Name = group.First().Product.Name
            },
            Lrp = new LrpDto
            {
                Id = group.Key.LrpId,
                Name = group.First().Lrp.Name
            },
            Status = group.First().Status != null
                ? new EnumValueDto
                {
                    Value = group.First().Status.ToString(),
                    Name = group.First().Status.ToString()
                }
                : null,
            Count = group.Sum(x => x.ChildSupporterLinks.Count)
        })
        .ToListAsync(cancellationToken);
}

异常信息 #

The LINQ expression '(GroupByShaperExpression:KeySelector: new {LrpId = (c.LrpId),CountryId = (c.CountryId),FunderId = (c.FunderId)},ElementSelector:(EntityShaperExpression:EntityType: ChildValueBufferExpression:(ProjectionBindingExpression: EmptyProjectionMember)IsNullable: False)).First()' 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().

解决方案 #

EF Core 3.1对GroupBy后的First()/FirstOrDefault()翻译支持有限，可通过以下方式改写：

方案1：提前投影所需字段，再分组 #

先将Child及其关联实体的必要字段投影到匿名类型，再进行分组，避免在分组后调用First()访问导航属性：

public async Task<List<LrpReportDTO>> GetGroupedLrpReportData(Expression<Func<Child, bool>> filter, CancellationToken cancellationToken)
{
    return await DbContext.Set<Child>()
        .Where(filter)
        .Select(c => new 
        {
            c.LrpId,
            c.CountryId,
            c.FunderId,
            c.Status,
            LrpName = c.Lrp.Name,
            FunderName = c.Funder.Name,
            ProductId = c.Product.Id,
            ProductName = c.Product.Name,
            SupporterCount = c.ChildSupporterLinks.Count
        })
        .GroupBy(x => new { x.LrpId, x.CountryId, x.FunderId })
        .Select(group => new LrpReportDTO
        {
            Id = group.Key.LrpId,
            Funder = new FunderDto
            {
                Id = group.Key.FunderId,
                Name = group.First().FunderName
            },
            Product = new ProductDto
            {
                Id = group.First().ProductId,
                Name = group.First().ProductName
            },
            Lrp = new LrpDto
            {
                Id = group.Key.LrpId,
                Name = group.First().LrpName
            },
            Status = group.First().Status != null
                ? new EnumValueDto
                {
                    Value = group.First().Status.ToString(),
                    Name = group.First().Status.ToString()
                }
                : null,
            Count = group.Sum(x => x.SupporterCount)
        })
        .ToListAsync(cancellationToken);
}

该方式提前提取所需导航属性字段，分组后First()访问的是简单字段，EF Core可正常翻译。

方案2：使用聚合函数替代First()（适合同组内属性一致的场景） #

由于分组键是LrpId、CountryId、FunderId，同组内的Lrp、Funder等关联实体属性值应一致，可用Max()或Min()获取属性值，避免First()：

public async Task<List<LrpReportDTO>> GetGroupedLrpReportData(Expression<Func<Child, bool>> filter, CancellationToken cancellationToken)
{
    return await DbContext.Set<Child>()
        .Where(filter)
        .GroupBy(c => new { c.LrpId, c.CountryId, c.FunderId })
        .Select(group => new LrpReportDTO
        {
            Id = group.Key.LrpId,
            Funder = new FunderDto
            {
                Id = group.Key.FunderId,
                Name = group.Max(x => x.Funder.Name)
            },
            Product = new ProductDto
            {
                Id = group.Max(x => x.Product.Id),
                Name = group.Max(x => x.Product.Name)
            },
            Lrp = new LrpDto
            {
                Id = group.Key.LrpId,
                Name = group.Max(x => x.Lrp.Name)
            },
            Status = group.Max(x => x.Status) != null
                ? new EnumValueDto
                {
                    Value = group.Max(x => x.Status).ToString(),
                    Name = group.Max(x => x.Status).ToString()
                }
                : null,
            Count = group.Sum(x => x.ChildSupporterLinks.Count)
        })
        .ToListAsync(cancellationToken);
}

注意：仅当同组内对应属性值完全相同时，Max()/Min()才会得到正确结果，这符合分组键对应的关联实体一致的业务逻辑。

方案3：客户端评估（不推荐，仅当数据量小时使用） #

若数据量不大，可先将分组前的数据拉到客户端再处理，但会增加数据库查询的数据量：

public async Task<List<LrpReportDTO>> GetGroupedLrpReportData(Expression<Func<Child, bool>> filter, CancellationToken cancellationToken)
{
    var childData = await DbContext.Set<Child>()
        .Include(c => c.Lrp)
        .Include(c => c.Funder)
        .Include(c => c.Product)
        .Where(filter)
        .Select(c => new 
        {
            c.LrpId,
            c.CountryId,
            c.FunderId,
            c.Status,
            c.Lrp,
            c.Funder,
            c.Product,
            SupporterCount = c.ChildSupporterLinks.Count
        })
        .ToListAsync(cancellationToken);

    return childData
        .GroupBy(c => new { c.LrpId, c.CountryId, c.FunderId })
        .Select(group => new LrpReportDTO
        {
            Id = group.Key.LrpId,
            Funder = new FunderDto
            {
                Id = group.Key.FunderId,
                Name = group.First().Funder.Name
            },
            Product = new ProductDto
            {
                Id = group.First().Product.Id,
                Name = group.First().Product.Name
            },
            Lrp = new LrpDto
            {
                Id = group.Key.LrpId,
                Name = group.First().Lrp.Name
            },
            Status = group.First().Status != null
                ? new EnumValueDto
                {
                    Value = group.First().Status.ToString(),
                    Name = group.First().Status.ToString()
                }
                : null,
            Count = group.Sum(x => x.SupporterCount)
        })
        .ToList();
}

此方法通过ToListAsync()将数据加载到内存后再分组，避免EF Core翻译问题，但数据量大时会影响性能。

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