GroupBy and average a column in Linq

Question

I have this piece of code regarding the bellow table:

Date          Hour    Value (string)
2016-01-01    00:00   10
2016-01-01    00:00   12
2016-01-01    00:00   11
2016-01-01    00:01   10
2016-01-01    00:01   10
2016-01-01    00:01   12

I would like to group this by date and hour and make an average of value (converting it to double).

I've tried several solutions but no success when it comes to have a result like this:

Date-Hour            Value (string)
2016-01-01 - 00:00   11
2016-01-01 - 00:01   10,6

I've tried the bellow linq query to get it:

...
var data = (from ent in db.T060_DATA
            where ent.id_indicador == id
            group ent by new { ent.data, ent.hora } into g
            select new { g.Key, List = g.ToList(), Valor = g.Average(val => double.Parse(val.valor)) }).ToList();

I want this results to fill a List<ChartData>:

public class ChartData
{
    public string data_hora { get; set; }
    public double valor { get; set; }
}

score 1 · Accepted Answer · edited May 23 '17 at 10:34

1

Maybe this will work?

var data = (from ent in db.T060_DATA.AsEnumerable()
            where ent.id_indicador == id
            group ent by new { ent.data, ent.hora } into g
            select new ChartData { data_hora = g.Key.data+" - "+g.Key.hora, Valor = g.Average(o => double.Parse(o.valor))}).ToList();

Moreover I think you should use DateTime instead of string for data_hora property in ChartData.

Edit
LINQ to Entities does not work with double.Parse so the simplest solution would be to add db.T060_DATA.AsEnumerable() in the first line and do all the calculations in memory, for more info please refer this other questions 1 2 3.

edited May 23 '17 at 10:34

Community

1
1

answered Sep 30 '16 at 11:59

YuvShap

3,825
2
10
24

cant parse string to double inside linq why ? how should i solve this ? – PTLearner Sep 30 '16 at 12:05
LINQ to Entities does not recognize the method 'Double ToDouble(System.String)' method, and this method cannot be translated into a store expression. – PTLearner Sep 30 '16 at 12:09
Can you try Convert.ToDouble instead of double.parse? – YuvShap Sep 30 '16 at 12:10
Maybe try to put "db.T060_DATA.AsEnumerable()" in the first line? – YuvShap Sep 30 '16 at 12:13

GroupBy and average a column in Linq

1 Answers1