如何在LINQ中通过SQL查询编写选择计数组? [英] How to write a select count group by SQL query in LINQ?

问题描述

我有这个查询，但是当我尝试用LINQ编写等效查询时，却得到了错误的SQL.

I have this query which works but when I try to write the equivalent in LINQ I get the incorrect SQL produced.

我的查询是:

SELECT COUNT(*)
FROM tableName
GROUP BY ColumnId

我尝试将其编写为:

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

但是在LINQPad中它正在生成SQL:

But looking in LINQPad it is producing the SQL:

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

我做错了什么?谢谢！

推荐答案

您的LINQ查询正在计算组数，但是您的SQL查询正在按组进行计数.你想要

Your LINQ query is counting the number of groups but your SQL query is producing the counts by group. You want

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

按组获取计数.

请注意，如果您要使用完全相同的SQL

Note that if you want exactly the same SQL you want

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

上面的第一个示例应该会更加有用，因为它还提供了每个组的ID.

The first example above should be a little more useful as it gives the ids of each group as well.

这篇关于如何在LINQ中通过SQL查询编写选择计数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！