Transform sql query to linq with groupBy and months

Question

I have following query:

select concat(Left(DateName(month,[date]),3), ' ', Year([date])), 
    sum(TotalAttendants) as Total,
    Sum(FemaleAttendants) as Women,
    Sum(MaleAttendants) as Men
from dbo.Events
where IsDeleted=0 and EventTypeId = 1
group by concat(Left(DateName(month,[date]),3), ' ', Year([date]))

and I want to transform it to c# linq lambda expression.

I tried something like this:

var response = await _context.Events
                             .Where(x => !x.IsDeleted && x.EventTypeId == Domain.Enums.EventTypes.DirectBeneficiaries)
                             .GroupBy(x => x.Date)
                             .Select(x => new EventViewData
                                {
                                    MaleAttendants = x.Sum(u => u.MaleAttendants),
                                    FemaleAttendants = x.Sum(u => u.FemaleAttendants),
                                    TotalAttendants = x.Sum(u => u.TotalAttendants),
                                    MonthName = x.Key.ToString("00")
                                }).ToListAsync();

Im not getting same result as Im getting in my mssql management studio.

If you need more information about data structure and table Events here is the my another stackoverflow topic: link

Answer 1

I think you should group by month and year and do the formatting (concat, etc.) later (if needed at all).

select 
...
from dbo.Events
..
group by Month([date]), Year([date]))

Then in linq you can:

...
.GroupBy(x => new { Year = x.Date.Year, Month = x.Date.Month } )
.Select(x => new  // Note no type name
{
   MaleAttendants = x.Sum(u => u.MaleAttendants),
   FemaleAttendants = x.Sum(u => u.FemaleAttendants),
   TotalAttendants = x.Sum(u => u.TotalAttendants),
   Month = x.Key.Month,
   Year = x.Key.Year
})
.ToListAsync() // Hit the db
.Select( x => new EventViewData
{
   x.MaleAttendants
   x.FemaleAttendants
   x.TotalAttendants
   MonthName = System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetAbbreviatedMonthName(x.Month)
   ...
}

I don't think GetAbbreviatedMonthName is supported by EF so we need to do it after ToListAsync.