Where is a good place to dispose of the SqlConnection object created in AddDbContext? (.Net Core 2.2)

Question

I am trying to get Azure's Managed Identity authentication to work with my app that I will be deploying on Azure App Service to connect to an Azure SQL Server.

After looking through some examples and sample code I am attempting the following implementation:

services.AddDbContext<MainContext>(optionsBuilder =>
{
    SqlConnection connection = new SqlConnection();
    connection.ConnectionString = Configuration.GetConnectionString("DefaultConnection");
    connection.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;

    optionsBuilder.UseSqlServer(connection);
});

Where would be the right place to dispose of this SqlConnection? Is there a better way to implement this AccessToken set up?

Answer 1

You could try by first registering your context as normal:

services.AddDbContext<MainContext>(builder => 
               builder.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

According the documentation, this is registered as "Scoped" by default, so the connection will be automatically opened and closed on each request.

The AddDbContext extension method registers DbContext types with a scoped lifetime by default.

Then, retrieve the registered context and add the access token as follows:

services.AddScoped<MainContext>(serviceProvider => {
      var dbContext = serviceProvider.GetRequiredService<MainContext>();  
      var connection = dbContext.Database.GetDbConnection() as SqlConnection;
      if(connection == null) {
          return dbContext;
      }
      connection.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;
      return dbContext;
});

Now, on every request, the connection is created and disposed for you and the access token will be retrieved.

A different way (perhaps cleaner), would be to inherit from your context and add the token in the constructor:

public class ContextWithAccessToken : MainDbContext 
{
    public ContextWithAccessToken(DbContextOptions options) : base(options)
    {
        var connection = (SqlConnection)this.Database.GetDbConnection();
        connection.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/").Result;
    }
}

Then just register this context instead:

services.AddDbContext<ContextWithAccessToken>(builder => 
               builder.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));