分类导航

程序问答发布时间：2022-06-02 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了使用GROUP BY和COUNT（DISTINCT）的LINQ to SQL，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决使用GROUP BY和COUNT（DISTinCT）的LINQ to SQL？

开发过程中遇到使用GROUP BY和COUNT（DISTinCT）的LINQ to SQL的问题如何解决？下面主要结合日常开发的经验，给出你关于使用GROUP BY和COUNT（DISTinCT）的LINQ to SQL的解决方法建议，希望对你解决使用GROUP BY和COUNT（DISTinCT）的LINQ to SQL有所启发或帮助；

尚无直接支持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 @R_197_3637@ = @"
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; }
}

解决方法

我必须执行以下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

任何帮助将不胜感激。