How do I order by and group by in a Linq query?
I tried..
Dim iPerson = From lqPersons In objPersons Where L开发者_开发百科en(lqPersons.Person) > 0 Group lqPersons By key = lqPersons.Name Into Group Order By Group descending Select Group, key
For Each i In iPerson
tmp = tmp & vbNewLine & i.key & ", " & i.Group.Count
Next
The above works if I remove the Order By Group Descending
claus, but with it, I get an error on the Next
statement..
At least one object must implement IComparable.
My query is to obtain a list of the people in my class/object with how many times their name is used as an item of the class/object.
I.e.
Joe, 4 | James, 5 | Mike, 4
Does anyone have any ideas what I'm doing wrong?
Your query is fine (there's no problem with using Group in VB.NET as a name BTW ... actually you MUST use Group, so don't worry about that), except you should have Order By key.
p.s. Tested.
EDIT: For completeness, you can rename your Group like so:
Dim iPerson = From lqPersons In objPersons
Where Len(lqPersons.Person) > 0
Group lqPersons By key = lqPersons.Name Into g = Group
Order By key Descending
Select g, key
However that has nothing to do with your problem ... just clarifying that point.
Excuse my C#, but couldn't you do something like...
objPersons.Where(p=> p.Person > 0).GroupBy(p => p.Name).Select(p => new { Name= p.Key, Number = p.Count()}).OrderByDescending(p=> p.Number);
Main Idea:
- GroupBy as you did
- Select into a new object using your Key (Name) and counting how many in that group
- Order by descending
You get the error on the next statement because the query is executed when the For Each
loop is executes (it's called defferd execution).
The error actually says that you can't do Ordey By Group
because IGrouping
doesn't implement IComparable
, so the result of your Group By
-statement can't be ordered by the IGrouping
itself.
You have to order by the number of elements of the IGrouping
objects.
Try this (note that I don't know how your classes look like...):
Dim objPersons = New YourClass() {New YourClass("Joe"), _
New YourClass("Joe"), _
New YourClass("Joe"), _
New YourClass("James"), _
New YourClass("James"), _
New YourClass("James"), _
New YourClass("James"), _
New YourClass("Mike"), _
New YourClass("Mike")}
Dim query = objPersons.Where(function(p) p.Person > 0) _
.GroupBy(Function(p) p.Name) _
.OrderByDescending(Function(g) g.Count) _
.Select(function(g) g.Key & ", " & g.Count())
For Each s In query
Console.WriteLine(s)
Next
Output:
James, 4
Joe, 3
Mike, 2
is lqPersons.Name a string, or a struct or class containing first, last, middle, etc names? If it's the former, you should be good with this as strings are IComparable. If Name is an object of some user-defined class, you will either need to implement IComparable on that class (providing a CompareTo() method used by sorting algorithms), or Order By a "primitive" sub-property of Name, like LastName.
EDIT: Just saw something else. When you use Group By, the object that results is a Lookup; basically a KeyValuePair with a collection of Values instead of just one. Lookups are not IComparable. So instead of ordering by the Group, try ordering by the key identifier for that Group (lqPersons.Name).
I've done something similar in VB. I have it on my blog as well. But basically here is the SQL I started with.
SELECT orgno, psno, sche, projid, RecType,
SUM(OBBudExpAmt) AS OBBudExpAmt,
SUM(ABBudEncAmt) AS ABBudEncAmt
FROM tbl908_BudSPDGrps
WHERE orgno = '210'
GROUP BY orgno, psno, sche, projid, RecType
And here is the corresponding LINQ.
Dim projids = From p In db.tbl908_BudSPDGrps _
Where p.orgno = options.GroupFilter _
Group p By p.orgno, p.psno, p.sche, p.projid, p.RecType _
Into g = Sum(p.OBBudExpAmt), h = Sum(p.ABBudEncAmt) _
Order By orgno, psno, sche, projid, RecType _
Select sche, RecType, g, h
Pretty self-explanitory. Hope it helps you.
精彩评论