LINQ statements that aggregate sums from a List of objects

Question

I'm packaging data for use in a JavaScript charting library that expects a somewhat odd structure of data in which there is an array of labels and an array of dataItems where each data item has a label and an array of data that corresponds to the array of labels. I'm creating the dataItems from a list of objects in which each one has a year, week, location, program, and hours. I'm creating the labels by getting distinct year/week combinations, and then aggregating the data to get distinct program/location items each with an array of the sums of the hours when grouped by year/week.

I've got these two LINQ statements that I'm using to create just the data items that I believe could be one, but I can't figure out how it would be possible. I'm summing the hours for each Program and Location for each Year and Week, and then I need an array of the sums for each Location and Program.

var dataRows = from h in hours
 orderby h.Year, h.WeekOfYear, h.LocationName, h.ProgramName
 group h by new { h.Year, h.WeekOfYear, h.LocationName, h.ProgramName } into programLocationGroups
 select new { Location = programLocationGroups.Key.LocationName, Program = programLocationGroups.Key.ProgramName, Hours = programLocationGroups.Sum(p => p.Hours) };
chartData.DataRows = (from d in dataRows
 group d by new { d.Program, d.Location } into programLocationSums
 select new FindAllAnalyticsResponse.DataRow
 {
 Location = programLocationSums.Key.Location,
 Program = programLocationSums.Key.Program,
 Data = programLocationSums.Select(p => p.Hours).ToList()
 }).ToList();

I'd love to see a more concise solution if it exists. There's also room for improvement due to the fact that missing data will result in mismatched arrays (not possible in the current environment, but something I'm aware of).

Answer 1

To combine the two queries into one, you can use select ... into:

chartData.DataRows = (from h in hours
 orderby h.Year, h.WeekOfYear, h.LocationName, h.ProgramName
 group h by new { h.Year, h.WeekOfYear, h.LocationName, h.ProgramName }
 into programLocationGroups
 select new
 {
 Location = programLocationGroups.Key.LocationName,
 Program = programLocationGroups.Key.ProgramName,
 Hours = programLocationGroups.Sum(p => p.Hours)
 }
 into d
 group d by new { d.Program, d.Location } into programLocationSums
 select new FindAllAnalyticsResponse.DataRow
 {
 Location = programLocationSums.Key.Location,
 Program = programLocationSums.Key.Program,
 Data = programLocationSums.Select(p => p.Hours).ToList()
 }).ToList();

Though I'm not sure this is actually an improvement.

I think an equivalent and simpler query would be to first group only by location and program and then by week in a nested query:

chartData.DataRows = (from h in hours
 orderby h.LocationName, h.ProgramName
 group h by new { h.LocationName, h.ProgramName } into programLocationGroup
 select new FindAllAnalyticsResponse.DataRow
 {
 Location = programLocationGroup.Key.LocationName,
 Program = programLocationGroup.Key.ProgramName,
 Data = (from pl in programLocationGroup
 orderby pl.Year, pl.WeekOfYear
 group pl by new { pl.Year, pl.WeekOfYear } into weekGroup
 select weekGroup.Sum(w => w.Hours)).ToList()
 }).ToList();

Note that the above code may need some better in the variables I introduced.