(Linq/Lambda) Join 2 or more tables with 2 DBContext

Question

Im Getting error like this: The specified LINQ expression contains references to queries that are associated with different contexts.

var employee = new ApplicationDbContext().Employee;
var otherTable = new OtherDbContext().OtherTable;

var returnValue = (from e in employee
                        join o in otherTable on e.Id equals o.Id
                        select new
                        {
                            e.Name,
                            e.Address,
                            o.Others
                        });

Any Solution/s? Thanks!

I'm not sure you can join across two contexts. EF has to convert that to a SQL query somehow — garryp, May 14 '15 at 11:18
Not possible to query both contexts at once, see this question/answer: http://stackoverflow.com/questions/4278993/is-it-possible-to-perform-joins-across-different-databases-using-linq — Terence, May 14 '15 at 11:19
The comments are correct. Note that the join method's inner Type will be IEnumerable anyway, so just load the entities of the 2nd context (inner) to an IEnumerable variable. https://msdn.microsoft.com/en-us/library/vstudio/bb534644(v=vs.100).aspx — Brett Caswell, May 14 '15 at 14:24
Somehow, soon it will have a lot of data. So i think the performance will suffer if I query all the records in 2nd context(something like .ToList()). Thanks! — jmvtrinidad, May 15 '15 at 01:13

score 4 · Accepted Answer · answered May 14 '15 at 11:24

4

You should instantiate your DBContext generally and don't specify a table/model. Example:

 private ApplicationDBContext db = new ApplicationDbContext();

Then choose if you're still going to use LINQ or Raw SQL. I believe you're more familiar with SQL because you mentioned joining tables so why not use that? Here's a tutorial on how to use Raw SQL.

If you still insist in using LINQ and involve join, here's a good reference for it.

answered May 14 '15 at 11:24

TJ Riceball

240
1
2
11

All the queries in project is using linq or lambda, it will be unform if I use raw sql in this particular query. Thanks! – jmvtrinidad May 15 '15 at 01:19
It will be more good enough if we do it on [SP](https://msdn.microsoft.com/en-us/data/gg699321.aspx) and create a [linked server](http://forums.asp.net/t/1254974.aspx?How+to+join+tables+from+different+databases+in+SQL+select+statement+) instead calling `.ToList()` that caused to query it all. So I'll accepting this answer. – jmvtrinidad Jul 13 '16 at 02:28

score 0 · Answer 2 · answered Dec 18 '21 at 07:23

The bets solution is to have one DbContext with the two entities with. This way EF can easily join between them. You can create 2 more, distinct entities with just the fields you want. If your concerned about maintaining duplicative types, consider creating interfaces between them (SOLID's interface segregation principal). This way if you alter a property of field, do it first via the interface and it will force you to correct the duplicative types.

Assuming you cannot alter your existing ApplicationDbContext & OtherDbContext, you can create a third DbContext and 2 duplicative models this way:

interface IEmployee { string Name { get; } string Address { get; } }
interface IOtherTable { IEnumerable Others { get; } }
public class Employee : IEmployee { /* your existing class that may have more field*/ }
public class OtherTable : IOtherTable { /* your existing class that may have other fiels*/ }

public class Employee2 : IEmployee { /* Making this a subclass in a controller or something is preferred */ }
public class OtherTable2 : IOtherTable { /* Making this a subclass in a controller or something is preferred */ }
public sealed class CombinedDbContext {
    public DbSet<Employee2> { get; set; }
    public DbSet<OtherTable2> { get; set; }
}

Now you can do this safely and without hacky code...

var con = new CombinedDbContext();
var employee = con.Employee;
var otherTable = con.OtherTable;

var returnValue = (from e in employee
                    join o in otherTable on e.Id equals o.Id
                    select new
                    {
                        e.Name,
                        e.Address,
                        o.Others
                    });

score -1 · Answer 3 · answered May 14 '15 at 20:25

-1

List<Employee> empList = new ApplicationDbContext().Employee.ToList();
List<OtherTable> othrList= new OtherDbContext().OtherTable.ToList();

var returnValue = (from e in empList 
                        join o in othrList on e.Id equals o.Id
                        select new
                        {
                            e.Name,
                            e.Address,
                            o.Others
                        });

try this it will work.....

answered May 14 '15 at 20:25

Manish Shukla

59
6

Thanks for response! .ToList() is querying all the records, so maybe it will be stressful to query it all. what do you think? Other solution? – jmvtrinidad May 15 '15 at 01:10
Manish, Thanks, but its a bad idea to instantiate every entity just to do a join. – RashadRivera Dec 18 '21 at 07:08

(Linq/Lambda) Join 2 or more tables with 2 DBContext

3 Answers3