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.``

CodePudding user response：

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

CodePudding user response：

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

                              });