How do I order by and group by in a Linq to objects query?

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:

1. GroupBy as you did
2. Select into a new object using your Key (Name) and counting how many in that group
3. 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.