使用groupby和order by子句将SQL连接查询连接到LINQ

mwg9r5ms 于 2023-01-28 发布在其他

关注(0)|答案(3)|浏览(141)

我试图将这个连接查询复制到linq中，但是我不知道在哪里添加group by和order by语句。

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

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

来源：https://stackoverflow.com/questions/75229498/sql-join-query-to-linq-using-groupby-and-order-by-clause

3条答案

按热度按时间

flseospp1#

您可以在ToList（）之前执行GroupBy和OrderBy：

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 })
    .GroupBy(x=>x.CheckID).ThenBy(x=>x.Earnings)
    .OrderBy(x=>x.CheckID).ToList();

赞(0）回复(0）举报 2023-01-28

ax6ht2ek2#

查询后：
1.使用.GroupBy()按CheckID和Earnings列进行分组。
1.提取每个组的密钥。
1.按CheckID排序。

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 }
         )
         .GroupBy(x => new { x.CheckID, x.Earnings })
         .Select(g => new { g.Key.CheckID, g.Key.Earnings })
         .OrderBy(x => x.CheckID)
         .ToList();

赞(0）回复(0）举报 2023-01-28

pzfprimi3#

请尝试以下查询：

var query = 
    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)
    group c by new { c.CheckID, cv.Earnings } into g
    orderby g.Key.CheckID  
    select new 
    { 
        g.Key.CheckID, 
        g.Key.Earnings 
    };

也可以在投影后放置orderby：

var query =
    ...
    group c by new { c.CheckID, cv.Earnings } into g
    select new 
    { 
        g.Key.CheckID, 
        g.Key.Earnings 
    } into s
    orderby s.CheckID
    select s;

赞(0）回复(0）举报 2023-01-28

我来回答

使用groupby和order by子句将SQL连接查询连接到LINQ

3条答案

相关问题

热门标签

最新问答