我正在努力將以下 SQL 轉換為 LINQ。任何人都可以幫助我嗎：
SELECT M.Id, M.borrower_id, M.application_id, [borrower_first_name] ,[borrower_last_name], DP.ID, DP.masterline_id, PR.ID, PR.masterline_id
FROM [dbo].[CCMemberizationPII] AS M
LEFT JOIN
(SELECT MAX(DP.ID) ID, DP.masterline_id
FROM [dbo].[CCDailyPositions] AS DP
GROUP BY DP.masterline_id) DP
ON DP.masterline_id = M.application_id
LEFT JOIN
(SELECT MAX(PR.ID) ID, PR.masterline_id
FROM [dbo].[CCPurchase] AS PR
GROUP BY PR.masterline_id) PR
ON PR.masterline_id = M.application_id
order by M.Id
uj5u.com熱心網友回復：
在構建 LINQ 查詢時，嘗試分解部分以構建最終查詢。
var positions =
from dp in ctx.CCDailyPositions
group by new { dp.masterline_id } into g
select new
{
ID = g.Max(x => x.ID),
dp.masterline_id
};
var purchases =
from pr in ctx.CCPurchase
group by new { pr.masterline_id } into g
select new
{
ID = g.Max(x => x.ID),
pr.masterline_id
};
var query =
from m in ctx.CCMemberizationPII
from dp in positions.Where(dp => dp.masterline_id == m.application_id)
.DefaultIfEmpty()
from pr in purchases.Where(pr => pr.masterline_id == m.application_id)
.DefaultIfEmpty()
orderby m.Id
select new
{
m.Id,
m.borrower_id,
m.application_id,
m.borrower_first_name,
m.borrower_last_name,
DP_ID = dp.ID,
PR_ID = pr.ID,
};
您的查詢也可以使用OUTER APPLY. 請注意，EF Core 可以使用視窗函式將 OUTER APPLY 轉換為 LEFT JOIN。
var query =
from m in ctx.CCMemberizationPII
from dp in ctx.CCDailyPositions.Where(dp => dp.masterline_id == m.application_id)
.OrderByDescending(dp => dp.ID)
.Take(1)
.DefaultIfEmpty()
from pr in ctx.CCPurchase.Where(pr => pr.masterline_id == m.application_id)
.OrderByDescending(pr => pr.ID)
.Take(1)
.DefaultIfEmpty()
orderby m.Id
select new
{
m.Id,
m.borrower_id,
m.application_id,
m.borrower_first_name,
m.borrower_last_name,
DP_ID = dp.ID,
PR_ID = pr.ID,
};
哪個 SQL 會更好，取決于您的特定情況下的執行計劃。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340921.html
上一篇：在C#中對字典串列進行分組