New Posts New Posts RSS Feed: group by and having clauses
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

group by and having clauses
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Topic: group by and having clauses
    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.
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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 LINQ, so a web search may produce more clever answers.


Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down