EF Core LINQ表达式无法翻译问题排查与解决方案求助

问题 #

需从数据库获取所有临期物料，实现代码如下：

1. 按PO_Summary_Id统计Actual_Delivered总和 #

var getActualDelivered = _context.QC_Receiving
             .GroupBy(x => x.PO_Summary_Id)
             .Select(g => new { PO_Summary_Id = g.Key, ActualDelivered = g.Sum(x => x.Actual_Delivered) });

2. 查询30天内到期的QC_Receiving数据 #

var receiving = _context.QC_Receiving
        .Where(x => x.Expiry_Date <= DateTime.Now.AddDays(30))
        .GroupBy(x => new
        {
            x.Expiry_Date,
            x.PO_Summary_Id,
            x.Id,
            x.IsNearlyExpire,
            x.ExpiryIsApprove,
            x.TotalReject
        })
        .Select(g => new NearlyExpireDto
        {
            ExpiryDate = g.Key.Expiry_Date.HasValue ? g.Key.Expiry_Date.Value.ToString("MM/dd/yyyy") : null,
            Id = g.Key.PO_Summary_Id,
            IsNearlyExpire = g.Key.IsNearlyExpire,
            ExpiryIsApprove = g.Key.ExpiryIsApprove,
            TotalReject = g.Key.TotalReject,
            Days = g.Key.Expiry_Date.HasValue ? (int)Math.Round(g.Key.Expiry_Date.Value.Subtract(DateTime.Now).TotalDays) : 0,
        });

3. 查询PO_Summary表数据 #

var posummary = _context.POSummary
        .Select(x => new { x.Id, x.PO_Number, x.ItemCode, x.ItemDescription, x.VendorName, x.UOM, x.Ordered, x.IsActive });

关联三个查询并返回分页结果 #

var nearlyExpiryItems = posummary
        .GroupJoin(getActualDelivered,
            posummary => posummary.Id,
            delivered => delivered.PO_Summary_Id,
            (posummary, delivered) => new { posummary, delivered })
        .SelectMany(x => x.delivered.DefaultIfEmpty(),
            (posummary, delivered) => new { posummary.posummary, delivered })
        .GroupJoin(receiving,
            x => x.posummary.Id,
            receiving => receiving.Id,
            (x, receiving) => new { x.posummary, x.delivered, receiving })
        .SelectMany(x => x.receiving.DefaultIfEmpty(),
            (x, receiving) => new { x.posummary, x.delivered, receiving })
        .Where(x => x.receiving == null || x.receiving.ExpiryIsApprove == null)
        .Where(x => x.receiving == null || x.receiving.IsNearlyExpire == true)
        .Select(x => new NearlyExpireDto
        {
            Id = x.posummary.Id,
            PO_Number = x.posummary.PO_Number,
            ItemCode = x.posummary.ItemCode,
            ItemDescription = x.posummary.ItemDescription,
            Supplier = x.posummary.VendorName,
            Uom = x.posummary.UOM,
            QuantityOrdered = x.posummary.Ordered,
            IsActive = x.posummary.IsActive,
            ActualGood = x.receiving.ActualGood,
            ActualRemaining = x.receiving.QuantityOrdered - (x.delivered != null
                ? x.delivered.ActualDelivered - x.receiving.TotalReject
                : x.receiving.TotalReject),
            TotalReject = x.receiving.TotalReject,
            ExpiryDate = x.receiving.ExpiryDate,
            Days = x.receiving.Days,
            IsNearlyExpire = x.receiving.IsNearlyExpire,
            ExpiryIsApprove = x.receiving.ExpiryIsApprove,
            ReceivingId = x.receiving.ReceivingId
        });

return await PagedList<NearlyExpireDto>.CreateAsync(nearlyExpiryItems, userParams.PageNumber, userParams.PageSize);

控制器代码 #

[HttpGet]
[Route("GetAllNearlyExpireWithPagination")]
public async Task<ActionResult<IEnumerable<NearlyExpireDto>>> GetAllNearlyExpireWithPagination([FromQuery] UserParams userParams)
{
    var expiry = await _unitOfWork.Receives.GetAllNearlyExpireWithPagination(userParams);

    Response.AddPaginationHeader(expiry.CurrentPage, expiry.PageSize, expiry.TotalCount, expiry.TotalPages, expiry.HasNextPage, expiry.HasPreviousPage);

    var nearlyResult = new
    {
        expiry,
        expiry.CurrentPage,
        expiry.PageSize,
        expiry.TotalCount,
        expiry.TotalPages,
        expiry.HasNextPage,
        expiry.HasPreviousPage
    };

    return Ok(nearlyResult);
}

运行时异常 #

