c# - Simple Example Subquery Linq

Question

T-SQL Query

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

LINQ to Entities alternative of 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 alot of 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 done it, however its not good because it sends 2 queries to the database.

Answer 1

Simple answer is use the "let" keyword and generate a sub-query that supports your conditional set for the main entity.

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 exists block in TSQL similar to

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

It's close to what you've asked for and will generally create the same query plan on SQL Server.

Hope this helps!