Linq: Count Groups of Rows Including 0 For Missing Rows

Question

I'd like to summarize a list of activities into the number of occurrences by Day for the last week, using Linq to Entities. For example, let's say I have data like this in my database:

Id | Date
1  | 2011-09-30 0:00:0
2  | 2011-10-02 0:00:00  
3  | 2011-10-02 0:00:00
4  | 2011-10-02 0:00:00
5  | 2011-10-04 0:00:00
6  | 2011-10-04 1:30:30
7  | 2011-10-04 0:00:00
8  | 2011-10-06 0:00:00

and let's say today's date is 2011-10-07

I'd like to generate the following:

Date        | Count
2011-10-01  | 0
2011-10-02  | 3
2011-10-03  | 0
2011-10-04  | 3
2011-10-05  | 0
2011-10-06  | 1
2011-10-07  | 0

Here's a sample technique I can use to group the occurrences by date, but I'm missing the zeros.

// Using Linq to Objects for demonstration purpose only
var activities = new List<Activity>();
activities.Add(new Activity { Id = 1, Date = new DateTime(2011, 9, 30)});
activities.Add(new Activity { Id = 2, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 3, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 4, Date = new DateTime(2011, 10, 2)});
activities.Add(new Activity { Id = 5, Date = new DateTime(2011, 10, 4)});
activities.Add(new Activity { Id = 6, Date = new DateTime(2011, 10, 4, 1, 30, 30) });
activities.Add(new Activity { Id = 7, Date = new DateTime(2011, 10, 4)});
activities.Add(new Activity { Id = 8, Date = new DateTime(2011, 10, 6)});

var data = (from a in activities
            group a by a.Date.Date into g
            where g.Key > DateTime.UtcNow.AddDays(-7)
            select new {
                Date = g.Key,
                Count = g.Count()
            }).ToList();

Here is the result:

Date        | Count
2011-10-02  | 3
2011-10-04  | 3
2011-10-06  | 1

Anyone know how I can include the missing zeros using Linq to Entities? I could always enumerate the results once I have it in memory, but it would be nice to get it directly from the database.

Answer 1

It might be just as easy to generate with a loop since the data is not available in the database already, and making it through LINQ will be complicated:

var firstDate = data.Min(d => d.Date).AddDays(-1);
var lastDate = data.Max(d => d.Date).AddDays(1);

for (var date = firstDate; date <= lastDate; date = date.AddDays(1))
    if (!data.Exists(d => d.Date == date))
        data.Add(new { Date = date, Count = 0 });

data = data.OrderBy(d => d.Date);
// do something with data

Unless your data set is really, really large, this in-memory querying and adjustment of the list will be peanuts compared to the time it takes for the database request.