CODEWITHSUNDEEP
c#linqlinq-to-sql
Refracted Paladin
Refracted Paladin

Reputation: 12216

Converting SQL to LINQ with INNER JOIN()?

I am struggling with how to write the below equivalent as LINQ. Truly I guess I am only struggling with how I represent the INNER JOIN () portion. Is that called a Nested Join? Anonymous Join? I am not even sure. Anyway, big thanks to anyone who can point me true. Even if it is just what this is called so I can BING it properly.

SELECT p.PersonID, p.FirstName, p.MiddleName, p.LastName, cp.EnrollmentID, cp.EnrollmentDate, cp.DisenrollmentDate
FROM vwPersonInfo AS p
    INNER JOIN (
    SELECT c.ClientID, c.EnrollmentID, c.EnrollmentDate, c.DisenrollmentDate
    FROM tblCMOEnrollment AS c
        LEFT OUTER JOIN tblWorkerHistory AS wh
        ON c.EnrollmentID = wh.EnrollmentID
            INNER JOIN tblStaffExtended AS se
            ON wh.Worker = se.StaffID
    WHERE (wh.EndDate IS NULL OR wh.EndDate >= getdate())
    AND wh.Worker = --WorkerID Param Here
) AS cp
ON p.PersonID = cp.ClientID
ORDER BY p.PersonID

Upvotes: 0

Views: 372

Answers (2)

Magnus
Magnus

Reputation: 46909

just put the inner query in its own variable. (It will be translated into one single SQL expression)

var innerQuery = from x in db.tblCMOEnrollment
                 where ...
                 select ...;

var query = from a in vwPersonInfo
            join b innerQuery on p.PersonID equals cp.ClientID
            select ...;

Upvotes: 4

CodeLikeBeaker
CodeLikeBeaker

Reputation: 21312

I think you can do this by writing a second method and joining on that method:

private static IEnumerable<Table> GetData(int joinKey)
    {
        return (from x in context.TableB.Where(id => id.Key == joinKey select x).AsQueryable();
    }

Then you can do your normal query:

var query = from c in context.TableA
join GetData(c.PrimaryKeyValue)

Upvotes: 0

Related Questions