Entity Framework - Linq NOT IN query

Question

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

Answer 1

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

Answer 2

Another solution is to use Left outer join and keep the records where joined columns are null.

An example is given below:

var query = db.Categories    
  .GroupJoin(db.Products,
      Category => Category.CategoryId,
      Product => Product.CategoryId,
      (x, y) => new { Category = x, Products = y })
  .SelectMany(
      xy => xy.Products.DefaultIfEmpty(),
      (x, y) => new { Category = x.Category, Product = y })
  .Where(w => w.Product.CategoryId == null)
  .Select(s => new { Category = s.Category});