C# 使用 GROUP BY 和 COUNT(DISTINCT) 的 LINQ to SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/448203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL using GROUP BY and COUNT(DISTINCT)
提问by Leandro López
I have to perform the following SQL query:
我必须执行以下 SQL 查询：
select answer_nbr, count(distinct user_nbr)
from tpoll_answer
where poll_nbr = 16
group by answer_nbr
The LINQ to SQL query
LINQ to SQL 查询
from a in tpoll_answer
where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct
maps to the following SQL query:
映射到以下 SQL 查询：
select distinct answer_nbr, distinct user_nbr
from tpoll_answer
where poll_nbr = 16
So far, so good. However the problem raises when trying to GROUP the results, as I'm not being able to find a LINQ to SQL query that maps to the first query I wrote here (thank you LINQPadfor making this process a lot easier). The following is the only one that I've found that gives me the desired result:
到现在为止还挺好。但是，在尝试对结果进行分组时会出现问题，因为我无法找到映射到我在此处编写的第一个查询的 LINQ to SQL 查询（感谢LINQPad让这个过程变得更容易）。以下是我发现的唯一一个给我想要的结果：
from answer in tpoll_answer where answer.poll_nbr = 16 _
group by a_id = answer.answer_nbr into votes = count(answer.user_nbr)
Which in turns produces the follwing ugly and non-optimized at all SQL query:
这反过来会产生以下丑陋且未优化的所有 SQL 查询：
SELECT [t1].[answer_nbr] AS [a_id], (
SELECT COUNT(*)
FROM (
SELECT CONVERT(Bit,[t2].[user_nbr]) AS [value], [t2].[answer_nbr], [t2].[poll_nbr]
FROM [TPOLL_ANSWER] AS [t2]
) AS [t3]
WHERE ([t3].[value] = 1) AND ([t1].[answer_nbr] = [t3].[answer_nbr]) AND ([t3].[poll_nbr] = @p0)
) AS [votes]
FROM (
SELECT [t0].[answer_nbr]
FROM [TPOLL_ANSWER] AS [t0]
WHERE [t0].[poll_nbr] = @p0
GROUP BY [t0].[answer_nbr]
) AS [t1]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [16]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
Any help will be more than appreciated.
任何帮助将不胜感激。
采纳答案by Marc Gravell
There isn't direct support for COUNT(DISTINCT {x})), but you can simulate it from an IGrouping<,>(i.e. what group byreturns); I'm afraid I only "do" C#, so you'll have to translate to VB...
没有对 的直接支持COUNT(DISTINCT {x}))，但您可以从IGrouping<,>（即group by返回的内容）中模拟它；恐怕我只会“做”C#，所以你必须翻译成 VB ......
select new
{
Foo= grp.Key,
Bar= grp.Select(x => x.SomeField).Distinct().Count()
};
Here's a Northwind example:
这是一个北风的例子：
using(var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out; // log TSQL to console
var qry = from cust in ctx.Customers
where cust.CustomerID != ""
group cust by cust.Country
into grp
select new
{
Country = grp.Key,
Count = grp.Select(x => x.City).Distinct().Count()
};
foreach(var row in qry.OrderBy(x=>x.Country))
{
Console.WriteLine("{0}: {1}", row.Country, row.Count);
}
}
The TSQL isn't quite what we'd like, but it does the job:
TSQL 不是我们想要的，但它完成了这项工作：
SELECT [t1].[Country], (
SELECT COUNT(*)
FROM (
SELECT DISTINCT [t2].[City]
FROM [dbo].[Customers] AS [t2]
WHERE ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1]
.[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [
t2].[Country]))) AND ([t2].[CustomerID] <> @p0)
) AS [t3]
) AS [Count]
FROM (
SELECT [t0].[Country]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] <> @p0
GROUP BY [t0].[Country]
) AS [t1]
-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
The results, however, are correct- verifyable by running it manually:
但是，结果是正确的 - 可以通过手动运行来验证：
const string sql = @"
SELECT c.Country, COUNT(DISTINCT c.City) AS [Count]
FROM Customers c
WHERE c.CustomerID != ''
GROUP BY c.Country
ORDER BY c.Country";
var qry2 = ctx.ExecuteQuery<QueryResult>(sql);
foreach(var row in qry2)
{
Console.WriteLine("{0}: {1}", row.Country, row.Count);
}
With definition:
有定义：
class QueryResult
{
public string Country { get; set; }
public int Count { get; set; }
}
回答by Frans Bouma
Linq to sql has no support for Count(Distinct ...). You therefore have to map a .NET method in code onto a Sql server function (thus Count(distinct.. )) and use that.
Linq to sql 不支持 Count(Distinct ...)。因此，您必须将代码中的 .NET 方法映射到 Sql 服务器函数（因此 Count(distinct.. )）并使用它。
btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.
顺便说一句，如果您以既不是 VB.NET 也不是 C# 的格式发布从工具包复制的伪代码，它没有帮助。
回答by GeekyMonkey
This is how you do a distinct count query. Note that you have to filter out the nulls.
这就是您执行非重复计数查询的方式。请注意，您必须过滤掉空值。
var useranswercount = (from a in tpoll_answer
where user_nbr != null && answer_nbr != null
select user_nbr).Distinct().Count();
If you combine this with into your current grouping code, I think you'll have your solution.
如果您将其与当前的分组代码结合起来，我想您就会有解决方案。
回答by rs.emenu
simple and clean example of how group by works in LINQ
LINQ 中 group by 如何工作的简单而清晰的示例
http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx
http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx
回答by Alex Kamburov
The Northwind example cited by Marc Gravell can be rewritten with the City column selected directly by the group statement:
Marc Gravell 引用的 Northwind 示例可以用 group 语句直接选择的 City 列重写：
from cust in ctx.Customers
where cust.CustomerID != ""
group cust.City /*here*/ by cust.Country
into grp
select new
{
Country = grp.Key,
Count = grp.Distinct().Count()
};
回答by user6504946
I wouldn't bother doing it in Linq2SQL. Create a stored Procedure for the query you want and understand and then create the object to the stored procedure in the framework or just connect direct to it.
我不会费心在 Linq2SQL 中这样做。为您想要并理解的查询创建一个存储过程，然后在框架中为存储过程创建对象，或者直接连接到它。
