Linq: count line groups, including 0 for missing lines

Question

I would like to summarize the list of activities by the number of entries by day over the past week using Linq to Entities. For example, let's say I have such data 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 that today is 2011-10-07

I would like to create 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 is an example of a method that I can use to group events by date, but I don't have enough 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 

Does anyone know how I can include missing zeros using Linq for Entities? I could always list the results as soon as I have it in memory, but it would be nice to get it directly from the database.

Answer 1

Generating with a loop is just as easy since the data is no longer available in the database, and it will be difficult to do this through LINQ:

 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 

If your dataset is really, really big, this query and setting the list inside memory will be peanuts compared to the time it takes to query the database.

Answer 2

You can create an array of dates with an initial value and find out the number of days that you want to return. It's hard for me to code it, but you can get it out of the activity list using Min and Max and determine the initial value and the number of days, respectively.

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

Once you do this, you can either use the subtitle or join your aggregation:

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

Answer 3

If you want to get the result in a single linq query, you will have to perform this operation in the database. Therefore, you should have all the days in your dB, even if they have activity. This means that you need to create a new table.

In your scenario, I would create a calendar table and an outer join in this table to get days that have 0 actions.