I have table ReportData having below records

how to convert into below table? please let me know the Linq statement. Thank you for your help in advance.

source class structure

    public class ReportingData
{
    public string AgencyName { get; set; }
    public int Year { get; set; }
    public string MonthNumnber { get; set; }
    public string Month { get; set; }
    public int MonthCount { get; set; }
}

CodePudding user response：

It seems to me that per [Agency, Month] combination, you want the MonthCounts of years 2017..2019.

I wonder what you want in a few years: only the last three years? or years 2017.. 2025? But that is a later problem.

My advice would be to make Groups of ReportData with same value of [Agency, Month] combination. Use the overload of Enumerable.GroupBy that has a parameter resultSelector to precisely define the result. If your data is in a different process (usually a database), use the IQueryable version.

IEnumerable<Report> reportData = ...

// make groups with same values for [Agency, Month] combination
var result = reportData.GroupBy(report => new
{
    Agency = report.Agency,
    Month = report.Month,
},

// parameter resultSelector: for every [Agency, Month] combination,
// and all reports that have this [Agency, Month] combination,
// make one new:
(agencyMonthCombination, reports] => new
{
    Agency = agencyMonthCombination.Agency,
    Month = agencyMonthCombination.Month,

    Year2017 = reports.Where(report => report.Year == 2017)
                      .Select(report => report.MonthCount)
                      .Sum();

    Year2018 = reports.Where(report => report.Year == 2018)
                      .Select(report => report.MonthCount)
                      .Sum();

    Year2019 = reports.Where(report => report.Year == 2018)
                      .Select(report => report.MonthCount)
                      .Sum();

If you expect only one report per year, you don't have to Sum, you can just use FirstOrDefault.

You see the disadvantage of this approach of naming the years 2017..2019. Code would be much easier if you made it for all years, or the last N years:

// parameter resultSelector
(agencyMonthCombination, reports] => new
{
    Agency = agencyMonthCombination.Agency,
    Month = agencyMonthCombination.Month,

    // group the reports with this [Agency, Month] combination in years
    MonthCounts = reports.GroupBy(report => report.Year,

        (year, reportsInThisYear) => new
        {
            Year = year,
            MonthCount = reportsInThisYear.Select(report => report.MonthCount).Sum(),
        })

        // if you only want the last N: orderby descending Year and Take(N)
        .OrderByDescending(yearMonthCount => yearMonthCount.Year)
        .Take(N)

        .ToList(),
    });