LINQ to SQL с использованием GROUP BY и COUNT(DISTINCT)

Я должен выполнить следующий 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)

который в свою очередь производит follwing уродливый и не оптимизированный вообще 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

любая помощь будет оценили более чем.

6 54
c# linq linq-to-sql

6 ответов:

нет прямой поддержки COUNT(DISTINCT {x})), но вы можете имитировать его с IGrouping<,> (то есть что group by returns); боюсь, что я только "делаю" C#, поэтому вам придется перевести на VB...

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

вот пример "Борей":

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

пример "Борей" цитируется Марк Gravell можно переписать с колонкой города выбран непосредственно в заявлении группы: 

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

Linq to sql не поддерживает Count (Distinct ...). Поэтому вам нужно сопоставить метод .NET в коде с функцией Sql server (таким образом, Count(distinct.. )) и использовать это.

кстати, это не поможет, если вы публикуете псевдо-код, скопированный из инструментария в формате, который не является ни VB.NET и C#тоже.

1

Это то, как вы делаете отдельный запрос count. Обратите внимание, что вы должны отфильтровать нули.

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

Если вы объедините это с вашим текущим кодом группировки, я думаю, у вас будет свое решение.

1

простой и чистый пример того, как group by работает в LINQ

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

1

Я бы не стал делать это в Linq2SQL. Создайте хранимую процедуру для запроса, который вы хотите и понимаете, а затем создайте объект для хранимой процедуры в рамках или просто подключитесь непосредственно к ней.

-4