Entity Framework - Linq NOT IN query

.net, entity-framework, linq

Solution

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

Problem

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 ```

Original source