Print Page | Close Window

Sum and Grouping in Linq

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=4632
Printed Date: 25-Apr-2024 at 7:03am


Topic: Sum and Grouping in Linq
Posted By: BillG
Subject: Sum and Grouping in Linq
Date Posted: 29-Dec-2013 at 10:26am
How would i do the following in Linq. Here is the SQL

Select duesyearmontn, sum(duesamount) as duesrate
from duesowed
where socsecno = "999-99-9999' and duescatcd = 'D'
group by duesyearmonth
having sum(duesamount) <> 0
order by duesyearmonth desc



this is what i have so far

               var duesOwedQuery = _manager.DuesOweds
                    .Where(d => d.SocSecNo == member.SocSecNo && d.DuesCatCd.StartsWith("D"))
                    .OrderByDescending(d => d.DuesYearMonth);
 
                var duesOweds1 = duesOwedQuery.Execute<DuesOwed>();

how do i handle the group by and having clause?

Bill


Replies:
Posted By: sbelini
Date Posted: 08-Jan-2014 at 4:28pm
Hi Bill,

Try:
      var query = mgr.DuesOweds
        .Where(d => d.SocSecNo == member.SocSecNo && d.DuesCatCd.StartsWith("D"))
        .GroupBy(d => d.DuesYearMonth)
        .Select(d => new { duesYearMonth = d.Key, duesRate = d.Sum(d1 => d1.DuesAmount) })
        .Where(d => d.duesRate != 0)
        .OrderByDescending(d => d.duesYearMonth);


Print Page | Close Window