DbContext and Connection Pools

Question

In the application that I inherited there, it is in the base controller, which inherits every other controller in the application.

public BaseController() { db = new MyDbContext(); db.Database.Log = s => Debug.Write(s); } public MyDbContext() : base("name=MyDbContext") { // hack to force Visual Studio to deploy the Entityframework.SqlServer package var instance = SqlProviderServices.Instance; } 

Due to how the application was developed, at least 2 contexts are created upon request. (This MVC application is the call to HomeController on every page, plus any other controllers called for a particular page.)

My question is when DbContext create a connection to SQL Server? Is it right when the context is created, or only when the request is executed?

If this is the first, then I will use 2 times twice as many connections to the SQL server as necessary, and if this is the last, then this is probably not a very big problem.

I do not think that I can reorganize this in the near future, of course, not without justification. What are the potential pitfalls of this project I should know?

Entity Framework 6.1.3

Answer 1

Since you are not trying to create and transfer the connection yourself in your context constructor, then yes, as others say, EF will receive / release connections from the connection pool as necessary, and not when it is created.

Check out this quote from the EF documentation:

Connections

By default, context manages database connections. Context opens and closes connections as needed. For example, a context opens a connection to execute a query, and then closes the connection when all result sets have been processed.

There are times when you want to have more control when the connection opens and closes. For example, when working with SQL Server Compact, opening and closing the same connection is expensive. You can control this process manually using the Connection property.

For more information, see the following links:

https://msdn.microsoft.com/en-us/data/jj729737

https://msdn.microsoft.com/en-us/data/dn456849

Answer 2

Entity Framework follows Open late and close early. That way, he only opens the connection, when he needs it too, IE to materialize the request, then he closes it as soon as he can.

If you can, you must go to one context instance for each request. It also saves everything that happens during a request in a single transaction. You can do this quite easily if you use the Dependency Injection container to instantiate your controllers.

Answer 3

A connection is opened only when the request is executed. The connection pool is managed by the ADO.NET (SqlConnection) classes. Having multiple instances of DbContext for each request is fine and sometimes necessary. You will not have twice as many connections.