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.

Get it directly from the database? It's not on the database in the first place because you're *generating* the missing dates. It would probably be easier and more efficient to just use a for loop IMO. — mellamokb, Oct 07 '11 at 17:18

score 3 · Accepted Answer · answered Oct 07 '11 at 17:27

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.

score 1 · Answer 2 · answered Oct 07 '11 at 18:27

You can generate an array of dates given a start value and knowing the number of days that you want to return. I'm hard coding it here, but you could pull it from your activity list using Min and Max and determining the start value and number of days accordingly.

var allDays = Enumerable.Range(0,7).Select (i => new DateTime(2011,9,30).AddDays(i) );

Once you have that, you can either use a subselect or a join to do your aggregation:

var data = (from d in allDays
            select new {
                Date = d,
                Count = activities.Count (a => a.Date.Date == d)
            }).ToList();

score 0 · Answer 3 · answered Oct 07 '11 at 17:20

If you want to already get the result in one linq query, you'd have to perform this operation in database. Thus you have to have all the days in your db even if they have 0 activity. This means you have to create a new table.

In your scenario, I'd create a calendar table and outer join on that table to get the days that have 0 activities.

Linq: Count Groups of Rows Including 0 For Missing Rows

3 Answers3

Linked