title: Getting number of records by date in LINQ to SQL query even if no results
body: <p>I have written the following LINQ-to-SQL to enable me to get a total number of records by day.</p>
  
  <p>There are a few modifications I'd like to make to it in order to have it fit my needs. I have made various attempts but haven't been able to get it right.</p>
  
  <p>I'd like to list the last 9 days, even if there were no 'opens' during this time.
  I'd also like to group by Day/Month instead of just Day as I'm doing below.</p>
  
  <p>An example of the output I'd love to get would be:</p>
  
  <pre><code>DateString   Opens
  ===================
   9/5         0
  10/5         0
  11/5         3
  </code></pre>
  
  <p>etc...</p>
  
  <pre><code>public List&lt;OpensOverTimeViewModel&gt; GetOpensOverTimeViewModel(int eId)
  {
  
      DateTime lastDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).AddDays(-9);
      DateTime currentDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day).AddDays(1);
  
      var summary = (from p in db.Opens
                      where (p.ElementId == eId) &amp;&amp; (p.Timestamp &gt;= lastDate) &amp;&amp; (p.Timestamp &lt;= currentDate)
                      let k = new
                      {
                          Day = p.Timestamp.Day
                      }
                      group p by k into t
                      select new OpensOverTimeViewModel
                      {
                          DateString = t.Key.Day,
                          TotalOpens = t.Count(),
                          ElementName = ""
                      }).ToList();
  
      return summary;
  }
  </code></pre>
  
  <p>Any help on how best to tackle this would be much appreciated.</p>