Linq to SQL多列连接时如何使用OR条件关联第三张表？

Linq的join关键字仅支持基于等值的AND组合条件，无法直接在equals中写入OR逻辑。要实现t2.Team_Id匹配t3.Team_Id_Home或t3.Team_Id_Away的关联需求，推荐以下两种方案：

方案一：用From子句+Where条件替代Join（推荐） #

这种方式代码更简洁，数据库生成的SQL查询效率也更优：

List<Tuple<Match, Match_Players, Competition>> res = (from t1 in _context.Competitions
                                                      join t2 in _context.Match_Players
                                                      on new { t1.Club_Id, t1.Comp_Id } equals new { t2.Club_Id, t2.Comp_Id }
                                                      // 直接遍历Matches表，在Where中指定所有关联条件
                                                      from t3 in _context.Matches
                                                      where t2.Club_Id == t3.Club_Id 
                                                        && t2.Comp_Id == t3.Comp_Id 
                                                        && t2.Match_Id == t3.Match_Id
                                                        // 核心OR条件：匹配主场或客场球队ID
                                                        && (t2.Team_Id == t3.Team_Id_Home || t2.Team_Id == t3.Team_Id_Away)
                                                        && t1.Club_Id == club_id 
                                                        && t1.Comp_Inactive == false
                                                        && t2.UserId == player_id 
                                                        && t2.Player_Starter != 2
                                                      select new Tuple<Match, Match_Players, Competition>(t3, t2, t1)).ToList();

方案二：两个独立Join合并结果 #

如果需要对“主场关联”和“客场关联”的场景做差异化处理，可以分别查询后合并结果：

// 查询主场匹配的记录
var homeMatches = from t1 in _context.Competitions
                  join t2 in _context.Match_Players
                  on new { t1.Club_Id, t1.Comp_Id } equals new { t2.Club_Id, t2.Comp_Id }
                  join t3 in _context.Matches
                  on new { t2.Club_Id, t2.Comp_Id, t2.Team_Id, t2.Match_Id } equals new { t3.Club_Id, t3.Comp_Id, t3.Team_Id_Home, t3.Match_Id }
                  where t1.Club_Id == club_id 
                    && t1.Comp_Inactive == false
                    && t2.UserId == player_id 
                    && t2.Player_Starter != 2
                  select new Tuple<Match, Match_Players, Competition>(t3, t2, t1);

// 查询客场匹配的记录
var awayMatches = from t1 in _context.Competitions
                  join t2 in _context.Match_Players
                  on new { t1.Club_Id, t1.Comp_Id } equals new { t2.Club_Id, t2.Comp_Id }
                  join t3 in _context.Matches
                  on new { t2.Club_Id, t2.Comp_Id, t2.Team_Id, t2.Match_Id } equals new { t3.Club_Id, t3.Comp_Id, t3.Team_Id_Away, t3.Match_Id }
                  where t1.Club_Id == club_id 
                    && t1.Comp_Inactive == false
                    && t2.UserId == player_id 
                    && t2.Player_Starter != 2
                  select new Tuple<Match, Match_Players, Competition>(t3, t2, t1);

// 合并结果（Union自动去重，不需要去重可改用Concat）
List<Tuple<Match, Match_Players, Competition>> res = homeMatches.Union(awayMatches).ToList();

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