Evonet Evonet - 4 days ago 5
C# Question

How to get Average of grouped results in Linq query

I have a linq query that gets ratings from the previous 12 months:

var ratingsByMonth =
from month in Enumerable.Range(0, 12)
let key = new { Year = DateTime.Now.AddMonths(-month).Year, Month = DateTime.Now.AddMonths(-month).Month }
join groupedRating in ratings on key
equals new
{
groupedRating.RatingDate.Year,
groupedRating.RatingDate.Month
} into g
select new {
Date = key,
Rating = g.Select(x=>x.Rating)
};


ratingsByMonth returns the following JSON from my data set:

{Date: {Year: 2016, Month: 11}, Rating: [4]}
{Date: {Year: 2016, Month: 10}, Rating: [5, 5, 4]}
{Date: {Year: 2016, Month: 9}, Rating: []}
{Date: {Year: 2016, Month: 8}, Rating: []}
{Date: {Year: 2016, Month: 7}, Rating: []}
{Date: {Year: 2016, Month: 6}, Rating: []}
{Date: {Year: 2016, Month: 5}, Rating: []}
{Date: {Year: 2016, Month: 4}, Rating: []}
{Date: {Year: 2016, Month: 3}, Rating: []}
{Date: {Year: 2016, Month: 2}, Rating: []}
{Date: {Year: 2016, Month: 1}, Rating: []}
{Date: {Year: 2015, Month: 12}, Rating: []}


However I'm trying to aggregate the Rating field into an Average. I've tried this:

return Json(new
{
Average = ratingsByMonth.Average(x=>x.Rating),
Dates = ratingsByMonth.Select(x => Date)
});


But I get the following error highlighted on the Average - line:


Cannot implicitly convert type
'System.Collections.Generic.IEnumerable' to 'long?'


The output that I am looking for is:

{Date: {Year: 2016, Month: 11}, Rating: 4}
{Date: {Year: 2016, Month: 10}, Rating: 3.5}
{Date: {Year: 2016, Month: 9}, Rating: 0}
{Date: {Year: 2016, Month: 8}, Rating: 0}
{Date: {Year: 2016, Month: 7}, Rating: 0}
{Date: {Year: 2016, Month: 6}, Rating: 0}
{Date: {Year: 2016, Month: 5}, Rating: 0}
{Date: {Year: 2016, Month: 4}, Rating: 0}
{Date: {Year: 2016, Month: 3}, Rating: 0}
{Date: {Year: 2016, Month: 2}, Rating: 0}
{Date: {Year: 2016, Month: 1}, Rating: 0}
{Date: {Year: 2015, Month: 12}, Rating: 0}


Edit:

Have tried changing to the following:

return Json(new
{
Average = ratingsByMonth.Select(x => x.Rating).SelectMany(x => x).Average(),
Dates = ratingsByMonth.Select(x => x.Date.Month)
});


However, this just gives me a single 'Average' value across the whole data set, rather than an average per date.

Edit:

Here is sample data:

RatingID Rating RatingDate
5 5 2016-10-09 20:11:29.590
11 0 2016-10-09 20:14:42.503
21 5 2016-10-09 20:24:15.667
60 4 2016-10-28 11:01:21.220
64 4 2016-11-03 16:30:47.657


So for November the average should be 4, and October should be 3.5 (5+0+5+4 / 4)

Answer

In the original query you probably want to do this.

//other code removed for brevity
select new {
    Date = key,
    Rating = g.Count() > 0 ? g.Average(x => x.Rating) : 0
};

And then return the result

return Json(ratingsByMonth);
Comments