使用三个实体时LINQ GroupBy与Sum的运行时翻译问题求助

EF Core无法将你原代码中GroupBy后的嵌套Sum(r => r.Payments.Where(...).Sum(...))结构正确翻译为SQL，这种多层聚合的写法超出了当前EF Core的SQL转换能力。以下两种单查询方案可以解决问题，同时保持你需要的输出结构：

方案一：先投影结算级支付金额再分组汇总 #

先在单个Settlement层面计算对应两种支付类型的总额，再基于驾驶员信息分组求和：

var settlementsGrouped = await _databaseContext.Settlements
    .ExcludeDeleted()
    .Where(s => s.DriverId == request.Id && s.DateStart >= request.DateStart && s.DateEnd <= request.DateEnd)
    .Select(s => new 
    {
        DriverInfo = new { s.Driver.Id, s.Driver.FullName, s.Driver.Address, s.Driver.PostalCode, s.Driver.Town, s.Driver.IdNumber },
        CommonPayment = s.Payments.Where(p => p.IsActive && p.Type == PaymentEntityTypeEnum.Common).Sum(p => p.Amount),
        AdvancePayment = s.Payments.Where(p => p.IsActive && p.Type == PaymentEntityTypeEnum.Advance).Sum(p => p.Amount)
    })
    .GroupBy(x => x.DriverInfo)
    .Select(g => new 
    {
        g.Key.FullName,
        g.Key.Address,
        g.Key.PostalCode,
        g.Key.Town,
        g.Key.IdNumber,
        PaymentType = g.Sum(x => x.CommonPayment),
        AdvancePaymentType = g.Sum(x => x.AdvancePayment)
    })
    .ToListAsync(cancellationToken);

这种写法把嵌套聚合拆成了两层单层聚合，EF Core可以正确转换为对应的SQL分组统计逻辑。

方案二：展开Payments后分组统计 #

通过SelectMany将结算和关联支付展开为扁平结构，再按驾驶员信息分组并按支付类型求和：

var settlementsGrouped = await _databaseContext.Settlements
    .ExcludeDeleted()
    .Where(s => s.DriverId == request.Id && s.DateStart >= request.DateStart && s.DateEnd <= request.DateEnd)
    .SelectMany(s => s.Payments.Where(p => p.IsActive), (s, p) => new 
    {
        s.Driver.FullName,
        s.Driver.Address,
        s.Driver.PostalCode,
        s.Driver.Town,
        s.Driver.IdNumber,
        p.Type,
        p.Amount
    })
    .GroupBy(x => new { x.FullName, x.Address, x.PostalCode, x.Town, x.IdNumber })
    .Select(g => new 
    {
        g.Key.FullName,
        g.Key.Address,
        g.Key.PostalCode,
        g.Key.Town,
        g.Key.IdNumber,
        PaymentType = g.Where(x => x.Type == PaymentEntityTypeEnum.Common).Sum(x => x.Amount),
        AdvancePaymentType = g.Where(x => x.Type == PaymentEntityTypeEnum.Advance).Sum(x => x.Amount)
    })
    .ToListAsync(cancellationToken);

该方案通过扁平化关联数据，将聚合操作简化为分组内的条件求和，同样能被EF Core正确翻译。

两种方案都保持了数据库端查询的特性，不会触发客户端评估，且当某个结算无对应类型支付时，Sum会返回0，和原逻辑一致。

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