Using linq to group by dates with values for empty data

Question

I'm using LINQ to group ratings by month for the last 12 months and outputting JSON like this:

var startDate = DateTime.Now.AddMonths(-13);

var ratings = db.Ratings
    .Where(x => x.RatingDate > startDate)
    .GroupBy(c => new { Year = c.RatingDate.Year, Month = c.RatingDate.Month })
    .ToList()
    .Select(c => new
    {
        Date = new DateTime(c.Key.Year, c.Key.Month, 1),
        Month = new DateTime(c.Key.Year, c.Key.Month, 1).ToString("MMM"),
        Average = c.Average(d => d.Rating)
    })
    .OrderBy(a => a.Date);

return Json(new
    {
        Average = ratings.Select(x=>x.Average),
        Dates = ratings.Select(x=>x.Month)
    });

If I have data for October and November, then my result looks like this:

Average: [3.5, 4] Dates: ["Oct", "Nov"]

How do I prefill up to the previous 12 months with 0's? So I should get results from Dec 2015 to Nov 2016 with Dec 2015 -> Sept 2016 showing as 0's.

You need to use a join (join your result with a list of months). See this answer: http://stackoverflow.com/a/4246004/1732224 — Fer García, Nov 28 '16 at 06:32
It's not a great example as I'm looking for how to go back 12 months from now, not a fixed date range. Also their query / approach is quite different to mine — Evonet, Nov 28 '16 at 08:30

score 0 · Answer 1 · edited May 23 '17 at 12:01

0

I ended up following the link from Far Garcia and changing the way my query worked. I ran into some other problems but was finally resolved in How to get Average of grouped results in Linq query

edited May 23 '17 at 12:01

Community

1
1

answered Nov 29 '16 at 01:51

Evonet

3,600
4
37
83

Nkosi · Accepted Answer · 2016-11-29T02:08:02.957

Here is another way very similar to how you ended up doing it.

var now = DateTime.Now.AddDays(1 - DateTime.Now.Day);
var months = Enumerable.Range(-11, 12)
    .Select(m => new DateTime(now.AddMonths(m).Year, now.AddMonths(m).Month, 1));

var ratingsByMonth =
    from date in months
    let month = new { Year = date.Year, Month = date.Month }
    join r in ratings
    on month equals new { r.RatingDate.Year, r.RatingDate.Month }
    into g
    select new {
        Date = month,
        Rating = g.Count() > 0 ? g.Average(x => x.Rating) : 0
    };

Using linq to group by dates with values for empty data

2 Answers2