entity framework grouping by datetime

Question

Every 5 minutes a row in a sql server table is added. The fields are: DateTime timeMark,Int total.

Using entity framework I want to populate a new list covering a whole week of five minute values using an average of the totals from the last three months.

How would I accomplish this with Entity Framework?

Answer 1

Assuming your log is really exact on the "five mintues", and that I understood well , you want a list with 7 day * 24 hours * (60/5) minutes, so 2016 results ?

//define a startDate
var beginningDate = <the date 3 month ago to start with>;

//get the endDate
var endDate = beginningDate.AddMonths(3);
var list = myTable.Where(m => m.TimeMark >= beginningDate && m.TimeMark <=endDate)
           //group by dayofWeek, hour and minute will give you data for each distinct day of week, hour and minutes
           .GroupBy(m => new {
               dayofWeek = SqlFunctions.DatePart("weekday", m.TimeMark),
               hour = SqlFunction.DatePart("hour", m.TimeMark),
               minute = SqlFunctions.DatePart("minute", m.TimeMark)
           })
           .Select(g => new {
               g.Key.dayofWeek, 
               g.Key.hour, 
               g.Key.minute,
               total = g.Average(x => x.Total)
           });