loop or a recursive method cant get the data

I have this data in one table and I am trying to do a loop or a recursive method. I need to get the result base on using the meeting id. If I am using meetingid 1 it would return 2,3. If I do meetingid 2 i would get 1,3 and if I do 3 I would get 1,2. I have this method but I need to call in many time and I wanted to know if there is a better way. I believe I need linq to traverses the parent/prior meeting chain and one that traverses the child/continuation meeting chain.

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

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();

Solution

This can be done in SQL using two recursive CTEs - one that traverses the parent/prior meeting chain and one that traverses the child/continuation meeting chain. (The original meeting is excluded form both lists.) It is then just a matter of performing a UNION ALL and (if desired) a STRING_AGG() to build the final comma-separated list.

This logic can be wrapped up in a user-defined function which can then be added to the Entity Framework model and called from your application.

CREATE FUNCTION GetRelatedMeetings (@MeetingId INT)
RETURNS VARCHAR(MAX)
AS BEGIN
    DECLARE @RelatedMeetings VARCHAR(MAX);
  
    WITH PriorMeetings AS (
        SELECT M.MeetingId
        FROM Meetings M
        WHERE M.ContinuedMeetingId = @MeetingId
        UNION ALL
        SELECT M.MeetingId
        FROM PriorMeetings PM
        JOIN Meetings M
            ON M.ContinuedMeetingId = PM.MeetingId
    ),
    ContinuedMeetings AS (
        SELECT M.ContinuedMeetingId AS MeetingId
        FROM Meetings M
        WHERE M.MeetingId = @MeetingId
        UNION ALL
        SELECT M.ContinuedMeetingId AS MeetingId
        FROM ContinuedMeetings CM
        JOIN Meetings M
            ON M.MeetingId = CM.MeetingId
    ),
    RelatedMeetings AS (
        SELECT PM.MeetingId
        FROM PriorMeetings PM
        UNION ALL
        SELECT CM.MeetingId
        FROM ContinuedMeetings CM
    )
    SELECT @RelatedMeetings =
        STRING_AGG(RM.MeetingId, ',')
            WITHIN GROUP(ORDER BY RM.MeetingId)
    FROM RelatedMeetings RM;

    RETURN @RelatedMeetings
END

The above logic assumes that the meeting chain is linear with a 1-to-1 parent/child relationship. If the meeting relationships can be many-to-many, the graph traversal would be much more involved.

Although similar logic could be implemented in C#, I do not know of a way to write an equivalent recursive LINQ statement that would map to a single SQL query execution. A native LINQ implementation would likely loop and execute multiple queries. (I believe there are some add-ons that provide limited CTE support, but I do not know if they support recursion or would be adapted to this solution. That would require some more investigation.)

Results (with some extra test data):

MeetingId	RelatedMeetings
1	2,3
2	1,3
3	1,2
4	5,6,7
5	4,6,7
6	4,5,7
7	4,5,6
8	null

See this db<>fiddle for a demo.