I have a table that has some value. I want to group by date month, My date field name is ApplicatonDate, and some enum values.

Here is a SQL query which I need to convert it into Linq:

SELECT 
    Count(he.ApplicationDate) AS Total,
    MAX(he.ApplicationDate) AS ApplicationDate,
    SUM(CASE WHEN he.Status=0 then 1 else 0 end )AS Last12MonthTotalPending,
    SUM(CASE WHEN he.Status = 1 THEN 1 ELSE 0 END )AS Last12MonthTotalApproved,
    SUM(CASE WHEN he.Status = 2 THEN 1 ELSE 0 END )AS Last12MonthTotalDenied
FROM dbo.Client_Heatings he
WHERE he.ApplicationDate >= DATEADD(Year,-1,GETDATE()) AND he.ApplicationDate <= GETDATE()
GROUP BY CAST(ApplicationDate AS DATE)

This is my LINQ. That is my WRONG LINQ.

var result = heatingList
                  .Where(r => r.Id == 123)
                  .GroupBy(r =>r.ApplicationDate )
                  .Select(grp => new HeatingApplicationSummaryDTO
                  {
                      Last12MonthTotalPending = grp.Sum( t => t.Status == 0 ? 1 : 0),
                      Last12MonthTotalApproved = grp.Sum( t => t.Status == 1 ? 1 : 0),
                      Last12MonthTotalDenied = grp.Sum( t => t.Status == 2 ? 1 : 0),
                      Total = grp.Sum(c => c.Status)
                  }).ToList();

I can't convert SQL query to LINQ query. I do not know how to convert and implement SUM/CASE section with Linq.

Thanks in advance

CodePudding user response：

Try the following query:

var endDate = DateTime.Now;
var startDate = endDate.AddYears(-1);

var result = heatingList
    .Where(r => r.ApplicationDate >= startDate && r.ApplicationDate <= endtDate)
    .GroupBy(r => r.ApplicationDate.Date)
    .Select(grp => new HeatingApplicationSummaryDTO
    {
        Total = grp.Count(),
        ApplicationDate = grp.Max(t => t.ApplicationDate),
        Last12MonthTotalPending = grp.Sum(t => t.Status == 0 ? 1 : 0),
        Last12MonthTotalApproved = grp.Sum(t => t.Status == 1 ? 1 : 0),
        Last12MonthTotalDenied = grp.Sum(t => t.Status == 2 ? 1 : 0),
    }).ToList();