How to get hourly averages of my data

Question

I have a table called 'Samples' with the following columns: Id, Type, Quantity, Unit, SampleTime.

I have the following statement that calculates the daily weight average:

weightchart.data = db.Samples
    .Where(n => n.Type == "weight")
    .GroupBy(n => DbFunctions.TruncateTime(n.SampleTime))
    .Select(item => new ChartData()
{
    timestamp = item.Key,
    average = item.Average(a => a.Quantity),
    max = item.Max(a => a.Quantity),
    min = item.Min(a => a.Quantity),
    count = item.Count()
}).OrderBy(n => n.timestamp).ToList();
charts.Add(weightchart);

Now I would like to get the hourly average. I can't figure out how to do this. I'm new to c#, linq and entities.

Update: I've updated my statement to this:

weightchart.data = db.Samples.Where(n => n.Type == "weight").GroupBy(n => new { Date = n.SampleTime.Date, Hour = n.SampleTime.Hour }).Select(item => new ChartData()
{
    timestamp = new DateTime(item.Key.Date.Year, item.Key.Date.Month, item.Key.Date.Day, item.Key.Date.Hour, 0, 0),
    average = item.Average(a => a.Quantity),
    max = item.Max(a => a.Quantity),
    min = item.Min(a => a.Quantity),
    count = item.Count()
}).OrderBy(n => n.timestamp).ToList();
charts.Add(weightchart);

However I get this exception:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code Additional information: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

You could try using `SqlFunctions.DatePart`, but I'm not sure what EF versions support it. — MarcinJuraszek, Mar 27 '15 at 03:41
Are you able to give me some sample code? I've google for this and lots of the examples I cannot use as seems most people aren't using entities but Linq to Sql I believe. — rukiman, Mar 27 '15 at 03:52
Please post your answer as an answer, not ad an edit to the question. — abatishchev, Mar 30 '15 at 04:15

score 0 · Accepted Answer · edited May 23 '17 at 12:22

0

see update below

Assuming the type of SampleTime is a System.DateTime, you could change the GroupBy to .GroupBy(n => n.SampleTime.Hour), and that would let you compute the average per hour. Be aware that DateTime.Hour returns a hours from 0 to 23.

Try something like the following. I've slightly simplified, but if you can verify that it works, you can reshape it to the full thing you are trying. Also, it's hard for me to test it, because I'd have construct sample data first). An explanation for what is going on (a LINQ to SQL limitation) is here and a helpful hint on how to more easily get around the limitation via AsEnumerable() is here.

var dataAsList = (from n in db.Samples.Where(n => n.Type == "weight").AsEnumerable()
group n by new { Date = n.SampleTime.Date, Hour = n.SampleTime.Hour } into g
select new 
{
    dategrouping = g.Date,
    hourgrouping = g.Hour,
    average = g.Average(a => a.Quantity),
    max = g.Max(a => a.Quantity),
    min = g.Min(a => a.Quantity),
    count = g.Count()
}).ToList();

edited May 23 '17 at 12:22

Community

1
1

answered Mar 27 '15 at 04:06

DWright

9,258
4
36
53

SampleTime is of DateTime. I think you meant n.SampleTime.Hour? As the code you presented does not compile. – rukiman Mar 27 '15 at 04:53
Looks like you are using type names as member names in your anonymous object: `new { Date = n.SampleTime.Date, Hour = n.SampleTime.Hour }`. Try something like `new { theDate = n.SampleTime.Date, theHour = n.SampleTime.Hour }` and see if that helps. Of course you may have to change the names elsewhere in the query, too. – DWright Mar 27 '15 at 05:24
I updated my answer to give more insight into the problem you now have, and a possible solution. – DWright Mar 28 '15 at 20:56
See my edited question for the final answer, thanks to the response from DWright's for his code sample. – rukiman Mar 30 '15 at 04:10

How to get hourly averages of my data

1 Answers1