CODEWITHSUNDEEP
c#sqllinqgroup-bysum
Iti Tyagi
Iti Tyagi

Reputation: 3661

Get Sum of a field in Linq with Group by

I am having an SQL:

 SELECT ApplicationNo,COUNT(ApplicationNo) AS CNT, SUM(Amount) as AMNT 
FROM Payments where (TYPE=1 AND Position=1)  and (Date>='2011-01-01')
and (Date<='2012-01-01')
GROUP BY ApplicationNo

Is there a way in which I can convert the same in Linq?

var q = (from payments in context.Payments
                  where payments.Date >= fromdate && payments.Date <= todate
                  group payments by new { payments.ApplicationId } into g
                  select new
                  {
                      applicationId=g.Key,
                      Amount=g.Sum(a=>a.Amount)
                  });

If I write the same in Linq and then Group by in the end, I am not getting the same result.

Upvotes: 1

Views: 525

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

DateTime fromDate = new DateTime(2011, 1, 1);
DateTime toDate = new DateTime(2011, 1, 1);

var query = from p in db.Payments
            where p.Type == 1 && p.Position == 1 && 
            p.Date >= fromDate && p.Date <= toDate
            group p by p.ApplicationNo into g
            select new {
                 ApplicationNo = g.Key,
                 CNT = g.Count(),
                 AMNT = g.Sum(x => x.Amount)
           };

Here db is your context class.

Upvotes: 1

Related Questions