我有一張有價值的桌子。我想按日期月份分組，我的日期欄位名稱是 ApplicatonDate，以及一些列舉值。
這是我需要將其轉換為 Linq 的 SQL 查詢：
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)
這是我的 LINQ。那是我的錯誤 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();
我無法將 SQL 查詢轉換為 LINQ 查詢。我不知道如何用 Linq轉換和實作SUM / CASE部分。
提前致謝
uj5u.com熱心網友回復：
嘗試以下查詢：
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();
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/521695.html
標籤：C＃sql林克
上一篇：在生產者/消費者場景中緩沖IAsyncEnumerable
下一篇：嵌套的foreach重構