LINQ 中的内部查询

Question

我有以下 SQL 查询：

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

与此 SQL 等效的 LINQ 是什么。

谢谢

Answer 1

这将是对 Northwind 数据库的类似查询，
它排除了来自伦敦且客户 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;

转置到您的查询中给出，

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

试试这个：

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

编辑：加上我想：

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

这是使用 Join 和 except 运算符的不同方法：

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);