LINQ to SQL using GROUP BY and COUNT DISTINCT

Question

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

User · Answer

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

User · Answer

There isn t direct support for COUNT DISTINCT  x     but you can simulate it from an IGrouping lt   gt   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   gt  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   gt  x City  Distinct   Count                                 foreach var row in qry OrderBy x  gt 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   lt  gt   p0            AS  t3        AS  Count  FROM       SELECT  t0   Country      FROM  dbo   Customers  AS  t0      WHERE  t0   CustomerID   lt  gt   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 lt QueryResult gt  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

User · Answer

simple and clean example of how group by works in LINQ  http   www a2zmenu com LINQ LINQ-to-SQL-Group-By-Operator aspx

User · Answer

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  amp  amp  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

User · Answer

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

User · Answer

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  amp  amp  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

User · Answer

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

User · Answer

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

User · Answer

There isn t direct support for COUNT DISTINCT  x     but you can simulate it from an IGrouping lt   gt   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   gt  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   gt  x City  Distinct   Count                                 foreach var row in qry OrderBy x  gt 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   lt  gt   p0            AS  t3        AS  Count  FROM       SELECT  t0   Country      FROM  dbo   Customers  AS  t0      WHERE  t0   CustomerID   lt  gt   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 lt QueryResult gt  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

User · Answer

simple and clean example of how group by works in LINQ  http   www a2zmenu com LINQ LINQ-to-SQL-Group-By-Operator aspx

User · Answer

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  amp  amp  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

User · Answer

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

User · Answer

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

User · Answer

There isn t direct support for COUNT DISTINCT  x     but you can simulate it from an IGrouping lt   gt   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   gt  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   gt  x City  Distinct   Count                                 foreach var row in qry OrderBy x  gt 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   lt  gt   p0            AS  t3        AS  Count  FROM       SELECT  t0   Country      FROM  dbo   Customers  AS  t0      WHERE  t0   CustomerID   lt  gt   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 lt QueryResult gt  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

User · Answer

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

User · Answer

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  amp  amp  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

User · Answer

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

User · Answer

There isn t direct support for COUNT DISTINCT  x     but you can simulate it from an IGrouping lt   gt   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   gt  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   gt  x City  Distinct   Count                                 foreach var row in qry OrderBy x  gt 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   lt  gt   p0            AS  t3        AS  Count  FROM       SELECT  t0   Country      FROM  dbo   Customers  AS  t0      WHERE  t0   CustomerID   lt  gt   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 lt QueryResult gt  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

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

Examples related to c#

Examples related to linq

Examples related to linq-to-sql