如何使用LINQ2SQL连接来自两个不同上下文的表? [英] How To Join Tables from Two Different Contexts with LINQ2SQL?

问题描述

我的应用程序(不同的数据库)中有2个数据上下文，并且需要能够使用上下文B中的表的正确联接来查询上下文A中的表.如何在LINQ2SQL中执行此操作?

I have 2 data contexts in my application (different databases) and need to be able to query a table in context A with a right join on a table in context B. How do I go about doing this in LINQ2SQL?

为什么?:我们正在使用SaaS产品来跟踪我们的时间，项目等，并希望向该产品发送新的服务请求，以防止我们的团队重复输入数据.

Why?: We are using a SaaS product for tracking our time, projects, etc. and would like to send new service requests to this product to prevent our team from duplicating data entry.

上下文A::此数据库存储服务请求信息.它是第三方数据库，我们无法更改此数据库的结构，因为它可能会对下游产生意想不到的不可支持的后果.

Context A: This db stores service request information. It is a third party DB and we are not able to make changes to the structure of this DB as it could have unintended non-supportable consequences downstream.

上下文B::此数据存储已处理的服务请求的日志"数据.我和我的团队对这个数据库的结构等具有完全的控制权.未经处理的服务请求应该进入该数据库，然后另一个进程将其识别为未处理，并将记录发送给SaaS产品.

Context B: This data stores the "log" data of service requests that have been processed. My team and I have full control over this DB's structure, etc. Unprocessed service requests should find their way into this DB and another process will identify it as not being processed and send the record to the SaaS product.

这是我要修改的查询.我最初能够执行！list.Contains(c.swHDCaseId)，但这不能处理超过2100个项目.有没有一种方法可以将联接添加到其他上下文中?

This is the query that I am looking to modify. I was able to do a !list.Contains(c.swHDCaseId) initially, but this cannot handle more than 2100 items. Is there a way to add a join to the other context?

var query = (from c in contextA.Cases
             where monitoredInboxList.Contains(c.INBOXES.inboxName)
             //right join d in contextB.CaseLog on d.ID = c.ID....
             select new
             {
                 //setup fields here...
             });

推荐答案

在数据库解决方案之外，您最好的选择是在执行后使用LINQ联接(到对象).

Your best bet, outside of database solutions, is to join using LINQ (to objects) after execution.

我意识到这不是您想要的解决方案.至少在此级别上，您不必担心IN列表限制(.包含)

I realize this isn't the solution you were hoping for. At least at this level, you won't have to worry about the IN list limitation (.Contains)

上面的数据库解决方案之外的确实指向链接服务器解决方案，其中您允许上下文A中的表/视图存在于上下文B中的数据库中.

outside of database solutions above really points to linked server solutions where you allow the table/view from context A to exist in the database from context B.

这篇关于如何使用LINQ2SQL连接来自两个不同上下文的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！