DbContext and Connection pools

Question

In an application that I've inherited there's this in a Base Controller, that every other controller in the application inherits from.

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 the way the application has been designed, at least 2 contexts are created per request. (It's an MVC application and there is a call to the HomeController on every page plus whatever other controllers are called for a particular page.)

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

If it's the former, then i will be using 2 twice the number of connections to SQL server than is needed, and if it's the latter then it's probably not too much of an issue.

I don't think i can refactor this in the immediate future, certainly not without justification. What potential pitfalls of this design should i be aware of?

Entity Framework 6.1.3

Solution 1

Because you are not attempting to create and pass a connection yourself in your context's constructor, then, yes, as others are saying, EF will get/release connections from a connection pool as needed, not when it is constructed.

Notice this quote from the EF documentation:

Connections

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

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

See the following links for more information:

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

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

Solution 2

Entity Framework follows the Open late and close early principle. So, it only opens the connection when it needs too, IE to materialize a query, then it closes it as soon as it can.

If you can, you should move to a single context instance per request. This also keeps everything that happens during the request in a single transaction. You can do this pretty easily if you are using a Dependency Injection container to instantiate your controllers.

Solution 3

Connection is opened only when a query is executed. Connection pool is managed by ADO.NET classes (SqlConnection). Having multiple DbContext instances per request is okay and sometimes is necessary. You will not have twice number of connections in general.