title: Linq - Grouping by date and selecting count
body: <p>I am currently working through a problem where I would like to run a query which groups the results by the date selected.</p>
  
  <p>For this example, imagine a simple model like so:</p>
  
  <pre><code>public class User
  {
        public DateTime LastLogIn {get; set;}
        public string Name {get; set;}
  }
  </code></pre>
  
  <p>The solution I am looking for is to get a count of Users logged in by Date.
  In the database the DateTime are stored with both date and time components, but for this query I really only care about the date.</p>
  
  <p>What I currently have is this:</p>
  
  <pre><code>    context.Users
              .Where((x.LastLogIn  &gt;= lastWeek)    
                  &amp;&amp; (x.LastLogIn &lt;= DateTime.Now))
              .GroupBy(x =&gt; EntityFunctions.TruncateTime(x.LastLogIn))
              .Select(x =&gt; new
              {
                  Value = x.Count(),
                  Day = (DateTime)EntityFunctions.TruncateTime(x.Key)
              }).ToList();
  </code></pre>
  
  <p>The above however returns an empty list. </p>
  
  <p>End goal is to have a List of objects, which contain a Value (the count of users logged in on a day) and a Day (the day in question)</p>
  
  <p>Any thoughts?</p>
  
  <p>Upon changing the query to:</p>
  
  <pre><code>    context.Users
              .Where((x.LastLogIn  &gt;= lastWeek)    
                  &amp;&amp; (x.LastLogIn &lt;= DateTime.Now))
              .GroupBy(x =&gt; EntityFunctions.TruncateTime(x.LastLogIn))
              .Select(x =&gt; new
              {
                  Value = x.Count(),
                  Day = (DateTime)x.Key
              }).ToList();
  </code></pre>
  
  <p>it now returns a list with a single item, with the Value being the total count of Users that match the where clause, and the Day being the very first day.  It still hasn't seemed to be able to group by the days</p>
  
  <p><strong>NOTE:</strong> turns out the above code is right, I was just doing something else wrong.</p>
  
  <p>Sql that it is generating is (note might be very slight syntactical errors here with me adjusting it for the example):</p>
  
  <pre><code>SELECT 
  1 AS [C1], 
  [GroupBy1].[A1] AS [C2], 
   CAST( [GroupBy1].[K1] AS datetime2) AS [C3]
  FROM ( SELECT 
          [Filter1].[K1] AS [K1], 
          COUNT([Filter1].[A1]) AS [A1]
          FROM ( SELECT 
                   convert (datetime2, convert(varchar(255), [Extent1].[LastLogIn], 102) ,  102) AS [K1], 
                  1 AS [A1]
                  FROM [dbo].[Users] AS [Extent1]
                  WHERE (([Extent1].[LastLogIn] &gt;= @p__linq__1) AND ([Extent1].[LastLogIn] &lt;= @p__linq__2)
          )  AS [Filter1]
         GROUP BY [K1]
  )  AS [GroupBy1] 
  </code></pre>