title: LINQ query is too slow, Equivalent SQL is fine
body: <p>I am trying to query for daily active users in a database using C# and LINQ. I have a SQL query that works fine. It finishes running in &lt; 4 seconds. </p>
  
  <p>My table is called Logins, with properties: (DATETIME)Date, (GUID)UserId, etc</p>
  
  <pre><code>SELECT CAST([DATE] AS DATE) Date , COUNT (DISTINCT USERID) DailyActives
    FROM  Logins
    GROUP BY CAST([DATE] AS DATE)
    ORDER BY CAST([DATE] AS DATE)
  </code></pre>
  
  <p>Which counts the distinct users that logged in on that day. My LINQ query that gives the correct answer, but very slowly (Many minutes). Here I am selecting from a specific month</p>
  
  <pre><code>from rows in Logins
  where rows.Date.Month == 04 &amp;&amp; rows.Date.Year == 2013 
  group rows by rows.Date.Day into MyGroup
  orderby MyGroup.Key
  select new
      {
        LoginDate = MyGroup.Key,
        Count = MyGroup.Select(u =&gt; u.UserId).Distinct().Count()
  
      }
  </code></pre>
  
  <p>After testing, it seems like the Count statement takes forever when I have to Select(u=>u.UserId) because I think it has to map back to the list of UserId's. How can i write the query more efficiently?</p>
  
  <p>Any help is appreciated. Thanks!</p>