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;

Answer 2

Try this:

aspnet_Users.Where(s => tblUserNetwork
                  .Where(t => s.UserId == t.ConnectedToUserID).Count() == 0)
            .Select(s => s);

EDIT: Plus this I suppose:

aspnet_Users.Where(s => tblUserNetwork
                  .Where(t => s.UserId == t.ConnectedToUserID).Count() == 0
                     && s.UserId != '82522f05-2650-466a-a430-72e6c9fb68b7')
            .Select(s => s);

Answer 3

Here's a different approach using Join and Except operators:

var connectedUsers = aspnetUsers
    .Join(networkUsers, a => a.UserId, n => n.UserId, (a, n) => a);

var exceptions = aspnetUsers
    .Where(a => a.UserId == "82522f05-2650-466a-a430-72e6c9fb68b7")
    .Union(connectedUsers);

var query = aspnetUsers.Except(exceptions);