SQL query to lambda expression or linq

Question

How to 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 alternatives 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

<...> Contains (...) automatically converts LINQ-to-SQL into a statement NOT EXISTS(note: this is more efficient than a statement NOT IN).

Other providers (i.e. not LINQ-to-SQL) may not support this entry .Containsin EXISTS, so this may not work for everything.