Error: "The specified LINQ expression contains references to queries that are associated with different contexts"
c#, edmx, entity-framework, linq
Solution
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.
Problem
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?