Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Filling in missing dates using a linq group by date query

Tags:

I have a Linq query that basically counts how many entries were created on a particular day, which is done by grouping by year, month, day. The problem is that because some days won't have any entries I need to back fill those missing "calendar days" with an entry of 0 count. My guess is that this can probably be done with a Union or something, or maybe even some simple for loop to process the records after the query.

Here is the query:

from l in context.LoginToken
 where l.CreatedOn >= start && l.CreatedOn <= finish
 group l by
 new{l.CreatedOn.Year, l.CreatedOn.Month, l.CreatedOn.Day} into groups
 orderby groups.Key.Year , groups.Key.Month , groups.Key.Day
     select new StatsDateWithCount {
                                    Count = groups.Count(),
                                     Year =  groups.Key.Year,
                                    Month = groups.Key.Month,
                                      Day = groups.Key.Day
                                                                  }));

If I have data for 12/1 - 12/4/2009 like (simplified):

12/1/2009 20
12/2/2009 15
12/4/2009 16

I want an entry with 12/3/2009 0 added by code.

I know that in general this should be done in the DB using a denormalized table that you either populate with data or join to a calendar table, but my question is how would I accomplish this in code?
Can it be done in Linq? Should it be done in Linq?


Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!