.net - Entity Framework - Linq NOT IN 查询

Question

我已经看到其他几个帖子提出了类似的问题，但坦率地说，我很困惑。
我正在尝试在 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和 UNION条款。

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

Answer 1

那个怎么样:

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

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

更新

我认为你可以避免 Union这里:

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