locked
LINQ implementation of SQL NOT IN RRS feed

  • Question

  • User-1367363180 posted

    Hello,

    I would like to implement SQL NOT IN statement in LINQ, based on next example:

    SELECT
       ID,
       Description,
    FROM
       TableA
    WHERE
       ID NOT IN (SELECT ID FROM TableB WHERE SomeColumnTableB = 1)

    Thanks for help, kind regards

    Sunday, April 20, 2014 4:59 PM

Answers

  • User281315223 posted

    If you wanted to see if a particular ID value didn't exist within a collection, you could negate the Enumerable.Any() method :

    // Grab your IDs from your other table
    var ids = TableB.Where(b => b.SomeColumnTableB == 1);
    
    // Now ensure that your ID property of TableA does not exist in that table
    var results = TableA.Where(a => !ids.Any(b => b.ID == a.ID));
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 21, 2014 8:08 AM

All replies

  • User-821857111 posted
    var ids = TableB.Select(x => x.SomeColumn.Equals(1));
    var result  = TableA.Select(y => !ids.Contains(y.ID));



    Monday, April 21, 2014 5:04 AM
  • User-1367363180 posted

    Thanks, but it seems that there's a problem, since ID is int type.

    var result  = TableA.Select(y => !ids.Contains(y.ID));

    Error 1 Instance argument: cannot convert from 'System.Linq.IQueryable<bool>' to 'System.Linq.ParallelQuery<int>' ...

    Monday, April 21, 2014 7:45 AM
  • User281315223 posted

    If you wanted to see if a particular ID value didn't exist within a collection, you could negate the Enumerable.Any() method :

    // Grab your IDs from your other table
    var ids = TableB.Where(b => b.SomeColumnTableB == 1);
    
    // Now ensure that your ID property of TableA does not exist in that table
    var results = TableA.Where(a => !ids.Any(b => b.ID == a.ID));
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 21, 2014 8:08 AM
  • User-821857111 posted

    Error 1 Instance argument: cannot convert from 'System.Linq.IQueryable<bool>' to 'System.Linq.ParallelQuery<int>' ..

    Happens when you don't test code before posting.... Rion has corrected it.

    Monday, April 21, 2014 8:50 AM
  • User-1367363180 posted

    Thanks a lot, works great!

    Case closed.

    Kind regards

    Monday, April 21, 2014 3:48 PM
  • User1208776063 posted

    stevana

    I would like to implement SQL NOT IN statement in LINQ, based on next example:

    EDIT:

    You should be able to do this in one query.

    using (DataContext db = new DataContext())
    {
        (from a in db.tablea
            where !(from b in db.tableb where b.somecolumn = 1 select b.ID).Contains(a.ID)
            select new
            {
                ID = a.ID,
                Description = a.Description
            }).ToList();
    }
    Tuesday, April 22, 2014 10:11 PM
  • User-821857111 posted

    You should be able to do this in one query.

    The previous replies result in one query being sent to the database - even though they are two lines of code.

    Wednesday, April 23, 2014 12:55 AM