[SOLVED] LINQ query with GROUP BY and Count(*) into Anonymous Type

LINQ query with GROUP BY and Count(*) into Anonymous Type

I'm trying to use a LINQ query to determine how many of each particular object type I have and record those values into an anonymous type.

Let's say I have some data that looks like this (there are really objects exposing this property, but it'll work the same)

GroupId
1
1
2
2
2
3

I know how to format my query in SQL. It would be something like this:

SELECT grp = GroupId, cnt = COUNT(*)
FROM myTable
GROUP BY GroupId

In this case the output would be something like this SQL Fiddle:

GroupID  Count
1        2
2        3
3        1

How can I do the same thing with LINQ in vb.net

Dim groupCounts = From person In data
                  Group By person.GroupId
                  Select new {group = person.GroupId, count = count(*)}

That's not quite right, but I think it's close.

Also, not knowing much about anonymous types, can I actually declare groupCounts ahead of time that it will be an enumeration of items which each have a group and count property?

Solution

Try using this in LinqPad, and subbing out for your database entity it should get you closer.

Public Sub grouper2()
    Dim numbers = New Integer() {1,1,2,2,2,3}

    Dim numberGroups = From w In numbers _
                    Group w By Key = w Into Group _
                    Select Number = Key, numbersCount = Group.Count()

    'linqpad specific output
    'numberGroups.Dump()

    For Each g In numberGroups
        Console.WriteLine("Numbers that match '{0}':", g.Number)
            Console.WriteLine(g.numbersCount)        
    Next

End Sub