如何将含Group By与Order By的SQL Join查询转换为LINQ语句

原SQL查询 #

select
    c.CheckID,cv.Earnings  
FROM
    [Customers0].[pay].[CheckVendorCalc] as cv
    inner join [Customers0].[pay].[Checks] as c on cv.checkid = c.checkid
where
    c.CheckDate BETWEEN '2022-01-01'
    AND '2022-12-31'
    and c.CustomerID = 360
    and c.EmployeeCode = '01'
    and (
        cv.TaxableEarnings != null
        or cv.TaxableEarnings != 0)
group by
    c.CheckID,cv.Earnings
order by
    c.CheckID

当前未完成的LINQ代码 #

var v1 = (from cv in db.CheckVendorCalcs
                          join c in db.Checks on cv.CheckID equals c.CheckID
                          where (c.CheckDate >= YTDStartDate && c.CheckDate <= YTDEndtDate && c.CustomerID == CustomerID && c.EmployeeCode == e.EmployeeCode && (cv.TaxableEarnings != null || cv.TaxableEarnings != 0)
                         select new { c.CheckID, cv.Earnings }).ToList();

修改后的LINQ代码（两种实现方式） #

方式一：严格对应SQL的Group By逻辑

在查询语法中，group by需要放在where之后、select之前，order by则放在select之后：

var v1 = (from cv in db.CheckVendorCalcs
          join c in db.Checks on cv.CheckID equals c.CheckID
          where c.CheckDate >= YTDStartDate && c.CheckDate <= YTDEndtDate 
                && c.CustomerID == CustomerID 
                && c.EmployeeCode == e.EmployeeCode 
                && (cv.TaxableEarnings != null || cv.TaxableEarnings != 0)
          // 按指定字段分组，into g将分组结果存入变量g
          group new { c.CheckID, cv.Earnings } by new { c.CheckID, cv.Earnings } into g
          // 选择分组的键（即原SQL中group by的两个字段）
          select g.Key
          // 按CheckID排序
          orderby g.Key.CheckID)
          .ToList();

方式二：用Distinct替代Group By（更简洁）

由于原SQL中select的字段和group by完全一致，本质是去重，用Distinct()可以达到相同效果：

var v1 = (from cv in db.CheckVendorCalcs
          join c in db.Checks on cv.CheckID equals c.CheckID
          where c.CheckDate >= YTDStartDate && c.CheckDate <= YTDEndtDate 
                && c.CustomerID == CustomerID 
                && c.EmployeeCode == e.EmployeeCode 
                && (cv.TaxableEarnings != null || cv.TaxableEarnings != 0)
          select new { c.CheckID, cv.Earnings })
          // 去重，对应原SQL的group by逻辑
          .Distinct()
          // 按CheckID排序
          .OrderBy(x => x.CheckID)
          .ToList();

关键说明 #

• Group By位置：查询语法中，group ... by ... into必须放在筛选条件（where）之后，它会把符合条件的数据按指定字段分组，分组后的结果通过into指定的变量访问。
• Order By位置：可以在查询语法的select后用orderby子句，或者在方法链中调用OrderBy()方法，确保排序逻辑在数据筛选/分组之后执行。

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