EF CORE 3.0 Cannot use multiple DbContext instances within a single query execution

Question

After upgrading from .Net Core 2.2 to 3.0, the apps is throwing this err message.

Cannot use multiple DbContext instances within a single query execution

What is the workaround?

IQueryable<ApplicationUser> query;

var queryJoin = from ci in _courseInstructorRepository.Table
        join uc in _userCourseRepository.Table on ci.CourseId equals uc.CourseId
        select new { ci, uc };

if (userId > 0)
    queryJoin = queryJoin.Where(x => x.ci.UserId == userId);

if (courseId > 0)
    queryJoin = queryJoin.Where(x => x.uc.CourseId == courseId);

if (classId > 0)
    queryJoin = queryJoin.Where(x => x.uc.CourseClassId == classId);

query = queryJoin.Select(x => x.uc.User).Distinct();

if (!string.IsNullOrEmpty(studentFirstChar))
    query = query.Where(x => x.FirstName.StartsWith(studentFirstChar));

if (schoolId > 0)
    query = query.Where(x => x.SchoolId == schoolId);

query = query.OrderBy(x => x.UserName);

return new PagedList<ApplicationUser>(query, pageIndex, pageSize);

Gert Arnold · Accepted Answer · 2019-11-03 10:27:35Z

2

You have a couple of design flaws in your code that EF core 2 swept under the carpet.

Your repositories don't share one context instance. EF core 2 couldn't create one SQL query from your code either, but it silently switched to client-side evaluation. That is, it just executed two SQL queries and joined them in memory. This must have been highly inefficient. One of the best design decisions in EF core 3 was to abandon automatic client-side evaluation, so now you're getting this error.
You don't use navigation properties. Using an ORM like EF, using manual joins should hardly ever be necessary. The Instructor class should have a navigation property like Courses, and Course a navigation property like Instructor.
Don't use this redundant repository layer anyway. As you're already experiencing in this small piece of code, it usually makes things harder than necessary without any added value.

answered Nov 3, 2019 at 10:27

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gert Arnold Over a year ago

Downvoter, care to explain? Maybe you thought I advocated a singleton context instance? It's about one context instance in one unit of work in which various repositories participate.

Samuel Jenks · Accepted Answer · 2019-11-03 08:15:41Z

1

One of your variables was created using another instance of DBContext, so when you try to use it as part of another DBContext's query it throws. The work around is to close the first DBContext and invoke DBContext.Attach(model) on the second.

answered Nov 3, 2019 at 8:15

Samuel Jenks

1,2015 gold badges23 silver badges38 bronze badges

Collectives™ on Stack Overflow

EF CORE 3.0 Cannot use multiple DbContext instances within a single query execution

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related