Group record by hour in Enity Framwork and fill gaps

Question

There was a similar question before Grouping records hour by hour or day by day and filling gaps with zero or null and Grouping records hour by hour or day by day and filling gaps with zero or null in mysql but both solutuion use SQL. I would like to resolve this problem with code.

The easiest solution is to run:

var q = from i in XXX.Table
    let dt = p.Date
    group i by new { y = dt.Year, m = dt.Month, d = dt.Day, h = dt.Hour}
    select g;

but I have got a lot of gaps still to fill.

Is this query going to be composed into another query, or can we assume this (and the filled-in gaps) are going to be materialized? In other words, does the server have to generate the fills? — casperOne, Feb 28 '14 at 12:38
@casperOne I like to use this query as sub-query so the answer is yes the server should generate the fills — Piotr Stapp, Feb 28 '14 at 13:23
@SergeyBerezovskiy I think yes. I never had problems with EF + SQL Server in `DateTime` type — Piotr Stapp, Feb 28 '14 at 13:24
@Garath that probably was LINQ to SQL. EF will fail translating Date property retrieving — Sergey Berezovskiy, Feb 28 '14 at 13:27
You can use EntityFunctions.TruncateTime or DbFunctions.TruncateTime if you are on EF6. — Dismissile, Feb 28 '14 at 13:44
@SergeyBerezovskiy You are right, I wrote this code without checking:) — Piotr Stapp, Feb 28 '14 at 13:54
possible duplicate of [LINQ group by date - include empty days WITHOUT using join](http://stackoverflow.com/questions/17086120/linq-group-by-date-include-empty-days-without-using-join), except that your post is about hours. — Gert Arnold, Feb 28 '14 at 14:51

score 0 · Answer 1 · answered Feb 28 '14 at 19:16

You can fetch groups from server and fill gaps on client:

var startDate = new DateTime(2014, 1, 2);
var endDate = new DateTime(2014, 1, 3);

var dict = (from c in YourTable
  where c.YourDate >= startDate && c.YourDate <= endDate 
  let hours = DbFunctions.DiffHours(startDate, c.YourDate)
  group c by hours into g 
  select new {Hour = g.Key, Count = g.Count()}).ToDictionary(r => r.Hour.Value, r => r.Count);

var result = Enumerable.Range(0, (int)(endDate - startDate).TotalHours).Select(h => new {Date = startDate.AddHours(h), Count = dict.ContainsKey(h) ? dict[h] : 0});

Group record by hour in Enity Framwork and fill gaps

1 Answers1