Print Page | Close Window

group by and having clauses

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=4214
Printed Date: 18-Apr-2025 at 10:36am


Topic: group by and having clauses
Posted By: BillG
Subject: group by and having clauses
Date Posted: 07-Jul-2013 at 5:54am
i have an IEnumerable which was created by a query of a table. It contains all the dues records for a particular member.
 
duesOwed
 
i need to filter it as follows. I want to group the data by DuesYeaMonth. Thats just a string column in the format 20130701
Year/Month/Day. I only want the rows where the Dues Category code (DuesCatCd) starts with "D" and after they are grouped only those groups where DuesAmount <> 0 and ordered by DuesYearMonth in descending order. So basically i am trying to find the newest dues record still owing and will work my way back to the first.
 
here is what i have so far. I figure it has to be done in two statements.
 
var groups = duesOwed.Where(d => d.DuesCatCd.StartsWith("D")).GroupBy(d => d.DuesYearMonth);
 
now the part i am not sure about
 
var MostRecordDuesRecord = groups.Where(d => d.DuesAmount != 0).OrderByDescending(d => d.DuesYearMonth);
 
What is groups though. Visual Studio doesnt seem to think it is a IEnumerable of DuesOwed records because DuesAmount is in red and it doesnt like the parameter to OrderByAscending.


Replies:
Posted By: kimj
Date Posted: 07-Jul-2013 at 6:28pm
If duesOwed contains items of type MemberDuesRecord, then "groups" here will be an IEnumerable<IGrouping<, MemberDuesRecord>>. If you hover over the groups variable, Visual Studio Intellisense will tell you what the type is.

You can do something like this to apply the having and order by clauses:

groups
.Where(g => g.Sum(d => d.DuesAmount) > 0)
.OrderByDescending(g => g.Key)

I'm not quite sure what you want to return, but since an IGrouping is hard to work with it might be easier to project the results into an anonymous type.

This is standard http://msdn.microsoft.com/en-us/library/bb344977%28v=vs.100%29.aspx - LINQ , so a web search may produce more clever answers.




Print Page | Close Window