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
I think you should group by month and year and do the formatting (concat, etc.) later (if needed at all).
Then in linq you can:
I don't think
GetAbbreviatedMonthName
is supported by EF so we need to do it afterToListAsync
.