SQL Subquery in LINQ for Entity Framework 4.0

Question

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 :-).

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)  

Anybody able to help?? It seems like a fairly simple subquery in SQL, but in LINQ, I'm baffled.

Thanks,

Jorin

Answer 1

Something like this I guess:

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

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