score:103

Accepted answer

there isn't direct support for count(distinct {x})), but you can simulate it from an igrouping<,> (i.e. what group by returns); i'm afraid i only "do" c#, so you'll have to translate to 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:

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

score:-4

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.

score:1

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#.

score:1

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.

score:1

simple and clean example of how group by works in linq

http://www.a2zmenu.com/linq/linq-to-sql-group-by-operator.aspx

score:11

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

Related Query

More Query from same tag