使用子查询将SQL转换为LINQ

Question

我正在努力转换以下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 

Answer 1

在构建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会更好，取决于在你的特定情况下的执行计划。