SQL query to lambda expression or linq

Question

How can I convert the following query to a lambda expression

select * 
from Invoice_main 
where id not in 
    (select invoice_main_id from Invoice_payment_by_pay_method)

I could not find an alternative for 'not in'.

Answer 1

Assuming you are using LINQ-to-SQL:

from inv in Invoice_main
where !(from m in Invoice_payment_by_pay_method select m.invoice_main_id).Contains(inv.id)
select inv

The !(...).Contains(...) is automatically converted by LINQ-to-SQL to a NOT EXISTS clause (note: this is more efficient than the NOT IN clause).

Other providers (i.e. not LINQ-to-SQL) may not support this rewrite of .Contains into EXISTS so this may not work for everything.