如何用EF/Linq高效获取关联会议ID？替代循环/递归方案咨询

问题背景 #

我在表中存储了会议关联数据如下：

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

需求是根据指定会议ID获取所有关联的会议ID：

• 传入1返回[2, 3]
• 传入2返回[1, 3]
• 传入3返回[1, 2]

目前通过循环/递归实现，但需要多次调用数据库，希望找到更高效的方案。当前使用的EF/Linq代码如下：

var y = (from m in Meetings
          where m.MeetingId == MeetingId
          select new
          {
              ContinuedMeetingsId = db.Meetings
                 .Where(s => s.MeetingId == m.MeetingId)
                 .Select(s => s.ContinuedMeetingId).FirstOrDefault()
          }).FirstOrDefault();

优化方案 #

方案一：内存批量处理（适合数据量小的场景） #

一次性将所有会议关联数据加载到内存，然后在内存中遍历完整的前置/续会链，仅需1次数据库查询。

// 提前加载全量会议数据到内存（可复用）
var allMeetings = db.Meetings.ToList();

public List<int> GetAllRelatedMeetingIds(int targetId)
{
    var relatedIds = new HashSet<int>();

    // 遍历续会链（向下）
    int current = targetId;
    while (true)
    {
        var nextMeeting = allMeetings.FirstOrDefault(m => m.MeetingId == current);
        if (nextMeeting?.ContinuedMeetingId == null) break;
        relatedIds.Add(nextMeeting.ContinuedMeetingId.Value);
        current = nextMeeting.ContinuedMeetingId.Value;
    }

    // 遍历前置会议链（向上）
    current = targetId;
    while (true)
    {
        var prevMeeting = allMeetings.FirstOrDefault(m => m.ContinuedMeetingId == current);
        if (prevMeeting == null) break;
        relatedIds.Add(prevMeeting.MeetingId);
        current = prevMeeting.MeetingId;
    }

    return relatedIds.ToList();
}

方案二：数据库递归CTE查询（适合数据量大的场景） #

利用EF Core 5+支持的递归SQL查询，直接在数据库层面获取完整关联链，避免内存加载全量数据。

public async Task<List<int>> GetAllRelatedMeetingIdsAsync(int targetId)
{
    // 查询续会链
    var continuedIds = await db.Meetings
        .FromSqlRaw(@"
            WITH RECURSIVE ContinuedChain AS (
                SELECT ContinuedMeetingId FROM Meetings WHERE MeetingId = {0}
                UNION ALL
                SELECT m.ContinuedMeetingId FROM Meetings m
                INNER JOIN ContinuedChain cc ON m.MeetingId = cc.ContinuedMeetingId
                WHERE m.ContinuedMeetingId IS NOT NULL
            )
            SELECT ContinuedMeetingId FROM ContinuedChain
        ", targetId)
        .Where(id => id != null)
        .Select(id => id.Value)
        .ToListAsync();

    // 查询前置会议链
    var previousIds = await db.Meetings
        .FromSqlRaw(@"
            WITH RECURSIVE PreviousChain AS (
                SELECT MeetingId FROM Meetings WHERE ContinuedMeetingId = {0}
                UNION ALL
                SELECT m.MeetingId FROM Meetings m
                INNER JOIN PreviousChain pc ON m.ContinuedMeetingId = pc.MeetingId
            )
            SELECT MeetingId FROM PreviousChain
        ", targetId)
        .ToListAsync();

    // 合并去重后返回
    return continuedIds.Concat(previousIds).Distinct().ToList();
}

方案选择 #

• 若会议数据量较小（比如数百条以内），优先选方案一，代码简洁且内存处理速度快。
• 若会议数据量较大，选方案二，利用数据库的递归查询能力，减少内存占用和不必要的数据传输。

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