LINQ implementation of SQL NOT IN

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

Answer 1

User-821857111 posted

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

Answer 2

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>' ...

Answer 3

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));

Answer 4

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.

Answer 5

User-1367363180 posted

Thanks a lot, works great!

Case closed.

Kind regards

Answer 6

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();
}

Answer 7

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.