Linq group by month/year with empty months for past 12 months

Question

How would I get this query to get the monthly count data for the past 12 months? I don't want to hard code the range, I want to use the current date DateTime.Now and get all the data for the past 12 months from that. I am trying to avoid adding a calendar table to the database and do this just using LINQ.

Some months might not have any data but I still need a count of 0 for those.

For example. If my data contains

Date     Count
12/2/2013, 4
10/1/2014, 1
11/5/2014, 6

The results should be, using the current date of 11/9/2014

11/2013, 0
12/1013, 4
 1/2014, 0
 2/2014, 0
 3/2014, 0
 4/2014, 0
 5/2014, 0
 6/2014, 0
 7/2014, 0
 8/2014, 0
 9/2014, 0
10/2014, 1
11/2014, 6

I can't get it to work. I think it's how I'm using Range but I'm not sure.

TimeSpan ts = new TimeSpan(365, 0, 0, 0);
DateTime yearAgo = DateTime.Now.Subtract(ts);

var changesPerYearAndMonth =
    from year in Enumerable.Range(yearAgo.Year, 1)
    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() };

I have modified the answer from this this link as a starting point. Linq: group by year and month, and manage empty months

I just found this article that is the same question but unanswered Linq - group by datetime for previous 12 months - include empty months

Answer 1

To get the past twelve months, use

var now = DateTime.Now;
var months = Enumerable.Range(-12, 12)
    .Select(x => new { 
        year = now.AddMonths(x).Year, 
        month = now.AddMonths(x).Month });

To be safe you should first move 'now' to the start of the month to avoid any end-of-month effects with AddMonth.

var now = DateTime.Now;
now = now.Date.AddDays(1-now.Day);

Complete example:-

 var list = new [] {
            new { LocalTimeStamp = DateTime.Parse("12/2/2013"), count = 4},
            new { LocalTimeStamp = DateTime.Parse("10/1/2014"), count = 1 },
            new { LocalTimeStamp = DateTime.Parse("11/5/2014"), count = 6}
        };

        var now = DateTime.Now;
        now = now.Date.AddDays(1-now.Day);
        var months = Enumerable.Range(-12, 13)
            .Select(x => new { 
                year = now.AddMonths(x).Year, 
                month = now.AddMonths(x).Month });

        var changesPerYearAndMonth =
            months.GroupJoin(list, 
                m => new {month = m.month, year = m.year}, 
                revision => new { month = revision.LocalTimeStamp.Month,
                                  year = revision.LocalTimeStamp.Year},
                (p, g) => new {month = p.month, year = p.year, 
                               count = g.Sum(a => a.count)});


        foreach (var change in changesPerYearAndMonth)
        {
            Console.WriteLine(change.month + " " + change.year +" " + change.count);
        }

Answer 2

You don't need a 3-way join, you just need to filter your data before grouping.

1) Query expression syntax

// since your list item type was not posted, anyway same access as your LocalTimeStamp property
list = new List<DateTime>();
DateTime aYearAgo = DateTime.Now.AddYears(-1);
var dateslastYear = from date in list
                 where date > aYearAgo
                 group date by new { date.Year, date.Month } into g
                 select new { GroupCriteria = g.Key, Count = g.Count() };

2) Chained

dateslastYear = list.Where (d=>d>aYearAgo)
                    .GroupBy (date=>new{date.Year, date.Month });

3) If you want grouping by year/month pairs, including records of not existent entries, and also omitting those pairs that are older than a year occurring with the joined Enumerable.Range call:

  var thisYearPairs = from m in Enumerable.Range(1, DateTime.Now.Month)
                      select new { Year = DateTime.Now.Year, Month = m };
  var lastYearPairs = from m in Enumerable.Range(DateTime.Now.Month, 12 - DateTime.Now.Month + 1)
                      select new { Year = DateTime.Now.Year - 1, Month = m };
  var ymOuter = from ym in thisYearPairs.Union(lastYearPairs)
                join l in list on new { ym.Year, ym.Month } equals new { l.Year, l.Month } into oj
                from p in oj.DefaultIfEmpty()
                select new { a = ym, b = p == null ? DateTime.MinValue : p };
  var ymGroup = from ym in ymOuter
                group ym by ym into g
                select new { GroupCriteria = g.Key.a, Count = g.Key.b == DateTime.MinValue ? 0 : g.Count() };

Answer 3

You are taking the range for the 12 months of last year only but you actually want the last twelve months.

You can do this using a Enumerable.Range and the AddMonths method:

var changesPerYearAndMonth =
    from month in Enumerable.Range(0, 12)
    let key = new { Year = DateTime.Now.AddMonths(-month).Year, Month = DateTime.Now.AddMonths(-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() };

Answer 4

public int YearDiff(DateTime a, DateTime b)
{
    return (int) Math.Floor((a.Year + a.Month / 100.0 + a.Day / 10000.0) - (b.Year + b.Month / 100.0 + b.Day / 10000.0));
}