如何将带有子查询的SQL语句转换为等效的LINQ查询

对应SQL逻辑的LINQ查询实现（兼容EF6/EF Core） #

以下两种写法生成的SQL和你提供的原始SQL逻辑完全对齐：

查询语法版本（更接近SQL写法，可读性更高）

// CCDailyPositions分组子查询：按masterline_id分组取每组最大ID
var dpSubQuery = from dp in dbContext.CCDailyPositions
                 group dp by dp.masterline_id into g
                 select new 
                 {
                     ID = g.Max(x => x.ID),
                     masterline_id = g.Key
                 };

// CCPurchase分组子查询：按masterline_id分组取每组最大ID
var prSubQuery = from pr in dbContext.CCPurchase
                 group pr by pr.masterline_id into g
                 select new 
                 {
                     ID = g.Max(x => x.ID),
                     masterline_id = g.Key
                 };

// 主查询：左连两个子查询后投影结果
var queryResult = from m in dbContext.CCMemberizationPII
                  join dp in dpSubQuery on m.application_id equals dp.masterline_id into dpLeftJoin
                  from dpItem in dpLeftJoin.DefaultIfEmpty()
                  join pr in prSubQuery on m.application_id equals pr.masterline_id into prLeftJoin
                  from prItem in prLeftJoin.DefaultIfEmpty()
                  orderby m.Id
                  select new 
                  {
                      M_Id = m.Id,
                      borrower_id = m.borrower_id,
                      application_id = m.application_id,
                      borrower_first_name = m.borrower_first_name,
                      borrower_last_name = m.borrower_last_name,
                      DP_ID = (int?)dpItem.ID,
                      DP_masterline_id = dpItem.masterline_id,
                      PR_ID = (int?)prItem.ID,
                      PR_masterline_id = prItem.masterline_id
                  };

// 执行查询获取结果
var resultList = queryResult.ToList();

方法链版本

var dpSubQuery = dbContext.CCDailyPositions
    .GroupBy(dp => dp.masterline_id)
    .Select(g => new { ID = g.Max(x => x.ID), masterline_id = g.Key });

var prSubQuery = dbContext.CCPurchase
    .GroupBy(pr => pr.masterline_id)
    .Select(g => new { ID = g.Max(x => x.ID), masterline_id = g.Key });

var resultList = dbContext.CCMemberizationPII
    .GroupJoin(dpSubQuery, m => m.application_id, dp => dp.masterline_id, (m, dpJoin) => new { m, dpJoin })
    .SelectMany(t => t.dpJoin.DefaultIfEmpty(), (t, dpItem) => new { t.m, dpItem })
    .GroupJoin(prSubQuery, t => t.m.application_id, pr => pr.masterline_id, (t, prJoin) => new { t.m, t.dpItem, prJoin })
    .SelectMany(t => t.prJoin.DefaultIfEmpty(), (t, prItem) => new { t.m, t.dpItem, prItem })
    .OrderBy(t => t.m.Id)
    .Select(t => new 
    {
        M_Id = t.m.Id,
        borrower_id = t.m.borrower_id,
        application_id = t.m.application_id,
        borrower_first_name = t.m.borrower_first_name,
        borrower_last_name = t.m.borrower_last_name,
        DP_ID = (int?)t.dpItem.ID,
        DP_masterline_id = t.dpItem.masterline_id,
        PR_ID = (int?)t.prItem.ID,
        PR_masterline_id = t.prItem.masterline_id
    })
    .ToList();

注意事项 #

• 代码中的dbContext为你项目中对应的数据上下文实例，需确保实体类字段与数据库字段的映射配置正确。
• 左联返回的dpItem、prItem可能为空，因此值类型字段（如ID）需显式声明为可空类型，避免运行时空引用异常，EF会自动对应SQL的NULL处理逻辑。
• 异步场景可将ToList()替换为ToListAsync()。

内容的提问来源于stack exchange，提问作者Sugar Bowl