LINQ for Entity Framework 4.0中的SQL子查询 [英] SQL Subquery in LINQ for Entity Framework 4.0

问题描述

我是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子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！