Chartjs-How to group and count missing values using linq

by

1👍

✅

You can create a helper function to generate the month+year enumeration you need:

public static IEnumerable<(int Year,int Month)> MonthsInYears(int fromYear, int fromMonth, int toYear, int toMonth) {
    for (int year = fromYear; year <= toYear; ++year)
        for (int month = (year == fromYear ? fromMonth : 1); month <= (year == toYear ? toMonth : 12); ++month)
            yield return (year, month);
}

Then using this, you can create an enumeration of the period:

var veterans = _db.Records
                  .Where(j => j.Requestor == "Veterans" && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) >= 0 && EF.Functions.DateDiffMonth(j.Request_Date, DateTime.Now) <= 24)
                  .GroupBy(g => new { g.Request_Date.Value.Year, g.Request_Date.Value.Month }).OrderBy(d => d.Key.Year).ThenBy(d => d.Key.Month)
                  .Select(group => new {
                      YearMonth = group.Key,
                      Count = group.Count()
                  });

var minYearMonth = veterans.Select(v => v.YearMonth).First();
var maxYearMonth = veterans.Select(v => v.YearMonth).Last();
var monthsInYears = MonthsInYears(minYearMonth.Year, minYearMonth.Month, maxYearMonth.Year, maxYearMonth.Month);

Then you can GroupJoin (as a left join) to your database data:

var veteransCount = monthsInYears.GroupJoin(
                                    veterans,
                                    ym => new { ym.Year, ym.Month },
                                    v => v.YearMonth,
                                    (ym, sj) => sj.FirstOrDefault()?.Count ?? 0)
                                 .ToArray();

Alternatively, since this is a specific case, you could create a Dictionary for your source data and lookup each enumeration value:

var veteransMap = veterans.ToDictionary(v => v.YearMonth, v => v.Count);
var veteransCount2 = monthsInYears.Select(ym => veteransMap.TryGetValue(new { ym.Year, ym.Month }, out var count) ? count : 0)
                                  .ToArray();

NOTE: If you want the full beginning and ending years, you could just call the MonthsInYears method with 1 and 12 for the from and to months.

Leave a comment