SQL join query to LINQ using groupby and order by clause

Question

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

Answer 1

Try the following query:

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 
    };

Also orderby can be placed after projection:

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;