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

by

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.

More Related Contents:

  1. How to create LINQ Expression Tree to select an anonymous type
  2. ‘Contains()’ workaround using Linq to Entities?
  3. Unable to create a constant value of type Only primitive types or enumeration types are supported in this context
  4. “A lambda expression with a statement body cannot be converted to an expression tree”
  5. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  6. Establish a link between two lists in linq to entities where clause
  7. Entity Framework/Linq EXpression converting from string to int
  8. A specified Include path is not valid. The EntityType does not declare a navigation property with the name *
  9. Entity Framework (.NET Full Framework) Ordering Includes [duplicate]
  10. How to compare only date components from DateTime in EF?
  11. Entity Framework 6 Code First Custom Functions
  12. How to group by on 2 child entities and get total of both this child entities?
  13. Order by dynamic parameter
  14. Dynamic table name in linq [duplicate]
  15. Linq Syntax – Selecting multiple columns
  16. Join and Include in Entity Framework
  17. Filtering include items in LINQ and Entity Framework [duplicate]
  18. Creating dynamic queries with entity framework
  19. Log Queries executed by Entity Framework DbContext
  20. The LINQ expression could not be translated and will be evaluated locally
  21. Update records using LINQ
  22. Calling a SQL User-defined function in a LINQ query
  23. Remove item from list based on condition
  24. How to group by DateTime.Date in EntityFramework
  25. Linq Query keeps throwing “Unable to create a constant value of type System.Object….”, Why?
  26. How to include() nested child entity in linq
  27. System.Data.MetadataException: Unable to load the specified metadata resource
  28. Should the order of LINQ query clauses affect Entity Framework performance?
  29. LINQ Query to Convert string to datetime
  30. Entity Framework select one of each group by date

Leave a Comment