title: Linq - group by datetime for previous 12 months - include empty months
body: <p>I have a scenario whereby I need to retrieve a count of objects grouped by the month of a datetime field.</p>
  
  <p>I found the following post which gets me part of the way there...</p>
  
  <p><a href="https://stackoverflow.com/questions/4245876/linq-group-by-year-and-month-and-manage-empty-months">Linq: group by year and month, and manage empty months</a></p>
  
  <p>...but I need to list the previous 12 months from today's date and the count of objects for each month, which is where I'm struggling.</p>
  
  <p>I've seen a few other posts with similar issues/solutions but I chose the above one as it's also a requirement to produce a record for any months with a count of 0.</p>
  
  <p>Thanks for any help I can get on this.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>OK, I got a little further thanks to Enigmativity (Thanks for taking the time!):</p>
  
  <pre><code>var news = from s in db.NewsItems
                     where s.SubmittedDate &gt; first
                     select new 
                     {
                         Date = s.SubmittedDate,
                         Title = s.Title,
                     };
  
  var grouping = from g in news.AsEnumerable()
                         select new NewsCountCollection
                         (
                             g.Date,
                             g.Title
                         );
  
  var lookup = grouping.ToLookup(x =&gt; x.Month, x =&gt; x.Title);
  
  var counts = from n in Enumerable.Range(-11, 12)
                      let Month = last.AddMonths(n)
                      select new
                      {
                          Month,
                          Count = lookup[Month].Count(),
                      };
  
  var countList = from c in counts.AsEnumerable()
                          select new NewsCountMonthList
                          (
                              c.Month.ToString("MMMM"),
                              c.Count
                          );
  </code></pre>
  
  <p>...and the following</p>
  
  <pre><code>public class NewsCountCollection
  {
      public DateTime Month { get; set; }
      public string Title { get; set; }
  
      public NewsCountCollection(DateTime date, string title)
      {
          this.Month = new DateTime(date.Year, date.Month, 1);
          this.Title = title;
      }
  
  }
  
  public class NewsCountMonthList
  {
      public string Month { get; set; }
      public int Count { get; set; }
  
      public NewsCountMonthList(string month, int count)
      {
          this.Month = month;
          this.Count = count;
      }
  }
  </code></pre>
  
  <p>...seems very inefficient though...I can't help thinking there must be a better way than this. Am I on the right track?</p>