Linq Group by Week Number Including 0 Record Weeks

Question

I'm trying to get a dataset for an SSRS Line Chart. I am trying to show the number of records per week of the year for the last six months. In order for the line chart to be accurate, I have to show weeks that have zero records.

I want to provide the line chart with data that looks like this:

Year  Week  Count
2014  52    13
2015  1     0
2015  2     16

The following linq query gets everything I need minus the weeks that have zero records:

 list = (from t in context.Cats.Where(
                     t => t.Name == "Fluffy" &&
                  (t.Recorded >= start && t.Recorded <= end)
                      ).AsEnumerable()
                        group t by new { Year = t.Recorded.Year, WeekNumber = (t.Recorded - new DateTime(t.Recorded.Year, 1, 1)).Days / 7 } into ut
                        select new TrendRecord
                        {
                            Year = ut.Key.Year,
                            Week = ut.Key.WeekNumber,
                            Count = ut.Count()
                        }).OrderBy(t => t.Year).ThenBy(t => t.Week).ToList<TrendRecord>();

I've looked at this and this SO questions but neither seems to quite fit my predicament.

I'm leaning towards creating a list with all possible week numbers between the start and end dates and then left joining my data to it somehow so that weeks with 0 records show up.

Is this an acceptable approach to this problem(please show me how if so)? Is there a way to do this in one query without the need for a separate list to join to?

Not a solution but a question which is important for calculation of weeks. Are you sure that you want to count the weeks of the year by beginning the first week with Jan 01 not regarding the weekday? Its very unusual. Normally calendar weeks are counted by beginning each week with a special weekday with the consequence that a week overlaps to the new year. — Fratyx, Oct 29 '14 at 20:52
I am aware that the way I have it now will cause my week numbers to be off a bit. That's a challenge for another day though haha. This line chart is actually going to be a sparkline so the user is not concerned with the actual week numbers(since they won't see any of the axes). The important thing, for now at least, is that the sparkline contains data for the last six months and that zero record weeks are represented. Because deadline :-( — Bruno, Oct 29 '14 at 21:01
Creating a list of weeks and outer joining (by LINQ to objects) is the way to go. — Gert Arnold, Oct 29 '14 at 21:11

score 1 · Accepted Answer · answered Oct 29 '14 at 21:31

You will need a list of weeks and do some kind of LEFT OUTER JOIN using Linq.

Something like this should help you get started

    var weeks = new[]
    {
        new {Year = 2014, Week = 52},
        new {Year = 2015, Week = 1},
        new {Year = 2015, Week = 2},
    };

    var listWithEmptyWeeksIncluded = 
        from w in weeks
        from l in list.Where(x => x.Year == w.Year && x.Week == w.Week).DefaultIfEmpty()
        select new TrendRecord
                    {
                        Year = w.Year,
                        Week = w.Week,
                        Count = l == null ? 0 : l.Count
                    };

Of course, the weeks should be generated on the basis of your variables start and end

Also, you should be using a standard week calculation, for instance the ISO week standard, so that weeks that span two years are handled correctly.

Linq Group by Week Number Including 0 Record Weeks

1 Answers1