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 =...