实体框架 - LINQ的NOT IN查询 [英] Entity Framework - Linq NOT IN query

问题描述

我见过的其他几个职位，询问类似的问题，但坦白说，我很困惑。 我试图做EntityFarmework和LINQ下面的SQL语句，但不能得到'NOT IN和联盟的工作

  SELECT LmsTeam。* FROM LmsTeam
INNER JOIN游戏LmsTeam.GameId = Game.ID
WHERE LmsTeam.Id NOT IN
（选择TeamHomeId为TeamID从LmsEventMatch WHERE事件ID = 1
联盟
选择TeamAwayId为TeamID从LmsEventMatch WHERE事件ID = 1）
和LmsTeam.GameId = 1 AND LmsTeam.Active = 1
 

所以，我已经得到了加入和一些如下的where子句，但不能做。在和 UNION 条款。

 从吨LmsTeams
加入克游戏t.GameId等于g.Id
  其中t.GameId == 1＆安培;＆安培; t.Active ==真
  选择T
 

解决方案

怎么样：

 从吨LmsTeams
加入克游戏t.GameId等于g.Id
其中t.GameId == 1＆安培;＆安培; t.Active ==真放;＆安培; ！（
        （从米LmsEventMatch那里m.EventId == 1选择m.TeamHomeId）.Union（
         从米LmsEventMatch那里m.EventId == 1选择m.TeamAwayId）
    ）。载（t.Id）
选择T
 

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

更新

我认为你能避免联盟这里：

 从吨LmsTeams
加入克游戏t.GameId等于g.Id
其中t.GameId == 1＆安培;＆安培; t.Active ==真放;＆安培; ！（
        LmsEventMatch.Where（M =＆GT; m.EventId == 1）.SelectMany（M =＆gt;新建INT [] {m​​.TeamHomeId，TeamAwayId}）
    ）。载（t.Id）
选择T
 

I've seen several other posts asking similar question but frankly I'm confused. I'm trying to do the following sql statement in EntityFarmework and Linq but cant get the 'NOT IN' and 'UNION' working

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

So I've got the join and some of the where clause as below but can't do the NOT IN and UNION clauses.

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

解决方案

How about that:

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

I haven't tested it because don't have your data context, but think it should be done that way.

Update

I think you can avoid 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

这篇关于实体框架 - LINQ的NOT IN查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！