MVC 5 - Multiple DbContexts in a controller

Question

I am developing a MVC 5 internet application and I have a question in relation to having more than one DbContext class in a controller.

My DbContext class has many DbSet objects for different model entities. None of my model entities are using virtual objects, however, my view models are using virtual objects in each controller.

When a controller references different DbSet objects in the same controller/action result, should I use a different DbContext instance for each different DbSet reference? I have encountered the following error in a web service class when I try to access different DbSets to create a view model when using only one DbContext instance:

Message = "There is already an open DataReader associated with this Command which must be closed first."

Can this happen when doing CRUD operations on an object in a MVC controller when different DbSets are accessed using the same DbContext class?

Here is an example:

I have a MapLocationList controller, that when creating/editing a MapLocationList object, the following different DbSet objects are accessed: Asset, MapLocation.

How much increase in overhead is there when using more than one DbContext instance in a MVC controller?

Basically, my question is should a different DbContext instance be used when accessing different DbSets in a controller/action result?

Thanks in advance.

Answer 1

You don't need to use different instances of DBContext in your controller for accessing different DBSets. (Best practice is to use the same instance during the duration of the request, which might access one or more DBSets. This can be accomplished by dependency injection or other means, such as your controller having a member variable of the DBContext class and disposing it on when controller is disposed). As long as you are disposing your dbcontext instance properly, using the same instance multiple times in the controller is fine. Your problem is that you don't have MultipleActiveResultSets (MARS) enabled. In your connection string, insert the following

MultipleActiveResultSets=True

More info about MARS can be found here