Entity Framework Core: Should I explicitly open and close database connections? 🤔
By default Entity Framework (EF) Core opens and closes connections every time an operation is executed in the database (insert, select, update, delete etc).
It is well known that closing db connection after an operation is finished is a good practice and EF Core takes care of this, however letting EF Core manage opening and closing database connections is not always the best approach.
In this article I will be comparing connections managed by EF Core as well as managed manually in a given scenario and explain why sometimes you should consider taking care of your own connections.
Default Connection Management Behavior
In order to demonstrate the default EF Core behavior when it comes to managed connection,
I have prepared a simple example where 10.000 queries are executed in the database, as you can see below. (the following examples can be found in this GitHub repository: EFCore.DBConnections.Article).
At line 11 the state change event was subscribed, so that we can see every time the connection changes, also it was implemented a counter that is initialized at line 3 and incremented at line 13 (only when a new connection is opened).
As a result of this code snippet we can see below that exactly 10.000 connections were opened, besides that the time the operations were completed is printed.
In the next example shown below the approach is different, so instead of letting EF manage the connection a single connection is open at line 16 and closed manually at line 20 after the operations are completed.
As a result we can see below that only 1 connection was opened and it took less time for completing the operations.
The two examples shown above were performed with connection pooling enabled, on the other hand if this setting is disabled the difference in time to complete the execution of these operations is much more evident, as we can see in the examples below.
Note that in the first execution using a connection managed by EF and connection pooling enabled, the queries took just over a second to be executed and in this example we can see that it took almost a minute to complete.
As for manually managed queries, the difference in time even with connection pooling disabled was smaller, I would say that there was basically no relevant difference.
Connection pooling
Just to contextualize and make clear what caused such a time difference between these two approaches regarding connection management, with pooling enabled and disabled, it is important to understand what Connection pooling is and what it is for.
Basically connection pooling is the ability to reuse database connections instead of recreating connections every time operations are requested. In other words, connection pooling is a cache of database connections, so it is possible to reduce the cost of opening and closing connections by keeping the pool of open connections which can then be reused as needed, and thus optimize performance. It is possible to enable (default) or disable connection pooling through the connection string itself, as shown below:
Conclusion
EF Core provides several mechanisms that help us a lot when it comes to communication between applications and databases, one of these facilities is associated to connection management, however EF Core also gives us the opportunity to manage our own connections and therefore, it is up to us to assess the scenario and envisage whether or not we should delegate connection management. For example, in scenarios where we need to run several queries in a given context and consolidate the information in a single method, opening and closing the connection explicitly can be a good alternative to optimize application performance.
Good ?V I should certainly pronounce, impressed with your website. I had no trouble navigating through all tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or something, site theme . a tones way for your client to communicate. Excellent task..
With every thing that appears to be building within this subject matter, your opinions tend to be somewhat exciting. Having said that, I beg your pardon, but I do not subscribe to your entire theory, all be it refreshing none the less. It appears to everyone that your comments are generally not entirely validated and in fact you are yourself not even wholly convinced of your argument. In any case I did enjoy examining it.
Interesting!