The LINQ expression contains references to queries that are associated with different contexts

Question

Here's my code:

var myStrings = (from x in db1.MyStrings.Where(x => homeStrings.Contains(x.Content))
                    join y in db2.MyStaticStringTranslations on x.Id equals y.id
                    select new MyStringModel()
                    {
                        Id = x.Id,
                        Original = x.Content,
                        Translation = y.translation
                    }).ToList();

And I get the error that the specified LINQ expression contains references to queries that are associated with different contexts. I know that the problem is that I try to access tables from both db1 and db2, but how do I fix this?

Are the individual tables small enough to fit in memory? Is either of the two tables much larger than the other one? — Sergey Kalinichenko, Oct 02 '14 at 19:57
Linq sends optimized queries to the database , so if there are two contexts , it is impossible to send optimized query to the database. As @dasblinkenlight said,if the tables are small, bring the data to memory and do the join on IEnumerable instead of Iqueryable — Dan Hunex, Oct 02 '14 at 20:02

score 7 · Accepted Answer · answered Oct 02 '14 at 20:31

7

MyStrings is a small table

Load filtered MyStrings in memory, then join with MyStaticStringTranslations using LINQ:

// Read the small table into memory, and make a dictionary from it.
// The last step will use this dictionary for joining.
var byId = db1.MyStrings
    .Where(x => homeStrings.Contains(x.Content))
    .ToDictionary(s => s.Id);
// Extract the keys. We will need them to filter the big table
var ids = byId.Keys.ToList();
// Bring in only the relevant records
var myStrings = db2.MyStaticStringTranslations
    .Where(y => ids.Contains(y.id))
    .AsEnumerable() // Make sure the joining is done in memory
    .Select(y => new {
        Id = y.id
        // Use y.id to look up the content from the dictionary
    ,   Original = byId[y.id].Content
    ,   Translation = y.translation
    });

answered Oct 02 '14 at 20:31

Sergey Kalinichenko

714,442
84
1,110
1,523

Good answer, I didn't think about what the size of MyStaticStringTranslations is. I'm curious why you do ids = byId.Keys.ToList(). Can't you just write .Where(y => byId.Contains(y.id)) ? – Jared Moore Oct 02 '14 at 22:31
1

@JaredMoore Yes, you can definitely use `byId.Keys.Contains(y.id)`. I added a separate line for it because I wanted a line item for the comment. – Sergey Kalinichenko Oct 03 '14 at 00:42
Fair enough. I would avoid copying it into a separate list because (1) it wastes memory, and (2) Dictionary.Contains(), which is O(1) best case and O(n) worst case, should theoretically scale better than List.Contains() which is always O(n). Not that it makes a huge difference if MyStrings is a small table. :) – Jared Moore Oct 03 '14 at 02:50

score 1 · Answer 2 · edited May 23 '17 at 12:16

You are right that db1 and db2 can't be used in the same Linq expression. x and y have to be joined in this process and not by a Linq provider. Try this:

var x = db1.MyStrings.Where(xx => homeStrings.Contains(xx.Content)).ToEnumerable();

var y = db2.MyStaticStringTranslations.ToEnumerable();

var myStrings = (from a in x
                 join b in y on x.Id equals y.id
                    select new MyStringModel()
                    {
                        Id = x.Id,
                        Original = x.Content,
                        Translation = y.translation
                    }).ToList();

Refer to this answer for more details: The specified LINQ expression contains references to queries that are associated with different contexts

dasblinkenlight's answer has a better overall approach than this. In this answer I'm trying to minimize the diff against your original code.

score 0 · Answer 3 · answered May 27 '16 at 11:35

I also faced the same problem: "The specified LINQ expression contains references to queries that are associated with different contexts." This is because it's not able to connect to two context at a time so i find the solution as below. Here in this example I want to list the lottery cards with the owner name but the Table having the owner name is in another Database.So I made two context DB1Context and DB2Context.and write the code as follows:

 var query = from lc in db1.LotteryCardMaster
             from om in db2.OwnerMaster
             where lc.IsActive == 1
             select new
                        {
                            lc.CashCardID,
                            lc.CashCardNO,
                            om.PersonnelName,
                            lc.Status

                        };

AB.LottryList = new List<LotteryCardMaster>();
            foreach (var result in query)
            {
                AB.LottryList.Add(new LotteryCardMaster()
                {
                    CashCardID = result.CashCardID,
                    CashCardNO = result.CashCardNO,
                    PersonnelName =result.PersonnelName,
                    Status = result.Status

                });
            }

but this gives me the above error so i found the other way to perform joining on two tables from diffrent database.and that way is as below.

var query = from lc in db1.LotteryCardMaster
            where lc.IsActive == 1
            select new
                        {
                            lc.CashCardID,
                            lc.CashCardNO,
                            om.PersonnelName,
                            lc.Status

                        };

AB.LottryList = new List<LotteryCardMaster>();
            foreach (var result in query)
            {
                AB.LottryList.Add(new LotteryCardMaster()
                {
                    CashCardID = result.CashCardID,
                    CashCardNO = result.CashCardNO,
                    PersonnelName =db2.OwnerMaster.FirstOrDefault(x=>x.OwnerID== result.OwnerID).OwnerName,
                    Status = result.Status

                });
            }

The LINQ expression contains references to queries that are associated with different contexts

3 Answers3

Linked