System.InvalidOperationException: The LINQ expression 'DbSet<ImportPOSummary>()
    .LeftJoin(
        inner: DbSet<PO_Receiving>()
            .GroupBy(
                keySelector: p => p.PO_Summary_Id, 
                elementSelector: p => p)
            .Select(e => new { 
                PO_Summary_Id = e.Key, 
                ActualDelivered = e
                    .Sum(x => x.Actual_Delivered)
             }), 
        outerKeySelector: i => i.Id, 
        innerKeySelector: e0 => e0.PO_Summary_Id, 
        resultSelector: (i, e0) => new TransparentIdentifier<ImportPOSummary, <>f__AnonymousType94<int, decimal>>(
            Outer = i, 
            Inner = e0
        ))
    .LeftJoin(
        inner: DbSet<PO_Receiving>()
            .Where(p0 => p0.Expiry_Date <= (Nullable<DateTime>)DateTime.Now.AddDays(30))
            .GroupBy(
                keySelector: p0 => new { 
                    Expiry_Date = p0.Expiry_Date, 
                    PO_Summary_Id = p0.PO_Summary_Id, 
                    Id = p0.Id, 
                    IsNearlyExpire = p0.IsNearlyExpire, 
                    ExpiryIsApprove = p0.ExpiryIsApprove, 
                    TotalReject = p0.TotalReject
                 }, 
                elementSelector: p0 => p0)
            .Select(e1 => new NearlyExpireDto{ 
                ExpiryDate = e1.Key.Expiry_Date.HasValue ? e1.Key.Expiry_Date.Value.ToString("MM/dd/yyyy") : null, 
                Id = e1.Key.PO_Summary_Id, 
                IsNearlyExpire = e1.Key.IsNearlyExpire, 
                ExpiryIsApprove = e1.Key.ExpiryIsApprove, 
                TotalReject = e1.Key.TotalReject, 
                Days = e1.Key.Expiry_Date.HasValue ? (int)Math.Round(e1.Key.Expiry_Date.Value.Subtract(DateTime.Now).TotalDays) : 0 
            }
            ), 
        outerKeySelector: ti => ti.Outer.Id, 
        innerKeySelector: e2 => e2.Id, 
        resultSelector: (ti, e2) => new TransparentIdentifier<TransparentIdentifier<ImportPOSummary, <>f__AnonymousType94<int, decimal>>, NearlyExpireDto>(
            Outer = ti, 
            Inner = e2
        ))' 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'.

异常原因 #

1. 客户端方法无法翻译：查询中使用了ToString("MM/dd/yyyy")、Math.Round、DateTime.Subtract等客户端专属方法，EF Core无法将这些逻辑转换为对应的SQL语句。
2. 复杂嵌套关联超出翻译能力：多层GroupJoin+SelectMany的嵌套结构，加上匿名类型的多层嵌套，EF Core查询翻译器无法处理这种复杂的关联逻辑。
3. 不必要的GroupBy干扰：第二个查询中按单条记录的所有字段分组，本质上等价于无分组操作，多余的分组逻辑增加了查询复杂度，干扰了翻译器的解析。

解决方案 #

1. 简化查询结构，替换GroupJoin为Join #

EF Core对Join的翻译支持远优于GroupJoin+SelectMany的组合，直接使用左连接语法简化关联逻辑：

2. 移除不必要的GroupBy #

第二个查询中的GroupBy完全多余，直接Select即可：

var receiving = _context.QC_Receiving
    .Where(x => x.Expiry_Date <= DateTime.Now.AddDays(30))
    .Select(x => new 
    {
        x.Expiry_Date,
        x.PO_Summary_Id,
        x.Id,
        x.IsNearlyExpire,
        x.ExpiryIsApprove,
        x.TotalReject
    });

3. 使用EF Core支持的函数替代客户端方法 #

用EF Core内置函数替换无法翻译的客户端逻辑，比如用EF.Functions.DateDiffDay计算天数，日期格式化可放到客户端处理（或用数据库支持的格式化函数，如SQL Server的FORMAT）：

修改后的关联查询代码 #

var nearlyExpiryItems = from pos in posummary
                        join del in getActualDelivered on pos.Id equals del.PO_Summary_Id into delGroup
                        from del in delGroup.DefaultIfEmpty()
                        join rec in receiving on pos.Id equals rec.PO_Summary_Id into recGroup
                        from rec in recGroup.DefaultIfEmpty()
                        where rec == null || rec.ExpiryIsApprove == null
                        where rec == null || rec.IsNearlyExpire == true
                        select new NearlyExpireDto
                        {
                            Id = pos.Id,
                            PO_Number = pos.PO_Number,
                            ItemCode = pos.ItemCode,
                            ItemDescription = pos.ItemDescription,
                            Supplier = pos.VendorName,
                            Uom = pos.UOM,
                            QuantityOrdered = pos.Ordered,
                            IsActive = pos.IsActive,
                            TotalReject = rec?.TotalReject,
                            // 用EF内置函数计算剩余天数
                            Days = rec?.Expiry_Date.HasValue == true 
                                ? EF.Functions.DateDiffDay(DateTime.Now, rec.Expiry_Date.Value) 
                                : 0,
                            // 日期格式化移到客户端处理，避免SQL翻译问题
                            ExpiryDate = rec?.Expiry_Date.HasValue == true 
                                ? rec.Expiry_Date.Value.ToString("MM/dd/yyyy") 
                                : null,
                            IsNearlyExpire = rec?.IsNearlyExpire,
                            ExpiryIsApprove = rec?.ExpiryIsApprove,
                            ReceivingId = rec?.Id,
                            // 修正空引用判断逻辑
                            ActualRemaining = pos.Ordered - (del != null 
                                ? del.ActualDelivered - (rec?.TotalReject ?? 0) 
                                : (rec?.TotalReject ?? 0))
                        };

4. 必要时启用客户端评估（谨慎使用） #

如果某些逻辑实在无法翻译成SQL，可将部分数据先加载到内存再处理，但需注意性能（避免加载大量数据）：

// 先将基础数据加载到内存
var posummaryList = await posummary.ToListAsync();
var deliveredList = await getActualDelivered.ToListAsync();
var receivingList = await receiving.ToListAsync();

// 在客户端完成关联和计算
var nearlyExpiryItems = // 客户端LINQ逻辑...

内容的提问来源于stack exchange，提问作者Vega Aldrin M