Multiple Context joining c#

Question

In my ASP.Net MVC application, there are two contexts I have linked with my system.

In my controller, I have mentioned it as

private zSqlDb db = new zSqlDb();

private DatabaseZSql dbs = new DatabaseZSql();

So I want to connect some table from both contexts and I wrote this code to link it and get the data.

var EmpDetails = (from e in db.CreateEmployee 
join dep in db.Department on e.DepId equals dep.Id 
join des in db.Designation on e.DesignId equals des.Id 
join emDetails in dbs.EmpDetails on e.Id equals emDetails.EmpID 
join supervisor in db.Employee on emDetails.EmpID equals supervisor.Id
where e.Id == UId select new {
  e.Id,
    e.EmpNo,
    e.EmpName,
    dep.Department,
    des.Designation,
    emDetails.BasicSalary,
    emDetails.EmpCatagory,
    emDetails.EmpGrade,
    emDetails.YearOfService,
    SupervisorName = supervisor.EmpName
});

When I debug the code I got an error ```The specified LINQ expression contains references to queries that are associated with different contexts.``

Separate into 2 different query. First query handle `db` and store the data into memory. Then use the stored data to join `dbs`. — Yong Shun, Aug 17 '22 at 08:57
hi @YongShun thing is that the db1 has the total employee details. One of tables in db2 has the employee Id. So I Need to connect those 2 to get the emp details. Separating the queries I won't think can do this. If can do, can you show me an example ? — Dev Beginner, Aug 17 '22 at 09:08
First, you should pull all `employee Ids` from `db2` so now you have all `Id List` now loop the details from the `Id List` on db context1. You should this way. We cannot execute query combining two context at the same time.. — Md Farid Uddin Kiron, Aug 17 '22 at 09:19

score 0 · Accepted Answer · answered Aug 17 '22 at 20:46

Linq does not allow you to query tables that are from different contexts

So try to fetch all the related data from one context first. Then query that data with the second context. The .AsEnumerable() part is crucial.

var firstContext = (from e in db.CreateEmployee 
                 join dep in db.Department on e.DepId equals dep.Id 
                 join des in db.Designation on e.DesignId equals des.Id 
                 join supervisor in db.Employee on e.Id equals supervisor.Id
                 where e.Id == UId select new { 
                 e.Id
                 e.EmpNo,
                 e.EmpName,
                 dep.Department,
                 des.Designation,
                 SupervisorName = supervisor.EmpName).AsEnumerable();

var empDetails = (from emDetails in dbs.EmpDetails 
                 join e in firstContext on emDetails.EmpID equals e.Id
                 select new {
                        e.Id,
                        e.EmpNo,
                        e.EmpName,
                        e.Department,
                        e.Designation,
                        emDetails.BasicSalary,
                        emDetails.EmpCatgory,
                        emDetails.EmpGrade,
                        emDetails.YearOfService,
                        SupervisorName = e.EmpName
                 });

For more information visit This question

score -1 · Answer 2 · answered Aug 17 '22 at 09:41

Thanks to @YongShun and @mdfariduddinkiron. I wrote this and this worked


 var EmpMain = db.CreateEmployee.Where(x => x.Status == true).ToList();
            var Department = db.CreateDepartment.Where(x => x.Status == true).ToList();
            var Dessignation = db.CreateDesignation.Where(x => x.Status == true).ToList();
            var EmpDtils = dbs.EmpDetails.ToList();
            var MixDetails = (from e in EmpMain
                              join d in Department on e.DepId equals d.Id
                              join de in Dessignation on e.DesignId equals de.Id
                              join ma in EmpDtils on e.Id equals ma.EmpID
                              join sup in EmpMain on ma.Id equals sup.Id
                              where e.Id == UId

                              select new
                              {
                                  e.Id,
                                  e.EmpNo,
                                  e.EmpName,
                                  d.Department,
                                  de.Designation,
                                  ma.BasicSalary,
                                  ma.EmpCatagory,
                                  ma.EmpGrade,
                                  ma.YearOfService,
                                  SupName =sup.EmpName

                              });

But now you're pulling your entire tables into your application memory, and are performing the join in your application process instead of letting the database do what it's good at. Why do you have two contexts to begin with? — CodeCaster, Aug 17 '22 at 09:44
@CodeCaster it because, There is a system in the company that holds the employee master details. So for this app I need to get details from that database table to show the employee names and details. Thats why need to connect to two different contexts — Dev Beginner, Aug 18 '22 at 08:34
Yes yes, "microservices" all the way. Do you understand the rest of what I wrote? You could at least just select the IDs, instead of pulling the entire tables. And for _your_ tables (db.Create...), you don't need the `ToList()`. — CodeCaster, Aug 18 '22 at 08:36
@CodeCaster That's a good point. I will do the same. Thank you very much for the idea. :) — Dev Beginner, Aug 18 '22 at 08:48

Multiple Context joining c#

2 Answers2

Linked