将T-SQL的OUTER APPLY语句转换为EF/LINQ实现求助

我一直尝试把一段包含OUTER APPLY的T-SQL代码转换成EF（Entity Framework）实现，但没成功，希望得到帮助。

补充说明：表结构如下，当使用MeetingId=1时，我期望返回ContinuedMeetingIds列表（示例应返回2,3）。

表结构：

MeetingId       ContinuedMeetingId
----------      -------------
1               2
2               3
3               null

原T-SQL代码：

SELECT
    Prev2.ContinuedMeetingId AS PreviousValue
FROM
    [Meeting] This
OUTER APPLY
    (SELECT
         prev.ContinuedMeetingId
     FROM
         [Meeting] prev
     WHERE
         Prev.ContinuedMeetingId < This.MeetingId) Prev2
WHERE
    This.MeetingId = 2710

我编写的EF/LINQ代码：

var query1 = (from m in db.Meetings
              where m.MeetingId == MeetingId
              select new
                     {
                         m,
                         SubName = db.Meetings
                                     .Where(s => m.MeetingId == s.MeetingId)
                                     .Select(s => s.ContinuedMeetingId)
                     }).ToList();

问题分析与解决方案 #

一、实现原T-SQL逻辑的EF转换

原T-SQL的逻辑是：找到MeetingId=2710的记录，关联所有ContinuedMeetingId小于该MeetingId的Meeting记录，取出它们的ContinuedMeetingId。你当前的LINQ代码逻辑错误，子查询条件是匹配MeetingId，和原SQL的关联逻辑不符。

正确的EF/LINQ写法（模拟OUTER APPLY）：

var targetMeetingId = 2710;
// 查询语法
var query = from thisMeeting in db.Meetings
            where thisMeeting.MeetingId == targetMeetingId
            from prevMeeting in db.Meetings
                                 .Where(p => p.ContinuedMeetingId < thisMeeting.MeetingId)
                                 .DefaultIfEmpty() // 对应OUTER APPLY的外部连接，无匹配时返回null
            select prevMeeting.ContinuedMeetingId;

var result = query.ToList();

// 方法语法等价写法
var queryMethod = db.Meetings
                    .Where(tm => tm.MeetingId == targetMeetingId)
                    .SelectMany(tm => db.Meetings
                                        .Where(p => p.ContinuedMeetingId < tm.MeetingId)
                                        .DefaultIfEmpty(),
                                (tm, pm) => pm.ContinuedMeetingId);

var resultMethod = queryMethod.ToList();

二、实现你示例需求的EF转换

你补充的示例（MeetingId=1返回2,3）是获取会议的后续链（1→2→3），这需要递归查询，原T-SQL无法实现这个需求，以下是对应的EF实现：

1. 使用原生递归SQL（适用于所有EF版本）

var targetMeetingId = 1;
var result = db.Meetings
               .FromSqlRaw(@"
                   WITH RecursiveMeetings AS (
                       SELECT MeetingId, ContinuedMeetingId
                       FROM Meeting
                       WHERE MeetingId = {0}
                       UNION ALL
                       SELECT m.MeetingId, m.ContinuedMeetingId
                       FROM Meeting m
                       JOIN RecursiveMeetings rm ON m.MeetingId = rm.ContinuedMeetingId
                   )
                   SELECT ContinuedMeetingId
                   FROM RecursiveMeetings
                   WHERE ContinuedMeetingId IS NOT NULL", targetMeetingId)
               .Select(m => m.ContinuedMeetingId)
               .ToList();

2. 使用纯LINQ递归（EF Core 6+支持）

var targetMeetingId = 1;
Func<int?, IQueryable<int>> getContinuedChain = null;
getContinuedChain = id => 
    db.Meetings
      .Where(m => m.MeetingId == id)
      .SelectMany(m => m.ContinuedMeetingId != null 
          ? getContinuedChain(m.ContinuedMeetingId).Prepend(m.ContinuedMeetingId.Value) 
          : Enumerable.Empty<int>());

var result = getContinuedChain(targetMeetingId).ToList();

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