database independence in Entity Framework Core

Question

I have a simple WPF/EF Core 2.2.4 application that uses postgres.
I'm analyzing the possible strategies to migrate it on SQL Server.

In non ORM applications, it's quite common to limit the database-specific reference to the connection string togheter with providing a way to dynamically load the database driver (thinking of JDBC model). In that model you have to address the problem of writing SQL that works cross databases.

Here the problem of SQL writing, actually the major one, is solved right from the start. So I find it quite paradoxical that we sort of reintroduce database dependency in the form of helper methods.

My first question is about the DbContext. The OnConfiguring receives a DbContextOptionsBuilder that is used to pass Connection string. But in order to pass the connection string you use a database-specific method that is provided as an extension method by the database provider. That is the optionsBuilder.UseNpgsql(connstr) in the following example. How should I address this in a database-independend application?

class MyDbContext: DbContext
{

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

    string connstr = 
            ConfigurationManager
            .ConnectionStrings["MYAPP_PROD"].ConnectionString;

    optionsBuilder.UseNpgsql(connstr);

    }

}

The second question is: how can I load the entire database package in a dynamic way, so that I can manage to configure it instead of harcoding it? Actually I use NuGet to get the package:

Npgsql.EntityFrameworkCore.PostgreSQL

Say that I want to use:

Microsoft.EntityFrameworkCore.SqlServer

How can this be done?

Answer 1

Use the strategy pattern to register the relevant database provider based on external configuration.

interface IDbProvider {
    bool AppliesTo(string providerName);
    DbContextOptions<T> LoadProvider<T>();
}

public class PostgresSqlProvider : IDbProvider {

    public bool AppliesTo(string providerName) {
        return providerName.Equals("Postgres");
    }

    public DbContextOptions<T> LoadProvider<T>() {
        //load provider.
    }
}

var providers = new [] {
    new PostgresSqlProvider()
};
var selectedDbProvider = ""; //Load from user input / config

var selectedProvider = providers.SingleOrDefault(x => x.AppliesTo(selectedDbProvider));
if(selectedProvider == null) {
    throw new NotSupportedException($"Database provider {selectedDbProvider} is not supported.");
}

var options = selectedProvider.LoadProvider<DbContext>();

Answer 2

This scenario is already covered by EF Core. Configuring providers should be done in Startup.ConfigureServices, using any of the AddDbContext methods that accept a builder action.

In the simplest case (dirtiest?), you can select providers based on a flag or a value that comes from the config system itself eg :

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();

    var connString=Configuration.GetConnectionString("SchoolContext");  
    var useSqlServer=Configuration.GetSection("MyDbConfig").GetValue<bool>("UseSqlServer");
    services.AddDbContext<SchoolContext>(options =>{
        if (useSqlServer)
        {
            options.UseSqlServer(connString);
        }
        else 
        {
            options.UseNpgsql(connString);
        }
    });
}

or

var provider=Configuration.GetSection("MyDbConfig").GetValue<ProviderEnum>("Provider");
services.AddDbContext<SchoolContext>(options =>{
    switch (provider)
    {
        case ProviderEnum.SqlServer:
             options.UseSqlServer(connString);
             break;
        case ProviderEnum.Postgres :
             options.UseNpgsql(connString);
             break;
        ...
    }
});

That flag can come from configuration as well, eg from the command-line, environment variables, etc.

Refactoring to .... lots

Extension method

This code can be extracted to an extension method on IServiceCollection, similar to other contexts, eg:

public static ConfigureContexts(this IServiceCollection services,string connString, string provider)
{
    services.AddDbContext<SchoolContext>(options =>{
        switch (provider)
        {
            case ProviderEnum.SqlServer:
                 options.UseSqlServer(connString);
                 break;
            case ProviderEnum.Postgres :
                 options.UseNpgsql(connString);
                 break;
            ...
        }
    });
}

and used :

var connString=Configuration.GetConnectionString("SchoolContext");  
var provider=Configuration.GetSection("MyDbConfig").GetValue<ProviderEnum>("Provider");
services.ConfigureContexts(provider,connString);

Builder picker

The builder, configuration patterns allow many variations that can handle complex scenarios. For example, we can pick a builder method in advance :

var efBuilder= SelectBuilder(provider,connString);
services.AddDbContext<SchoolContext>(efBuilder);

...

Action<DbContextOptionsBuilder> SelectBuilder(ProviderEnum provider,string connString)
{
    switch (provider)
    {
        case ProviderEnum.SqlServer:
           return ConfigureSql;
        case ProviderEnum.Postgres :
           return ConfigurePostgres;
    }

    void ConfigureSqlServer(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(connString);
    }

    void ConfigurePostgres(DbContextOptionsBuilder options)
    {
        options.UseNpgSql(connString);
    }
}

In C# 8 this could be reduced to:

Action<DbContextOptionsBuilder> SelectBuilder(ProviderEnum provider,string connString)
{
    return provider switch (provider) {
        ProviderEnum.SqlServer => ConfigureSql,
        ProviderEnum.Postgres => ConfigurePostgres
    };

    void ConfigureSqlServer(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(connString);
    }

    void ConfigurePostgres(DbContextOptionsBuilder options)
    {
        options.UseNpgSql(connString);
    }
}

Concrete config class

Another possibility is to create a strongly-typed configuration class and have it provide the builder :

class MyDbConfig
{
    public ProviderEnum Provider {get;set;}
    ....
    public Action<DbContextOptionsBuilder> SelectBuilder(string connString)
    {
        return provider switch (provider) {
            ProviderEnum.SqlServer => ConfigureSql,
            ProviderEnum.Postgres => ConfigurePostgres
        };

        void ConfigureSqlServer(DbContextOptionsBuilder options)
        {
            options.UseSqlServer(connString);
        }

        void ConfigurePostgres(DbContextOptionsBuilder options)
        {
            options.UseNpgSql(connString);
        }
    }

}

and use it :

var dbConfig=Configuration.Get<MyDbConfig>("MyDbConfig");
var efBuilder=dbCongig.SelectBuilder(connString);
services.AddDbContext<SchoolContext>(efBuilder);