Geek asks and answers

Entity Framework - Linq NOT IN Request

I saw several other posts asking a similar question, but frankly, I'm confused. I am trying to execute the following sql statement in EntityFarmework and Linq, but cannot make "NOT IN" and "UNION" work

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 have joinsome where clauses, as shown below, but they cannot execute NOT INand clauses UNION.

from t in LmsTeams
join g in Games on t.GameId equals g.Id
  where t.GameId == 1 && t.Active == true
  select t
+5
source share
4 answers

How about this:

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 have not tested it because I do not have a data context, but I think it should be done that way.

Update

I think you can avoid Unionhere:

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
+3
source

Left external join , .

:

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});
+1

, , .

Also check my post: SQL to LINQ (Case 7 - Filter the data using the IN and NOT IN clauses)

//first do the union of two
var query = ((from d in  LmsEventMatch 
             where d.EventId == 1
              select d.TeamHomeId).
        Union(from e in  LmsEventMatch 
                       where e.EventId == 1
                          select e.TeamAwayId));

//than perform !contains operation for no in
var records =( from t in LmsTeams
join g in Games on t.GameId equals g.Id
  where t.GameId == 1 && t.Active == true && !query.Contains(t.Id)
  select t).ToList();

graphical representation of an in request, not in-linq

enter image description here

to convert from sql to linq you can use: Linqer enter image description here

0
source 
var homeIds = LmsEventMatch.Where(em => em.Eventid == 1)
                           .Select(em => em.TeamHomeId);
var awayIds = LmsEventMatch.Where(em => em.Eventid == 1)
                           .Select(em => em.TeamAwayId);
var allIds = homeIds.Union(awayIds);

var query = LmsTeams.Where( 
                t => !allIds.Contains( t.Id ) && t.Active == 1 && t.GameId == 1);
0
source

More articles:


All Articles