How to write the selected counting group by SQL query in LINQ?

Question

I have this query that works, but when I try to write the equivalent in LINQ, I get the wrong SQL.

My request:

SELECT COUNT(*) FROM tableName GROUP BY ColumnId 

I tried to write it as:

 tableName.GroupBy(x => x.ColumnId).Count() 

But, looking in LINQPad, it produces SQL:

 SELECT COUNT(*) AS [value] FROM ( SELECT NULL AS [EMPTY] FROM [tableName] AS [t0] GROUP BY [t0].[ColumnId] ) AS [t1] 

What am I doing wrong? Thanks!

Answer 1

Your LINQ query counts the number of groups, but your SQL query counts by group. Do you want to

 var counts = tableName.GroupBy(x => x.ColumnId) .Select(g => new { g.Key, Count = g.Count() }); 

to get group counts.

Note that if you want the exact same SQL you want,

 var counts = tableName.GroupBy(x => x.ColumnId) .Select(g => g.Count()); 

The first example above should be a little more useful, as it also gives the identifiers of each group.

Answer 2

Try tableName.GroupBy(x => x.ColumnId).Select(x => x.Count())