how to use group by in linq C# and fetch the list of records

Question

i have a 2 tables and i do some joins to return a list of records based on some requirement. I am able to write a group by query for my requiremnet and fecth the records,but i need the same in Linq query. my sql query is :

select 
   MiscServiceDesc, 
   sum(PaymentAmount),
   count(PaymentAmount) 
from 
   MiscTransaction MT 
join MiscService MS 
   on MT.MiscServiceID = MS.MiscServiceID 
group by 
  MiscServiceDesc

where MiscTransaction and MiscService are my two tables.

Any help? Thanks in advance

also check this post:http://stackoverflow.com/questions/27730575/linq-to-sql-left-outer-join-with-group-by-and-having-clause — Ehsan Sajjad, Jan 16 '15 at 09:59

score 2 · Accepted Answer · answered Jan 16 '15 at 09:57

Try this. I dont know MiscServiceDesc, PaymentAmount, PaymentAmount which of these columns are in MiscTransaction table or MiscService table, but you can change the code appropriatly if needed.

var result = (from mt in MiscTransaction 
               join ms in MiscService on mt.MiscServiceID equals ms.MiscServiceID
               select new {ms.MiscServiceDesc, mt.PaymentAmount} into lst
               group lst by lst.MiscServiceDesc into gr
               select new {MiscServiceDesc  = gr.Key, Summ = gr.Sum(c=>c.PaymentAmount), Count = gr.Count()}
       ).ToList();

how to use group by in linq C# and fetch the list of records

1 Answers1