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

Question

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

I tried..

Dim iPerson = From lqPersons In objPersons Where Len(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?

Answer 1

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).

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

hi all thanks for all your responses - very useful!

I'm still having the same problem though, as I want to sort descending on the count of person name occurances, i.e. I want the highest number at the top of the returned query, but it still keeps failing on Next statement (At least one object must implement IComparable).

Maybe I've approached the query incorrectly as I get the count value whilst looping through the results?

    For Each i In iPerson
    tmp = tmp & vbNewLine & i.key & ", " & i.Group.Count 'Heres where I get the count of each name found
    Next

Answer 6

hi all thanks for all your responses - very useful!

I'm still having the same problem though, as I want to sort descending on the count of person name occurances, i.e. I want the highest number at the top of the returned query, but it still keeps failing on Next statement (At least one object must implement IComparable).

Maybe I've approached the query incorrectly as I get the count value whilst looping through the results?

    For Each i In iPerson
    tmp = tmp & vbNewLine & i.key & ", " & i.Group.Count 'Heres where I get the count of each name found
    Next

Answer 7

You get the error on the next statement because the query is executed when For Each is 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 Count-Property of the IGrouping.

Try this (note that I don't know how your Classes look like...):

Sub Main()
    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

    Console.ReadKey()
End Sub

outputs:

James, 4
Joe, 3
Mike, 2

Answer 8

I think your lqPersons items should implement IComparable, also the : objPersons.Where(p=> p.Person > 0).GroupBy(p => p.Name).Select(p => new { Name= p.Key, Number = p.Count()}).OrderByDescending(p=> p.Number); wroted by mirezus is a good answer.

Answer 9

Ok, so my query may need to be a little more thought out...

Ive just thought simply getting the TOP name isnt enough... If 3 names are joint top, I want to see just those three i.e.

Joe, Joe, Joe, James, James, Mike, Mike, Ben, Ben, Ben,

I'd like my query to return...

Joe, 3 | Ben, 3

My bad :)

On a slightely different note can anybody recommend any good books for LINQ, in particular Linq to objects?