如何将带用于层级子节点检查的OUTER APPLY的SQL转换为LINQ

转换后的LINQ代码 #

对应SQL中的OUTER APPLY可通过LINQ的多from子句加DefaultIfEmpty()实现，以下是两种常用写法：

写法1：与原SQL结构完全匹配（无需提前查询父节点）

// 假设数据上下文集合名为 ProductHierarchies
var result = await (from parent in dbContext.ProductHierarchies
            where parent.Id == 6
            from phNode in dbContext.ProductHierarchies
            where phNode.Left >= parent.Left 
               && phNode.Right <= parent.Right
               && phNode.LevelTypeId == 4
            // 对应OUTER APPLY逻辑
            from c in dbContext.ProductHierarchies
                .Where(ph2 => ph2.Left > parent.Left && ph2.Right < parent.Right)
                .Select(_ => 1)
                .Take(1)
                .DefaultIfEmpty()
            orderby phNode.Left
            select new 
            {
                phNode.Id,
                phNode.Description,
                phNode.CreatedAt,
                phNode.Left,
                phNode.Right,
                phNode.LastUpdated,
                HasChildren = c ?? 0,
                phNode.LevelTypeId
            }).ToListAsync();

写法2：提前查询父节点（逻辑更直观，适合固定父节点ID的场景）

// 先获取ID为6的父节点
var parent = await dbContext.ProductHierarchies.FirstAsync(p => p.Id == 6);

var result = await (from phNode in dbContext.ProductHierarchies
            where phNode.Left >= parent.Left 
               && phNode.Right <= parent.Right
               && phNode.LevelTypeId == 4
            from c in dbContext.ProductHierarchies
                .Where(ph2 => ph2.Left > parent.Left && ph2.Right < parent.Right)
                .Select(_ => 1)
                .Take(1)
                .DefaultIfEmpty()
            orderby phNode.Left
            select new 
            {
                phNode.Id,
                phNode.Description,
                phNode.CreatedAt,
                phNode.Left,
                phNode.Right,
                phNode.LastUpdated,
                HasChildren = c ?? 0,
                phNode.LevelTypeId
            }).ToListAsync();

关键逻辑说明 #

• OUTER APPLY对应实现：LINQ中第二个from子句搭配DefaultIfEmpty()即可实现左关联子查询，Take(1)对应SQL的TOP 1
• coalesce(c.HasChildren, 0)直接用C#的null合并运算符??实现，子查询无匹配结果时自动返回0
• 原SQL的BETWEEN语法拆解为两个比较表达式即可，EF/EF Core可自动识别转换为原生SQL的BETWEEN逻辑
• 上述代码在EF Core 3.x及以上版本均可被正确翻译为包含OUTER APPLY的原生SQL，不会触发客户端求值。

内容的提问来源于stack exchange，提问作者Gani Lastra