Error: "The specified LINQ expression contains references to queries that are associated with different contexts"

Question

I am receiving the error shown in the title from a LINQ query that includes two tables from two different edmx files. Here is the query:

var query = (from a in db1.Table1
           join b in db1.Table2 on a.Id equals b.Id
           orderby a.Status
           where b.Id == 1 && a.Status == "new"
           select new
           {
               Id = a.Id,
               CompanyId = (from c in db2.Company
                            where s.Id == a.Id
                            select
                            new { c.CompanyId })
           });

db1 and db2 are contexts that are associated with two different edmx files. How can I overcome this error?

Answer 1

You'll have to perform two database queries:

var IDs =  (from a in db1.Table1 
            join b in db1.Table2 on a.Id equals b.Id 
            orderby a.Status 
            where b.Id == 1 && a.Status == "new" 
            select new a.Id).ToArray();

var query = from c in db2.Company
            join a in IDs on c.Id equals a.Id
            select new { Id = a.Id, CompanyId = c.CompanyId };

The .ToArray() is crucial. It prevents EF from trying to execute the combined query (which will fail since it uses two different contexts). You can use .AsEnumerable() if you'd rather keep lazy loading.

---

And your follow-up question:

Is there any other way to make the LINQ query more optimized? That is, to perform the action in a single LINQ query itself?

In order for your original query to successfully run, it must use only a single data context, which means all the data must be available from a single EDMX, which in turn means a single connection string. There are several ways you can achieve that:

• If both tables are on the same database, add them both to a single EDMX.
• If they're on different databases but on the same instance, create a view on one of the databases that selects from the table on the other database, then add the local table and view to a single EDMX.
• If they're on different instances/servers, created a linked server, then create a view of the table on the linked server, then add the local table and view to a single EDMX.

Answer 2

You either need to add the second table to the model of the first context. If this is in multiple databases, you need to do the secondary lookup client-side using a Linq to Objects join.