Clever Geek Handbook

Simple Linq Subquery Example

T-SQL query

Select * from dbo.User_Users
Where UserID IN (Select UserID from Course_Enrollments)

LINQ to Entities alternative to above query

var innerquery = from en in Course_Enrollments
select en.UserID;

var query = from u in User_Users
where innerquery.Contains(u.UserID)
select u;

There are many complex subqueries on stackoverflow, I just want to see a simple example of how a simple subquery is done via linq. This is how I did it, however its not very good because it is sending 2 queries to the database.

+3


source to share


2 answers


The simple answer is to use the "let" keyword and create a subquery that maintains your conditional set for the main object.

var usersEnrolledInCourses = from u in User_Users
                                 let ces = from ce in Course_Enrollments
                                           select ce.UserID
                                 where ces.Contains(u.UserID)
                             select u;

This will create an existing block in TSQL like



SELECT [Extent1].*
   FROM dbo.User_Users AS Extent1
   WHERE EXISTS (SELECT 1 AS [C1]
                     FROM dbo.Course_Enrollements AS Extent2
                     WHERE (Extent2.UserID = Extent1.UserId))

This is close to what you asked for and will usually generate the same query plan on SQL Server.

Hope this helps!

+6


source 


from u in User_Users
where u.Course_Enrollments.Any()
select u

if you have foreign keys set. If not, you can do this



from u in User_Users
join cu in Course_Enrollments on u.UserId equals cu.UserId
select u

You must also wrap any of them with a call .Distinct()

0


source






More articles:


All Articles