"The supplied LINQ phrase includes references to queries associated with various contexts," says the error message.

c# edmx entity-framework linq

Question

A LINQ query that involves two tables from two distinct edmx files is giving me the error in the title. This is the problem:

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 consist of contexts linked to two separate edmx files. How can I correct this mistake?

1
59
8/16/2017 2:25:48 PM

Accepted Answer

There are two database queries you must run:

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 essential. It stops EF from attempting to run the combined query (which will fail since it uses two different contexts). You may utilize.AsEnumerable() if you'd want to continue lazily loading.


And now for your follow-up query:

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

Your initial query has to utilize just one data context, which means all the data must be accessible from just one EDMX, which calls for just one connection string for it to execute. There are various methods to do that:

  • Add both tables to a single EDMX if they are on the same database.
  • Create a view on one of the databases that selects from the table on the other database if they are on separate databases but the same instance; next, add the local table and view to a single EDMX.
  • Construct a connected server, then create a view of the table on it, then add the local table and view to a single EDMX if they are on separate instances of the same server.
115
8/16/2017 1:45:33 PM

Popular Answer

Either the second table has to be included to the model of the first context. You must do the secondary search client-side using a Linq to Objects join if this is spread across many databases.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow