How can I get empty months while grouping records by last 12 month in EF Core 5

Question

I have code snippet below for grouping records for last 12 months and it works properly but I just noticed that empty months is not included. Where did I go wrong? Thanks in advance

public IQueryable<DashboardGrouping> DashboardStats()
{
   var yearAgo = DateTime.Now.AddYears(-1);
   var date = new DateTime(yearAgo.Year, yearAgo.Month, 1);

   var items = context.Set<Transaction>()
   .Where(x => x.IsActive &&
      x.CreatedAt.HasValue && x.CreatedAt.Value.Date >= date.Date && x.PaymentStatusId == 
      PaymentStatus.Completed)
   .Include(x => x.Payment)
   .Include(x => x.Branch)
   .AsNoTracking()
   .Select(x => new
   {
      Year = x.CreatedAt.Value.Year,
      Month = x.CreatedAt.Value.Month,
      CashAmount = x.Payment.CashAmount,
      CardAmount = x.Payment.CardAmount,
   })
   .GroupBy(x => new
   {
      Year = x.Year,
      Month = x.Month,
   })
   .Select(x => new DashboardGrouping
   {
      Year = x.Key.Year,
      Month = x.Key.Month,
      TotalSale = x.Sum(s => s.CashAmount + s.CardAmount)
   });
   return items; 
}

Answer 1

You can do client-side postprocessing and enrich result with missing records.

Helper function for generating months:

static IEnumerable<DateTime> GetMonths(DateTime startDate, DateTime endDate)
{
    startDate = new DateTime(startDate.Year, startDate.Month, 1);
    endDate = new DateTime(endDate.Year, endDate.Month, 1);

    while (startDate < endDate)
    {
        yield return startDate;
        startDate = startDate.AddMonths(1);
    }
}

Postprocessing:

var currentDate = DateTime.Now;
var yearAgo = currentDate.AddYears(-1);

var months = GetMonths(yearAgo, currentDate);

var stats = DashboardStats().ToList();

// left join months to actual data
var query =
    from m in months
    join s in stats on new { m.Year, m.Month } equals new { s.Year, s.Month } into gj
    from s in gj.DefaultIfEmpty()
    select s ?? new DashboardGrouping
    {
        Year = m.Year,
        Month = m.Month,
        TotalSale = 0
    };

var result = query.ToList();