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?

Answer

May 10, 2013

crackhaus

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