问题描述
我是LINQ和EF的新手，但是我能够偶然遇到大多数查询，但这使我完全困惑.不管我尝试什么，它在SQL Profiler中都会带来很大的麻烦:-).
I'm new to LINQ and EF, but I've been able to stumble through for the majority of the queries I have, but this one has me completely confused. No matter what I try, it comes up in SQL Profiler as a big mess :-).
我有两个表:Users和UsersProjects.该查询的目的是列出与指定用户一起在项目上工作的所有用户.这是我用SQL编写的查询.这是一个子查询，但是我不知道进一步简化它的方法，但是我也很乐意在那里提出建议.
I have two tables: Users and UsersProjects. The goal of this query is to list all the users who are working on projects with the specified user. Here is the query as I have it written in SQL. It's a subquery, but I don't know of a way to simplify it further, but I'm open to suggestions there as well.
SELECT DISTINCT Users.FirstName, Users.LastName
FROM Users INNER JOIN UsersProjects ON Users.ID=UsersProjects.UserID
WHERE UsersProjects.ProjectID IN
(SELECT ProjectID FROM UsersProjects WHERE UserID=@UserID)
任何人都可以提供帮助吗?在SQL中，这似乎是一个相当简单的子查询，但是在LINQ中，我感到困惑.
Anybody able to help?? It seems like a fairly simple subquery in SQL, but in LINQ, I'm baffled.
谢谢
Jorin
推荐答案
我猜是这样的:
from u in Users
from projectId in UsersProjects.Where(up => up.UserId == @userId).Select(p => p.ProjectId)
where u.UsersProjects.Any(up => projectId == up.ProjectId)
select u
或(这是您在linq中的SQL查询)
or (it's your sql query in linq)
(from u in Users
join up in UsersProjects on @userId equals up.UserId
where u.UsersProjects.Any(up2 => up2.ProjectId == up.ProjectId)
select u).Distinct()
这篇关于LINQ for Entity Framework 4.0中的SQL子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！