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

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jan 25 '23 at 19:00

Bibek Bastola · Answer 1 · 2023-01-25T04:39:27.627

0

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

edited Jan 25 '23 at 04:39

answered Jan 25 '23 at 04:07

Bibek Bastola

15
3

'IQueryable>>' does not contain a definition for 'ThenBy' and no accessible extension method 'ThenBy' accepting a first argument of type 'IQueryable>>' could be found (are you missing a using directive or an assembly reference?) – Ben Jan 25 '23 at 05:36
can I use distict() instead of groupby? – Ben Jan 25 '23 at 05:38

Yong Shun · Answer 2 · 2023-01-25T06:20:42.607

After the query:

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

Done update the answer. Think specify the properties will be safer. — Yong Shun, Jan 25 '23 at 06:21

score 0 · Accepted Answer · answered Jan 25 '23 at 15:55

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;

SQL join query to LINQ using groupby and order by clause

3 Answers3