C# EntityFrameWork error 「cannot use multiple context instances within a single query execution」

Question

I am beginner of C# and EntityFramework Sorry if my English is poor.

I want to join two tables(OPE_TABLE, CLASS_TABLE) in one query by LinQ to Entityes. I was able to confirm that data when I remove join-CLASS_TABLE from query.

Add join-CLASS_TABLE then I receive "Cannot use multiple context instances within a single query execution. Ensure the query uses a single context instance" I don't know this meaning. I understand that OPE_TABLE is considered as one table because it is self-joined.

OPE_TABLE and CLASS_TABLE contexts are defined in separate files. Is it possible to retrieve data using multiple contexts in a single query?

this is ideal.

SELECT T2.OPECD
     , T2.OPENM
  FROM OPE_TABLE T1
  LEFT JOIN OPE_TABLE T2
    ON T2.CLAID = T1.CLAID
   AND T2.CLBID = T1.CLBID
   AND T2.POSITIONID IN ('00001', '00002', '00003')
   AND T2.DELETEKB = '1'
    OR T2.OPECD = T1.OPECD
  LEFT JOIN CLASS_TABLE CL
    ON CL.CLASSID = T2.CLASSID
   AND CL.CLASSKB = '6'
 WHERE T1.TANCD = :arg_CD  
 ORDER BY CASE WHEN T1.OPECD = T2.OPECD THEN 0 ELSE 1 END
     , CL.OPEID
     , T2.OPENM

The code is below. thank you.

namespace Inf.Operator
{
    public class EFoperatorRepository
    {
        private readonly operatorContext _context;
        private readonly classContext _classContext;

        public EFoperatorRepository(operatorContext context, classContext classContext)
        {
            _context = context;
            _classContext = classContext;
        }

        public IEnumerable<Operator> FindIdAndNm(string arg_CD)
        {
            var list = new List<string> { "00001", "00002", "00003" };

            var query = (from t1 in _context.OPE_TABLE
                         where t1.operatorId.Equals(arg_CD)
                         join t2 in _context.OPE_TABLE
                            on new { t1.departmentId, t1.teamId }
                            equals new { t2.departmentId, t2.teamId } into ts
                         from tsResult in ts.DefaultIfEmpty()
                         where list.Contains(tsResult.positionId)
                            && tsResult.deletekb.Equals("1")
                            || tsResult.opeCd.Equals(t1.opeCd)
                            
                         // ★↓If this is removed, data can be acquired.
　　　　　　　　　　　　   //   　However, if you include it, an error occurs.
                         join cl in _classContext.CLASS_TABLE
                          on tsResult.groupId equals cl.classId
                         where cl.classKb.Equals("6")
                         // ★↑
                         
                         select new
                         {
                             tsResult.operatorCd,
                             tsResult.operatorNm
                         }
                         )ToList();

            foreach (var item in query)
            {
                Console.WriteLine(item);
            }
        }
    }
}

Answer 1

The Error message tells you exactly what the problem is: You are using two contexts (_classContext and _context) inside a single query, and that's not possible.

I assume your two contexts map to different databases, so just create a view on your "main" db that pulls data from the second database and map that.