CODEWITHSUNDEEP
linqgroup-bytime-series
Ryan
Ryan

Reputation: 6057

How to make zero counts show in LINQ query when getting daily counts?

I have a database table with a datetime column and I simply want to count how many records per day going back 3 months. I am currently using this query:

var minDate = DateTime.Now.AddMonths(-3);

var stats = from t in TestStats
where t.Date > minDate
group t by EntityFunctions.TruncateTime(t.Date) into g
orderby g.Key
select new
{
   date = g.Key,
   count = g.Count()
};

That works fine, but the problem is that if there are no records for a day then that day is not in the results at all. For example:

3/21/2008 = 5
3/22/2008 = 2
3/24/2008 = 7

In that short example I want to make 3/23/2008 = 0. In the real query all zeros should show between 3 months ago and today.

Upvotes: 2

Views: 1568

Answers (2)

Paul Fleming
Paul Fleming

Reputation: 24526

I agree with @D Stanley's answer but want to throw an additional consideration into the mix. What are you doing with this data? Is it getting processed by the caller? Is it rendered in a UI? Is it getting transferred over a network?

Consider the size of the data. Why do you need to have the gaps filled in? If it is known to be returning over a network for instance, I'd advise against filling in the gaps. All you're doing is increasing the data size. This has to be serialised, transferred, then deserialised.

If you are going to loop the data to render in a UI, then why do you need the gaps? Why not implement the loop from min date to max date (like D Stanley's join) then place a default when no value is found.

If you ARE transferring over a network and you still NEED a single collection, consider applying D Stanley's resolution on the other side of the wire.

Just things to consider...

Upvotes: 0

D Stanley
D Stanley

Reputation: 152634

Fabricating missing data is not straightforward in SQL. I would recommend getting the data that is in SQL, then joining it to an in-memory list of all relevant dates:

var stats = (from t in TestStats
where t.Date > minDate
group t by EntityFunctions.TruncateTime(t.Date) into g
orderby g.Key
select new
{
   date = g.Key,
   count = g.Count()
}).ToList();  // hydrate so we only query the DB once

var firstDate = stats.Min(s => s.date);
var lastDate = stats.Max(s => s.date);

var allDates = Enumerable.Range(1,(lastDate - firstDate).Days)
                         .Select(i => firstDate.AddDays(i-1));

stats = (from d in allDates
        join s in stats 
            on d equals s.date into dates
        from ds in dates.DefaultIfEmpty()
        select new {
            date = d,
            count = ds == null ? 0 : ds.count
        }).ToList();

You could also get a list of dates not in the data and concatenate them.

Upvotes: 3

Related Questions