实体框架 - Linq NOT IN 查询

我见过其他几个帖子提出类似的问题，但坦率地说我很困惑。 我正在尝试在 EntityFarmework 和 Linq 中执行以下 sql 语句，但无法让“NOT IN”和“UNION”工作

SELECT LmsTeam.* FROM LmsTeam
INNER JOIN Game ON LmsTeam.GameId = Game.ID 
WHERE LmsTeam.Id NOT IN 
(SELECT TeamHomeId as TeamID FROM LmsEventMatch WHERE EventId =1
UNION
SELECT TeamAwayId as TeamID FROM LmsEventMatch WHERE EventId =1)
AND LmsTeam.GameId = 1 AND LmsTeam.Active = 1

所以我有join以及下面的一些 where 子句，但不能执行NOT IN and UNION条款。

from t in LmsTeams
join g in Games on t.GameId equals g.Id
  where t.GameId == 1 && t.Active == true
  select t

那个怎么样：

from t in LmsTeams
join g in Games on t.GameId equals g.Id
where t.GameId == 1 && t.Active == true && !(
        (from m in LmsEventMatch where m.EventId == 1 select m.TeamHomeId).Union(
         from m in LmsEventMatch where m.EventId == 1 select m.TeamAwayId)
    ).Contains(t.Id)
select t

我还没有测试它，因为没有你的数据上下文，但认为应该这样做。

Update

我认为你可以避免Union here:

from t in LmsTeams
join g in Games on t.GameId equals g.Id
where t.GameId == 1 && t.Active == true && !(
        LmsEventMatch.Where(m => m.EventId == 1).SelectMany(m => new int[] { m.TeamHomeId, TeamAwayId })
    ).Contains(t.Id)
select t