PTLearner PTLearner - 2 months ago 6
C# Question

GroupBy and average a column in Linq

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; }
}

Answer

Maybe this will work?

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 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.