Joins with a Numbers table in Entity Framework

Question

I'm trying to write a query using Entity Framework and Linq to Entities to get daily counts of incidents in a table, that'll be used for graphing. The problem is, I'd like to return a full list of days, even if there's no data available.

I found an answer suggesting using a numbers table, which is mostly working, however I'm having problems with the left outer join (which is what I think I need to do).

This simple query returns the last 7 days, including today:

var foo = from n in Numbers
      where n.NumberId > 0 && n.NumberId <= 7   
      let date = DbFunctions.AddDays(start, n.NumberId)
      select date;

But the full query, I can't figure out the proper join and group by syntax with a left outer join

var bar = from n in Numbers
          where n.NumberId > 0 && n.NumberId <= 7   
          let date = DbFunctions.AddDays(start, n.NumberId)
          join i in Incidents on EntityFunctions.TruncateTime(date.Value) equals EntityFunctions.TruncateTime(i.IncidentDate) into days
          from day in days.DefaultIfEmpty()
          group day by EntityFunctions.TruncateTime(day.IncidentDate) into g
          select new {
              g.Key,    
              Count = g.Count() 
          };

The final result should look something like:

Date      | Count
-------------
3/26/2014 | 0
3/25/2014 | 10
3/24/2014 | 9

....

score 0 · Accepted Answer · answered Mar 26 '14 at 21:00

I was able to solve it shortly after posting. The solution is:

var bar = from n in Numbers
          where n.NumberId > 0 && n.NumberId <= 7
          let date = DbFunctions.AddDays(start, n.NumberId)
          join i in Incidents on EntityFunctions.TruncateTime(date) equals EntityFunctions.TruncateTime(i.IncidentDate) into days
          select new {
              date,
              Count = days.Count()
           };

Joins with a Numbers table in Entity Framework

1 Answers1