How can I use two different data contexts in one LINQ request?

Question

Hi all,

Can anybody help me with next: how can I use two different data contexts in one LINQ request?

using (var db = new DataMapDataContext(Connection))
        {    
            using (var dbAdd = new DataMapDataContext(ConnectionAdd))
            {
                return (from i in dbAdd.ITEMs
                        join p in db.U_OTT_PINs on i.ITEMNO equals p.PIN_CODE
                        where p.PIN_TYPE == Utils.PinItem
                        select ...
            }
        }

Is it possible?

UPDATE:

I resolved my issue, but not with different data contexts:

        var listPinnedItems = new List<string>();
        using (var db = new DataMapDataContext(Connection))
        {
            listPinnedItems = (from lpi in db.U_OTT_PINs
                               where lpi.PIN_TYPE == Utils.PinItem
                               select lpi.PIN_CODE).ToList();
        }

        using (var dbAdd = new DataMapDataContext(ConnectionAdd))
        {
            return (from i in dbAdd.ITEMs
                    where listPinnedItems.Contains(i.ITEMNO) 
                    ...

Answer 1

I don't believe so - these two different contexts could be involved in different transations in the same database, or even talking to completely different database instances. How would it construct SQL to work across the two?

If you could explain what you're trying to do, we may be able to help you more.

Answer 2

I'm afraid LINQ to SQL is not made for querying across different databases. See below for possible workaround?

http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/3a15002c-704d-49f9-a8cc-0d2bde186e1d

Answer 3

It's possible to use two datacontexts, but you can't do database queries across both at the same time. You could however get the data involved in both queries and query the objects using generic linq statements (linq2objects).

Answer 4

LinQ is not supporting different DataContexts in one query. You can make

var x = dataContextA;
var y = dataContextB;

var result = //write linq with join between x and y

Answer 5

This is one of those questions that shouldn't be answered without first asking "What are you trying to achieve?".

Are both datacontexts pointing to the same database or different databases?

If they connect to same database and the only reason why you have two is that you have separated your entities then you can use just one DC to query a table that 'belongs' to another datacontext. Just use GetTable and L2S will resolve the mappings based on class and member attributes.

If they point to different databases on the same server and the login you're connecting to one of the DBs as has rights to read from the second DB you can include the table from one database in a datacontext based on another db by simply adding the database name as a prefix in the .dbml file.