如何在C# Linq中关联三张表并按Rate总和排序

场景与表结构 #

Product表 #

New Rate表（对应你的RateDetails） #

Old Rate表（对应你的Accessorials） #

期望结果 #

按每个ID对应的New Rate与Old Rate的Rate总和降序排序：

错误代码示例 #

你尝试的代码未正确处理多记录求和逻辑：

Details = (from ld in Details.AsQueryable()                                                                     
           join cr in Context.RateDetails on ld.Id equals cr.Id                                                  
           into cRate from x in cRate.DefaultIfEmpty(new RateDetail())                                                       
           join cs in Context.Accessorials on ld.Id equals cs.Id                                                
           into cSRate from y in cSRate.DefaultIfEmpty(new AccessorialRead())                                   
           orderby new { totalRate = x.TotalRate + y.RateAmount } descending                                    
           select ld).ToList()

正确Linq实现方案 #

核心思路：先分别对两个Rate表按ID分组计算总和，再与Product表关联，最终计算总Rate并排序。

查询语法实现 #

// 计算New Rate各ID的Rate总和
var newRateTotals = from cr in Context.RateDetails
                    group cr by cr.Id into grouped
                    select new { Id = grouped.Key, NewTotal = grouped.Sum(item => item.Rate) };

// 计算Old Rate各ID的Rate总和
var oldRateTotals = from cs in Context.Accessorials
                    group cs by cs.Id into grouped
                    select new { Id = grouped.Key, OldTotal = grouped.Sum(item => item.RateAmount) };

// 关联Product表，计算总Rate并排序
var result = from ld in Details.AsQueryable()
             join nr in newRateTotals on ld.Id equals nr.Id into nrGroup
             from nr in nrGroup.DefaultIfEmpty()
             join or in oldRateTotals on ld.Id equals or.Id into orGroup
             from or in orGroup.DefaultIfEmpty()
             let totalRate = (nr?.NewTotal ?? 0) + (or?.OldTotal ?? 0)
             orderby totalRate descending
             select ld;

Details = result.ToList();

方法语法实现 #

var newRateTotals = Context.RateDetails
    .GroupBy(cr => cr.Id)
    .Select(grouped => new { Id = grouped.Key, NewTotal = grouped.Sum(item => item.Rate) });

var oldRateTotals = Context.Accessorials
    .GroupBy(cs => cs.Id)
    .Select(grouped => new { Id = grouped.Key, OldTotal = grouped.Sum(item => item.RateAmount) });

var result = Details.AsQueryable()
    .GroupJoin(newRateTotals, ld => ld.Id, nr => nr.Id, (ld, nrGroup) => new { ld, nrGroup })
    .SelectMany(x => x.nrGroup.DefaultIfEmpty(), (x, nr) => new { x.ld, nr })
    .GroupJoin(oldRateTotals, x => x.ld.Id, or => or.Id, (x, orGroup) => new { x.ld, x.nr, orGroup })
    .SelectMany(x => x.orGroup.DefaultIfEmpty(), (x, or) => new 
    { 
        Product = x.ld, 
        TotalRate = (x.nr?.NewTotal ?? 0) + (or?.OldTotal ?? 0) 
    })
    .OrderByDescending(item => item.TotalRate)
    .Select(item => item.Product);

Details = result.ToList();

问题说明 #

你之前的代码错误在于直接对左连接后的单条记录Rate值相加，未处理同一ID下多条Rate记录的求和逻辑。正确流程需先对两个Rate表按ID分组求和，再关联计算总Rate，避免重复数据与错误的总和计算。

内容的提问来源于stack exchange，提问作者N.Bharath