I am trying to copy this join query into linq..But I don't understand where to add group by and order by statements..

 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();

CodePudding user response：

You can just do GroupBy and OrderBy before ToList():

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();

CodePudding user response：

After the query:

1. Use .GroupBy() to group by the CheckID and Earnings columns.
2. Extract the keys for each group.
3. Order by 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();