如何在C# LINQ多表关联查询中计算Earnings列总和？

需求说明 #

需要用C# LINQ实现与以下SQL等价的关联查询，计算关联后符合条件的Earnings列总和：

SELECT SUM(Earnings) FROM [Customers0].[pay].[Checks] AS C
INNER JOIN [Customers0].[pay].[CheckVendorCalc] AS CV ON C.CheckID=CV.CheckID
INNER JOIN [Customers0].[pay].[Vendor] AS V ON CV.VendorID=V.VendorID
WHERE 
C.CustomerID = 360 and 
V.EPVendorWLID = 156 and 
CV.Earnings <> 0 and
C.CheckDate >= '01-01-2022' and C.checkdate <= '12-31-2022'

当前代码问题 #

你提供的LINQ代码仅查询出符合条件的cv.Earnings记录集合，没有计算总和；同时WHERE条件与原始SQL存在差异（缺少CheckDate范围判断、CV.Earnings<>0的过滤，多了v.EffDate==effectiveDate的额外条件）。

var joinResult = (from c in db.Checks
                  join cv in db.CheckVendorCalcs on c.CheckID equals cv.CheckID
                  join v in db.Vendors on cv.VendorID equals v.VendorID
                  where ( c.CustomerID==CustomerID && v.EPVendorWLID==VendorWLID && v.EffDate==effectiveDate)
                  select new { Earnings= cv.Earnings}
                 );

正确实现方式 #

方式1：查询表达式语法

直接在过滤后的查询结果上调用Sum()方法计算总和，同时对齐原始SQL的WHERE条件：

// 计算总和，若Earnings为可空类型，可使用Sum(x => (decimal?)x) ?? 0避免空值报错
var totalEarnings = (from c in db.Checks
                    join cv in db.CheckVendorCalcs on c.CheckID equals cv.CheckID
                    join v in db.Vendors on cv.VendorID equals v.VendorID
                    where c.CustomerID == CustomerID 
                          && v.EPVendorWLID == VendorWLID 
                          && cv.Earnings != 0
                          && c.CheckDate >= new DateTime(2022, 1, 1) 
                          && c.CheckDate <= new DateTime(2022, 12, 31)
                    select cv.Earnings).Sum();

方式2：方法链语法

若偏好流式写法，可使用LINQ方法链实现：

var totalEarnings = db.Checks
    .Join(db.CheckVendorCalcs, 
          check => check.CheckID, 
          calc => calc.CheckID, 
          (check, calc) => new { Check = check, CheckVendorCalc = calc })
    .Join(db.Vendors, 
          combined => combined.CheckVendorCalc.VendorID, 
          vendor => vendor.VendorID, 
          (combined, vendor) => new { combined.Check, combined.CheckVendorCalc, Vendor = vendor })
    .Where(filter => filter.Check.CustomerID == CustomerID 
                && filter.Vendor.EPVendorWLID == VendorWLID 
                && filter.CheckVendorCalc.Earnings != 0
                && filter.Check.CheckDate >= new DateTime(2022, 1, 1) 
                && filter.Check.CheckDate <= new DateTime(2022, 12, 31))
    .Sum(result => result.CheckVendorCalc.Earnings);

注意事项 #

• 确保CustomerID、VendorWLID变量已正确赋值，与SQL中的360、156对应。
• 若Earnings是可空数值类型（如decimal?），建议使用Sum(x => x.Earnings.GetValueOrDefault())或Sum(x => (decimal?)x.Earnings) ?? 0，避免因空值导致的异常。
• 若业务上确实需要保留v.EffDate==effectiveDate条件，可在WHERE中添加，但需确认原始SQL是否遗漏该逻辑。

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