[API Proposal] SqlServerCache: exposing sql connection factory

[ricsiLT]

Background and motivation

In containerised environments running without a Kerberos sidecar, Microsoft.Data.SqlClient requires SspiContextProvider to be set on the
SqlConnection instance before it is opened. SqlServerCache currently creates connections internally via new SqlConnection(connectionString) with no extension point to intercept or configure that instance.

This is a general issue with current configuration options, as other SqlConnection properties (for example RetryLogicProvider, Disposed event handler) are also not exposed.

This causes issues (or a need to reimplement whole SqlServerCache) if you want to control those things.

API Proposal

public static IServiceCollection AddDistributedSqlServerCache(
    this IServiceCollection services, 
    Action<SqlServerCacheOptions> setupAction,
    Func<IServiceProvider, SqlConnection> connectionFactory) 
{
    / * */
}

API Usage

services.AddDistributedSqlServerCache(
    options =>
    {
        // needs to either throw, or have written order of resolution of both are set.
        // to prevent breaking changes, if this is not empty - sqlConnectionFactory is ignored.
        options.ConnectionString = "";
        options.TableName  = "MyDistributedCache";
        options.SchemaName = "dbo";
    },
    sp =>
    {
        var cfg  = sp.GetRequiredService<IOptions<MyDbConfig>>().Value;
        var conn = new SqlConnection(cfg.ConnectionString);
        conn.SspiContextProvider = sp.GetRequiredService<ISspiContextProvider>();
        conn.RetryLogicProvider  = SqlConfigurableRetryFactory.CreateExponentialRetryProvider(...);
        conn.Disposed += (_, _) => /* cleanup */;
        return conn;
    });

Alternative Designs

Alternatively, you could update SqlServerCacheOptions to have all of the properties that are contained on SqlConnection. That does not seem desirable though.

Risks

If design without breaking changes is selected - none?