Not In function in LINQ

Question

my SQL query is

select id,name from 
table1 tbl
where
tbl.id not in (select id from table2 where institutionid = 12011);

I need a linq query for this. I tried several queries. but cannot get the result.

possible duplicate of [LINQ to SQL in and not in](http://stackoverflow.com/questions/3047657/linq-to-sql-in-and-not-in) — Rahul Singh, Dec 22 '14 at 07:30

score 1 · Answer 1 · answered Dec 22 '14 at 08:07

1

I prefer use Contains:

var q = from tb1 in table1
        where !table2.where(y => y.institutionid == 12011)
                     .select(z => z.id).Contains(tb1.id)
        select new { tb1.id, tb1.name };

answered Dec 22 '14 at 08:07

Tim.Tang

3,158
1
15
18

score 0 · Answer 2 · answered Jan 04 '15 at 16:51

Use join/outer join, then test if join is null. The only thing odd is the DefaultIfEmpty().Take(1) "technique" to get you an outer join to one record or nothing. Otherwise, it's pretty clean syntax.

from tb1 in table1
join tb2 in table2 on new {tb1.id,institutionid = 12011} equals new { tb2.id, institutionid} into tb2s
from test in tb2s.DefaultIfEmpty().Take(1)
where test==null
select new { id,name}

Not In function in LINQ

2 Answers2