LINQ to SQL使用GROUP BY和COUNT(DISTINCT)

Question

LINQ to SQL使用GROUP BY和COUNT(DISTINCT)

67

我需要执行以下的SQL查询：

select answer_nbr, count(distinct user_nbr)
from tpoll_answer
where poll_nbr = 16
group by answer_nbr

LINQ to SQL 查询。

from a in tpoll_answer 
where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct

转换成以下SQL查询：

select distinct answer_nbr, distinct user_nbr
from tpoll_answer
where poll_nbr = 16

目前为止，一切都很顺利。但是，在尝试对结果进行分组时，问题就出现了，因为我无法找到与我在此处编写的第一个查询相对应的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)

这反过来产生了以下丑陋且未经优化的 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

非常感谢您的帮助。

- Leandro López

6个回答

11

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()
};

- Alex Kamburov

我对这个语法感到兴奋，但生成的SQL与Marc Gravell引用的内容完全相同。 - Sam

1

这是一个简单清晰的示例，展示了LINQ中group by的工作原理。

http://www.a2zmenu.com/LINQ/LINQ-to-SQL-Group-By-Operator.aspx

- rs.emenu

抱歉，没有找到关于count(distinct)的内容。 - Ben

packages.GroupBy(p => p.OrderId).Count() - Simon_Weaver

统计整个包裹中的所有OrderId。OP问如何在特定组上进行COUNT(DISTINCT) - 即统计每个国家的城市数量。 - Sam

1

Linq to sql不支持Count(Distinct ...)。因此，您必须在代码中将.NET方法映射到Sql服务器函数（因此Count(distinct..)）并使用它。

顺便说一句，如果您以既不是VB.NET也不是C#的格式发布从工具包复制的伪代码，则无济于事。

- Frans Bouma

谢谢你，Frans。我正在考虑创建一个视图或存储过程。非常感谢你，Marc。 - Leandro López

@Leandro - 你也可以考虑使用表值函数（UDF）代替存储过程；系统可以更准确地获取元数据，并且在服务器上是可组合的。不过只适用于LINQ-to-SQL，而不是Entity Framework（据我所知）。 - Marc Gravell

1

以下是如何进行去重计数查询的方法。请注意，您需要过滤掉空值。

var useranswercount = (from a in tpoll_answer
where user_nbr != null && answer_nbr != null
select user_nbr).Distinct().Count();

如果你将这个代码与你现有的分组代码结合起来，我认为你会得到你的解决方案。

- GeekyMonkey

谢谢GeekyMonkey。列都是NOT NULL，所以少了一件要担心的事情。如果我没记错的话，我认为你的查询是总计数而不是按组计数。 - Leandro López

-5

我不会在Linq2SQL中费心去做这件事。创建一个存储过程来执行你想要的查询，然后在框架中创建该存储过程的对象，或者直接连接到它。

- user6504946

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marc Gravell · Accepted Answer

目前还没有直接支持COUNT(DISTINCT {x}))的方法，但你可以从一个IGrouping<,>对象模拟它（即group by的返回值）；很抱歉我只熟悉C#，所以你需要将它转化为VB...

 select new
 {
     Foo= grp.Key,
     Bar= grp.Select(x => x.SomeField).Distinct().Count()
 };

这里是一个Northwind示例：

    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);
        }
    }

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

然而结果是正确的-可以通过手动运行进行验证:

        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);
        }

带有定义：

class QueryResult
{
    public string Country { get; set; }
    public int Count { get; set; }
}