How to properly manage and dispose SqlConnection with SqlCredential in EF Core using Dependency Injection?

Question

I am working on an ASP.NET Core project where I am using SqlCredential for database authentication, provided by a SqlCredentialProvider which is designed as a singleton to ensure a single instance throughout the application. Here's how I have set up the DI container and configured the services:

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        // SqlCredentialProvider is a singleton to provide consistent SqlCredential instances
        services.AddSingleton<SqlCredentialProvider>();
        
        // SqlConnection is registered as transient and constructed with SqlCredential
        services.AddTransient<SqlConnection>(serviceProvider =>
        {
            var sqlCredentialProvider = serviceProvider.GetRequiredService<SqlCredentialProvider>();
            var sqlCredential = sqlCredentialProvider.GetCredential();
            return new SqlConnection("ConnectionString", sqlCredential);
        });

        // ApplicationDbContext is configured to use the injected SqlConnection
        services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
        {
            var sqlConnection = serviceProvider.GetRequiredService<SqlConnection>();
            options.UseSqlServer(sqlConnection);
        });
    }
}

Given this configuration:

1. Is this the correct approach to ensure that SqlConnection instances, built with SqlCredential from a singleton provider, are properly managed and disposed of by EF Core?
2. Are there better practices or patterns to manage SqlConnection with SqlCredential in an ASP.NET Core application using Dependency Injection? I am particularly concerned about the correct disposal of SqlConnection to prevent any potential resource leaks. Any advice or guidance would be greatly appreciated.

Here's the original code:

    services.AddScoped<SqlCredential>(_ =>
    {
        //ToSecureString is an extension method for converting a string to a SecureString.
        return new SqlCredential("sa", "password".ToSecureString());
    });

    services.AddScoped(serviceProvider =>
    {
        var sqlCredentialProvider = serviceProvider.GetRequiredService<SqlCredentialProvider>();
        var sqlCredential = sqlCredentialProvider.GetCredential();
        return new SqlConnection(configuration.GetConnectionString("MyApplicationConnectionStr"), sqlCredential);
    });
    // ApplicationDbContext is configured to use the injected SqlConnection
    services.AddDbContext<ApplicationDbContext>((serviceProvider, options) =>
    {
         var sqlConnection = serviceProvider.GetRequiredService<SqlConnection>();
         options.UseSqlServer(sqlConnection);
    });

During concurrency testing, I noticed that the user connections on the database were exceeding ten thousand, leading me to suspect that SqlConnection instances were not disposed properly. I then modified the code to the above and now it seems to work fine

Answer 1

Correct, it needs to be a Scoped service, otherwise you get a new object every time you call it, and none of them will get disposed until the end of the scope. Whereas Scoped means you only get one connection. Be aware that this means that connections will be shared if you ever create another DbContext (without using DI) in the same scope.

However, ideally, you would just change the actual connection string to contain the correct credentials, rather than creating the connection yourself.

If you really want to change the way the connection is created, another option is to replace the ISqlServerConnection service.

services.Replace(ServiceDescriptor.Scoped<ISqlServerConnection, SqlServerConnectionWithCreds>())

public class SqlServerConnectionWithCreds : SqlServerConnection
{
    public SqlCredentialProvider CredentialProvider { get; }

    public SqlServerConnectionWithCreds(RelationalConnectionDependencies dependencies, SqlCredentialProvider credentialProvider)
        : base(dependencies)
    {
        CredentialProvider = credentialProvider;
    }

    protected override DbConnection CreateDbConnection()
    {
        var connection = (SqlConnection)base.CreateDbConnection();
        var sqlCredential = CredentialProvider.GetCredential();
        connection.Credential = sqlCredential;
        return connection;
    }

    // copy the code from base adding creds
    public override ISqlServerConnection CreateMasterConnection()
    {
        var connectionStringBuilder = new SqlConnectionStringBuilder(GetValidatedConnectionString()) { InitialCatalog = "master" };
        connectionStringBuilder.Remove("AttachDBFilename");

        var contextOptions = new DbContextOptionsBuilder()
            .UseSqlServer(
                connectionStringBuilder.ConnectionString,
                b => b.CommandTimeout(CommandTimeout ?? DefaultMasterConnectionCommandTimeout))
            .Options;

        return new SqlServerConnectionWithCreds(Dependencies with { ContextOptions = contextOptions }, CredentialProvider);
    }
}