Avoiding MySQL Connection Per Request in EntityFramework

Question

We use Pomelo.EntityFrameworkCore.MySql (which uses MySqlConnector under the hood) to establish a MySQL connection using a typical DbContext approach in Entity Framework.

services.AddDbContext<MyDbContext>(opt =>
    opt.UseMySql(_mysqlConnectionString, _mysqlVersion, b =>
    {
        b.UseNewtonsoftJson();
    })
);

We then use dependency injection on some controllers that require a database connection. For example:

public class ClientController : Controller
{
    private readonly MyDbContext _context;

    public ClientController(MyDbContext context)
    {
        _context = context;
    }
}

This works fine. However, we have some controllers that do not require any MySQL connection since they do not interact with the database. An example would be getting the server time. We wrap this in its own controller with no dependency injection:

[Produces("application/json")]
[Route("client")]
public class SimpleClientController : Controller
{
    [HttpPost("GetTime")]
    public IActionResult GetTime([FromBody] GetTimeRequest request)
    {
        var response = new GetTimeResponse()
        {
            Time = DateTime.UtcNow
        };

        return Json(response);
    }
}

However, it appears that a MySQL connection is created for these types of requests as well, which surprised me. I thought by using dependency injection we would only create a DbContext when required by the controller. Reading online suggests that a DbContext may, in fact, be created for every request, regardless of the controller being utilized. However, I'm having trouble finding definitive answers, so I'm hoping stack overflow can help here. For example, the documentation doesn't make it clear if just a DbContext is created (without establishing a connection), etc. I am curious if the inclusion of Pomelo's MySQL framework is changing the default behaviour here, and maybe they create a connection on DbContext creation?

My questions:

1. Is it true that a MySQL connection will be created due to the creation of a DbContext per request (regardless of whether the request will utilize a controller that injects the DbContext)?

2. Is there a simple way to fix this? I would like to only create a MySQL connection for requests that require one. I am currently exploring using DbContextFactory instead on DbContext - which seems like it would do the trick, but will require a large code change.

3. Any other comments related to this would be helpful.

Answer 1

1. For example, the documentation doesn't make it clear if just a DbContext is created (without establishing a connection)

Actual database connections are pooled, see this quote from the docs:

Note that context pooling is orthogonal to database connection pooling, which is managed at a lower level in the database driver.

And this github issue:

Unless you've disabled pooling (by setting Pooling=false in your connection string), opening and closing connections has negligible overhead and there shouldn't be any reason to avoid it. When a connection is closed it is returned to the pool, and when one is opened it is taken from the pool. This is the default/recommended way to work, rather than trying to implement your own connection reuse. Npgsql (and other ADO.NET drivers) has a high-performance pool (the upcoming 4.0 version improves performance even more in this area).

So you should not be concerned that much about the connection object creation

2. I am currently exploring using DbContextFactory instead on DbContext

As DBcontext pooling doc states (also relevant for the previous point):

A DbContext is generally a light object: creating and disposing one doesn't involve a database operation, and most applications can do so without any noticeable impact on performance.

Note that you can wrap the factory usage so end user code will not change (similar to the sample in the docs):

serviceCollection.AddPooledDbContextFactory<YourContext>(...);
serviceCollection.AddScoped(sp => 
    sp.GetRequiredService<IDbContextFactory<WhatsNewContext>>().CreateDbContext());