转换Sql与内部选择Linq

Question

如何将此Sql查询转换为LinQ

SELECT *
FROM
    users
    JOIN products
        ON users.id = products.user_id
WHERE users.id IN (
    SELECT id
    FROM users
    WHERE users.status = 'Online'
)

Answer 1

嗯，你的sql太复杂了(子查询是无用的)，所以简化它，你会得到一个简单的链接…

from u in users
join p in products on u.id equals p.user_id
where u.status == "Online"
select new{u, p};

你的SQL可以只是

select * 
from users u
join products p on u.id = p.user_id
where u.status = 'Online' 

Answer 2

根本不需要内部SELECT

SELECT *
FROM
    users
    JOIN products
        ON users.id = products.user_id
WHERE
    users.status = 'Online'

现在，将其转换为LINQ查询是小菜一碟。

注意:如果您正在查询另一个表，当连接时，将产生更多的输出行，因为它与查询中已经包含的一个表有1:n的关系，则需要使用嵌套SELECT的in子句。

Answer 3

我假设你有这些实体:

class User {
    public int Id { get; set; }
    public string Status { get; set; }
    public IList<Product> Products { get; set; }
}
class Product {
    public int Id { get; set; }
    public int UserId { get; set; }
    public User User { get; set; }
}

，从用户到产品是一对多关系。我们输入:

var result = 
    from user in users
    join product in products on user.Id equals product.UserId 
    where user.Status == "online"
    select new { user, product };