[c#] LINQ to SQL using GROUP BY and COUNT(DISTINCT)

I have to perform the following SQL query:

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

The LINQ to SQL query

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

maps to the following SQL query:

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 LINQPad for making this process a lot easier). The following is the only one that I've found that gives me the desired result:

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:

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.

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.

btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.

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.

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.

btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.

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.

The Northwind example cited by Marc Gravell can be rewritten with the City column selected directly by the group statement:

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

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.

btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.

The Northwind example cited by Marc Gravell can be rewritten with the City column selected directly by the group statement:

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

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.

simple and clean example of how group by works in LINQ

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

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.

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.

btw, it doesn't help if you post pseudo code copied from a toolkit in a format that's neither VB.NET nor C#.

simple and clean example of how group by works in LINQ

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