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

i have database table datetime column , want count how many records per day going 3 months. using query:

var mindate = datetime.now.addmonths(-3);  var stats = t in teststats t.date > mindate group t entityfunctions.truncatetime(t.date) g orderby g.key select new {    date = g.key,    count = g.count() };

that works fine, problem if there no records day day not in results @ all. example:

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

in short example want make 3/23/2008 = 0. in real query zeros should show between 3 months ago , today.

fabricating missing data not straightforward in sql. recommend getting data is in sql, joining in-memory list of relevant dates:

var stats = (from t in teststats t.date > mindate group t entityfunctions.truncatetime(t.date) g orderby g.key select new {    date = g.key,    count = g.count() }).tolist();  // hydrate query 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 dates         ds in dates.defaultifempty()         select new {             date = d,             count = ds == null ? 0 : ds.count         }).tolist(); 

you list of dates not in data , concatenate them.