title: LINQ to SQL using GROUP BY and COUNT(DISTINCT)
body: <p>I have to perform the following SQL query:</p>
  
  <pre><code>select answer_nbr, count(distinct user_nbr)
  from tpoll_answer
  where poll_nbr = 16
  group by answer_nbr
  </code></pre>
  
  <p>The LINQ to SQL query </p>
  
  <pre><code>from a in tpoll_answer 
  where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct 
  </code></pre>
  
  <p>maps to the following SQL query:</p>
  
  <pre><code>select distinct answer_nbr, distinct user_nbr
  from tpoll_answer
  where poll_nbr = 16
  </code></pre>
  
  <p>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 <a href="http://www.linqpad.net/" rel="noreferrer">LINQPad</a> for making this process a lot easier). The following is the only one that I've found that gives me the desired result:</p>
  
  <pre><code>from answer in tpoll_answer where answer.poll_nbr = 16 _
  group by a_id = answer.answer_nbr into votes = count(answer.user_nbr)
  </code></pre>
  
  <p>Which in turns produces the follwing ugly and non-optimized at all SQL query:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Any help will be more than appreciated.</p>