Help converting multi DB SQL Query to LINQ

Question

I have the below SQL Query, which returns what I expect, and I would LIKE to accomplish the same thing in LINQ but so far my results have been less then spectacular. The major hurdle, as I see it, is that the data is coming from 3 separate DB's. I was able to accomplish this in LINQ but it is extremely slow, see here.

So, with out further ado, here it is, with the hardcoded Guid() being the only exception as that gets passed in:

SELECT en.ClientID, p.LastName, p.FirstName, NurseName = dm2.FirstName + ' ' + dm2.LastName, SocialWorkerName = dm.FirstName + ' ' + dm.LastName, en.EnrollmentDate, en.DisenrollmentDate, ESWorkerName = sw.FirstName + ' ' + sw.LastName, sw.Phone

FROM CMO.dbo.tblCMOEnrollment en
    LEFT OUTER JOIN CMO.dbo.tblSupportWorker sw
        ON en.EconomicSupportWorkerID = sw.SupportWorkerID
    INNER JOIN Connect.dbo.tblPerson p
        ON en.ClientID = p.PersonID
    LEFT OUTER JOIN aspnetdb.dbo.tblDemographics dm
        ON en.CMOSocialWorkerID = dm.UserID
    LEFT OUTER JOIN aspnetdb.dbo.tblDemographics dm2
        ON en.CMONurseID = dm2.UserID

WHERE (en.CMOSocialWorkerID = '060632EE-BE09-4057-B17B-2D0190D0FF74'
        OR
        en.CMONurseID = '060632EE-BE09-4057-B17B-2D0190D0FF74')
AND (en.DisenrollmentDate IS NULL 
    OR
    en.DisenrollmentDate > GetDate())

ORDER BY en.DisenrollmentDate, p.LastName

Answer 1

Since you want to issue 1 query, you should only use 1 datacontext. Add views to one of the databases to represent the other databases tables, then add it all to one LinqToSqlClasses file.

If you can't modify any of the three databases, create a fourth database with views to the other three.