How To Join Tables from Two Different Contexts with LINQ2SQL?

Question

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?

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.

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.

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.

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...
             });

Answer 1

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

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)

Edit: 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.

Answer 2

you could try using a GetTable command. I think this loads all of contextB.TableB's data first, not 100% sure on that though. I don't have an environment set up to play around in or test this out so let me know if it works =)

from a in contextA.TableA
join b in contextB.GetTable<TableB>() on a.id equals b.id
select new { a, b }

Answer 3

If you cannot extract the 2 tables into List objects and then join them then you will probably have to do something database side. I would recomend creating a linked server and a view on the DB server you have control of. You can then do the join in the view and you would have a very simple LINQ query to just retrieve the view. I am njot sure how LINQtoSQL could every do a join between 2 data contexts pointing to 2 different servers.