LINQ query with GROUP BY and Count (*) in an anonymous type

Question

I am trying to use the LINQ query to determine how many of each particular type of object I have and write these values ​​to an anonymous type.

Let's say I have some data that looks like this (actually there are objects subject to this property, but they will work the same way)

GroupId 1 1 2 2 2 3 

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

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

In this case, the output will look like this :

 GroupID Count 1 2 2 3 3 1 

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

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

This is not entirely true, but I think it is close.

Also, without knowing much about anonymous types, can I actually declare groupCounts earlier that it will be an enumeration of elements, each of which has a group and counter property?

Answer 1

Try using this in LinqPad and fine-tune it for your database, it should come close to you.

 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 

Answer 2

I am using C #:

 var query = from person in data group person by person.GroupId into grouping select new { Key = grouping.Key, Count = grouping.Count() } 

But I tested the following snippet in VB and it works:

 Dim files = Directory.GetFiles (Path.GetTempPath()).Take (100).ToArray().AsQueryable() Dim groups = From f In files Group f By key = Path.GetExtension (f) Into Group Select Key = key, NumberGroup = Group.Count() 

Answer 3

So vb is a little weird when it comes to translating this syntax. It seems that you can only embed groups into an element named exactly " Group ". It then provides the rest of the grouping functionality.

 From person In data Group person By grpId = person.GroupId Into Group Select id = grpId, count = Group.Count