Linq: group by year and month, and manage empty months

Question

I have the following Linq to group a list by year, then by month.

var changesPerYearAndMonth = list
              .GroupBy(revision => new { revision.LocalTimeStamp.Year, revision.LocalTimeStamp.Month })
              .Select(group => new { GroupCriteria = group.Key, Count = group.Count() })
              .OrderBy(x => x.GroupCriteria.Year)
              .ThenBy(x => x.GroupCriteria.Month);

My current output is the following:

Year 2005, month 1, count 469
Year 2005, month 5, count 487
Year 2005, month 9, count 452
Year 2006, month 1, count 412
Year 2006, month 5, count 470
...

As you can see, months without value, are not included in the query. I would like to include them, having the following output:

Year 2005, month 1,  count 469
Year 2005, month 2,  count 0
Year 2005, month 3,  count 0
...
Year 2005, month 12, count 0
Year 2006, month 1,  count 412
Year 2006, month 2,  count 0
...
Year 2006, month 12, count 0

In other words, I need to get also empty months.

Could I implement this with a Linq query? Thanks in advance

Answer 1

I think you want something like this:

var changesPerYearAndMonth =
    from year in Enumerable.Range(2005, 6)
    from month in Enumerable.Range(1, 12)
    let key = new { Year = year, Month = month }
    join revision in list on key
              equals new { revision.LocalTimeStamp.Year, 
                           revision.LocalTimeStamp.Month } into g
    select new { GroupCriteria = key, Count = g.Count() };

Note that:

• You need to know what years you're trying to come up with data for. You can fetch this from another query, of course, to find the minimum and maximum years involved - or maybe find the minimum and assume there will be nothing in the future (I don't know if this a valid assumption or not though)
• This will automatically be ordered correctly