我的問題是像 SQL 查詢一樣使用另外 4 個不同的列型別布林值提取不同的 PartId。圖 1 顯示了我想要的輸出。問題是如果我們假設我們使用 dBcontext 作為默認值，我如何使用 LINQ 和 EF Core 解決同樣的問題。更重要的是假設物件 ApprovalNumberRecord 是 DbSet 并且導航到 Part 和 Approval 物件。
SELECT
B.PartId
,Sum(Case when B.ApprovalName = 'R-67' then 1 Else 0 End) as [R-67]
,Sum(Case when B.ApprovalName = 'R115' then 1 Else 0 End) as [R115]
,Sum(Case when B.ApprovalName = 'LPG-D' then 1 Else 0 End) as [LPG-D]
,Sum(Case when B.ApprovalName = 'CNG-D' then 1 Else 0 End) as [CNG-D]
FROM
(SELECT
anr.PartId
,a.ApprovalName
,anr.IsVisible
,p.[Description]
FROM
ApprovalNumberRecord AS anr
LEFT JOIN
Parts AS p ON anr.PartId = p.Id
LEFT JOIN
Approvals AS a ON anr.ApprovalId = a.Id) AS B
GROUP BY
B.PartId
SQL陳述句的輸出
這是內部 SELECT 輸出：
內部 SQL 陳述句的 OUTPUT
在資料庫模型下面：
ApprovalNumberRecord 類：
... //other fields and properties
public Part Part { get; set; }
public Approval Approval { get; set; }
零件類：
public Producer Producer{ get; set; }
public Category Category { get; set; }
public ICollection<ApprovalNumberRecord> ApprovalNumberRecords { get; set; }
審批等級：
none navigation objects inside
uj5u.com熱心網友回復：
var innerQuery =
from anr in dbContext.ApprovalNumberRecords
let p = anr.Part
let a = anr.Approval
select new { anr.PartId, a?.ApprovalName, anr.IsVisible, p?.Description };
var groupQuery =
from B in innerQuery
group B by B.PartId into g
select new {
PartId = g.Key,
R_67 = g.Sum(x => x.ApprovalName == 'R-67' ? 1 : 0),
R115 = g.Sum(x => x.ApprovalName == 'R111' ? 1 : 0),
LPG_D = g.Sum(x => x.ApprovalName == 'LPG-D' ? 1 : 0),
CNG_D = g.Sum(x => x.ApprovalName == 'CNG-D' ? 1 : 0)
};
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/353799.html
上一篇：將SQL查詢轉換為等效的LINQ