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

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.

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.