3
\$\begingroup\$

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

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.