Inner queries in LINQ

Question

I have the folowing SQL query:

Select * 
from aspnet_Users 
where UserId Not in 
(select UN.ConnectedToUserID
from    tblUserNetwork UN ) 
    and UserId <> '82522f05-2650-466a-a430-72e6c9fb68b7'

What will be the LINQ equivalent to this SQL.

Thanks

Answer 1

This would be a similar query for Northwind database, it excludes customers from london and with customer id ALFKI

var query =
from c in Customers
where c.CustomerID != "ALFKI" &&
!(from cc in Customers
        where cc.City == "London"
        select cc.CustomerID)
       .Contains(c.CustomerID)
select c;

Transposing onto your query gives,

var query =
from c in aspnet_Users
where c.UserId != "82522f05-2650-466a-a430-72e6c9fb68b7" &&
!(from cc in tblUserNetwork
        select cc.ConnectedToUserID)
       .Contains(c.UserId)
select c;