c# - 使用 Lambda 或 LINQ 查询在给定日期范围内按年和月分组

Question

我有一个包含以下类型对象的列表。当前列表按升序排列。

public class ReportsCountByDate{
    public int  CountOfReports { get ; set ;}
    public DateTime ReportGeneratedDate { get ;set ;}  
}

我的数据集:

CountOfReports     ReportGeneratedDate
3                  2017-02-07
5                  2017-02-21
5                  2017-03-12
1                  2017-05-03
4                  2017-05-23
3                  2017-06-12

[请注意，2017 年 4 月没有 repot]如果我需要获取从 2017 年 1 月到 2017 年 7 月的报告计数。

CountOfReports  YearAndMonth 
0               2017-01 
8               2017-02
5               2017-03
0               2017-04
5               2017-05
3               2017-06
0               2017-07

谁能告诉我如何使用 Lambda 或 LINQ 查询获得以下结果。

更新:

这是我的代码。

var startYear = input.durationStartDate.Year;
var startMonth = input.durationStartDate.Month;

var endYear = input.durationEndDate.Year;
var endMonth = input.durationEndDate.Month;

var yearRange = (endYear - startYear) == 0 ? 1 : (endYear - startYear);

var monthRange = ((input.durationEndDate.Year - input.durationStartDate.Year) * 12 + input.durationEndDate.Month - input.durationStartDate.Month) == 0 ? 1 : ((input.durationEndDate.Year - input.durationStartDate.Year) * 12 + input.durationEndDate.Month - input.durationStartDate.Month);



var desposition = await _dispositionSummaryManager.GetDispositionFormByCreateDate(input.platform, input.durationStartDate, input.durationEndDate);

var changesPerYearAndMonth =
                   from year in Enumerable.Range(startYear, yearRange)
                   from month in Enumerable.Range(startMonth, monthRange)
                   let key = new { Year = year, Month = month }
                   join ReportsCountByDate in desposition on key
                             equals new
                             {
                                 ReportsCountByDate.ReportGeneratedDate.Year,
                                 ReportsCountByDate.ReportGeneratedDate.Month
                             } into g
                   select new { GroupCriteria = key, Count = g.Count() };

Answer 1

给定当前输入

var startDate = new DateTime(startYear, startMonth, 1);
var endDate = new DateTime(endYear, endMonth + 1, 1).AddMinutes(-1);

var months = new List<DateTime>();
var dt = startDate;
while (dt < endDate) {
    months.Add(dt);
    dt = dt.AddMonths(1);
}

var result = from month in months
             let key = new { month.Year, month.Month }
             join report in desposition
             on key equals new { report.ReportGeneratedDate.Year, report.ReportGeneratedDate.Month } into g
             select new {
                 YearAndMonth = string.Format("{0}-{1:D2}", key.Year, key.Month),
                 CountOfReports = g.Sum(n => n.CountOfReports)
             };