帮助将多数据库SQL查询转换为LINQ [英] Help converting multi DB SQL Query to LINQ

问题描述

我有下面的SQL查询，该查询返回了我期望的结果，我希望在LINQ中完成同样的事情，但是到目前为止，我的结果还不那么出色.如我所见，主要障碍是数据来自3个独立的DB.我能够在LINQ中完成此操作，但是它非常慢，

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.

因此，事不宜迟，硬编码的Guid()是唯一的例外，因为它被传入:

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

推荐答案

由于要发出1个查询，因此只能使用1个datacontext.将视图添加到其中一个数据库以表示其他数据库表，然后将其全部添加到一个LinqToSqlClasses文件.

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.

这篇关于帮助将多数据库SQL查询转换为LINQ的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！