.NET EF Core multiple Database providers

Most of the time you don’t need to have multiple database providers, for example, you start with the SQL Server provider and there is no need to switch to another database provider.
In my recent project, we have to add multiple providers to support different deployment environments. Some customers prefer to use Windows Server and SQL Server database and others prefer to use Linux and MySQL or PostgreSQL.

In this walkthrough, I show how to add SQL Server and PostgresSQL providers and configure them. The default provider is SQL Server and the alternative provider is PostgreSQL.

Step 1 – Create a project

• Create ASP.NET Core Web API

Step 2 – Create Default DbContext

• Add Data folder to the root of the project
• Add a WeatherDbContext to Data folder:

public abstract class WeatherDbContext : DbContext
{
    protected readonly IConfiguration Configuration;

    protected WeatherDbContext(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public DbSet<WeatherForecast> WeatherForecasts { get; set; }
}

MsSqlDbContext is our default DbContext and we use this default DbContext every in code. MsSqlDbContext contains DbSets and shared functionalities that we want use among all DbContext classes.

Step 3 – Create SQL Server DbContext

Add a MsSqlDbContext to Data folder:

public class MsSqlDbContext : WeatherDbContext
{
    public MsSqlDbContext(IConfiguration configuration) : base(configuration)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection"));
    }
}

Instead of configuring SQL Server provider in ConfigureService method in Startup class:

services.AddDbContext<YourDbContext>(options => { options.UseSqlServer(Configuration.GetConnectionString("SomeConnection"); })

I configure provider in OnConfiguring method of MsSqlDbContext:

protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection"));
}

Step 4 – Create PosgreSQL DbContext

• Add a PostgresDbContext class to Data folder:

public class PostgresDbContext : MsSqlDbContext
{
    public PostgresDbContext(IConfiguration configuration)
        : base(configuration)
    {
    }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        options.UseNpgsql(Configuration.GetConnectionString("PostgreSqlConnection"));
    }
}

Step 5 – Registring DbContexts

So far we have two DbContexts but we want to choose a preferred database provider in the configuration file appsettings.json.

• Add following key/valu to appsettings.json:

"DatabaseProvider": "MsSql"

Register database provider in ConfigureServices based on the selected provider:

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

    switch (Configuration["DatabaseProvider"])
    {
        case "MsSql":
            services.AddDbContext<WeatherDbContext,MsSqlDbContext>();
            break;

        case "PostgreSql":
            services.AddDbContext<WeatherDbContext,PostgresDbContext>();
            break;
    }
}

Step 6 – Migrations

Let’s generate a migration for SQL Server database.

• Add Migrations\MsSql folders to Data folder
• In appsettings.json file set “MsSql” value for “DatabaseProvider” key
• Run the following command for generating migration SQL Server database:

Add-Migration InitialDbMsSql -Context MsSqlDbContext -OutputDir Data\Migrations\MsSql

Let’s generate a migration for PostgreSQL database.

• Add Migrations\PostgreSql folders to Data folder
• In appsettings.json file set “PostgreSql” value for “DatabaseProvider” key
• Run the following command for generating migration SQL Server database:

Add-Migration InitialDbPostgres -Context PostgresDbContext -OutputDir Data\Migrations\PostgreSql

Step 4 – Entity Configuration

Here is the WeatherForecast class:

public class WeatherForecast
{
    public int Id { get; set; }

    public DateTime Date { get; set; }

    public int TemperatureC { get; set; }

    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

    public string Summary { get; set; }
}

Consider the following configuration:

internal class WeatherForecastConfiguration : IEntityTypeConfiguration<WeatherForecast>
{
    public void Configure(EntityTypeBuilder<WeatherForecast> builder)
    {
        builder.Property(wf => wf.TemperatureC).HasColumnType("tinyint");
        builder.Property(wf => wf.Date).HasColumnType("datetime");
        builder.Property(wf => wf.Summary).HasColumnType("nvarchar(512)").IsRequired(false);
        builder.Ignore(wf => wf.TemperatureF);
    }
}

In the above configuration, I used tinyint and nvarchar are supported in SQL Server but not supported in PostgreSQL. In such a case you can move the configuration to OnModelCreating method DbContext:

public class MsSqlDbContext : DbContext
{
    ...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.TemperatureC).HasColumnType("tinyint");
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.Date).HasColumnType("datetime");
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.Summary).HasColumnType("nvarchar(512)").IsRequired(false);
    }
}

And for PostgresDbContext:

public class PostgresDbContext : MsSqlDbContext
{
    ...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.TemperatureC).HasColumnType("samllint");
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.Date).HasColumnType("timestamp(3)");
        modelBuilder.Entity<WeatherForecast>().Property(wf => wf.Summary).HasColumnType("varchar(512)").IsRequired(false);
    }
}

I prefer to move specific configuration from OnModelCreating to configuration classes and makeOnModelCreating method clean with help of reflection but you can keep it as it is.
To move configurations:

• Add Configurations folder to Data folder for common configuration of database providers
• Add WeatherForecastSharedConfiguration class to Configurations folder:

internal class WeatherForecastSharedConfiguration : IEntityTypeConfiguration<WeatherForecast>
{
    public void Configure(EntityTypeBuilder<WeatherForecast> builder)
    {
        builder.Ignore(wf => wf.TemperatureF);
    }
}

• Add MsSql folder to Configurations folder
• Add WeatherForecastMsSqlConfiguration class to MsSql folder:

internal class WeatherForecastMsSqlConfiguration : IEntityTypeConfiguration<WeatherForecast>
{
    public void Configure(EntityTypeBuilder<WeatherForecast> builder)
    {
        builder.Property(wf => wf.TemperatureC).HasColumnType("tinyint");
        builder.Property(wf => wf.Date).HasColumnType("datetime");
        builder.Property(wf => wf.Summary).HasColumnType("nvarchar(512)").IsRequired(false);
    }
}

• Add PostgreSql folder to Configurations folder
• Add WeatherForecastMsSqlConfiguration class to PostgreSql folder:

internal class WeatherForecastPostgresConfiguration : IEntityTypeConfiguration<WeatherForecast>
{
    public void Configure(EntityTypeBuilder<WeatherForecast> builder)
    {
        builder.Property(wf => wf.TemperatureC).HasColumnType("samllint");
        builder.Property(wf => wf.Date).HasColumnType("timestamp(3)");
        builder.Property(wf => wf.Summary).HasColumnType("varchar(512)").IsRequired(false);
    }
}

Add the following codes to WeatherDbContext (default DbContext) to load shared and provider-specific configuration:

protected void ApplyConfiguration(ModelBuilder modelBuilder, string[] namespaces)
{
    var methodInfo = (typeof(ModelBuilder).GetMethods()).Single((e =>
        e.Name == "ApplyConfiguration" &&
        e.ContainsGenericParameters &&
        e.GetParameters().SingleOrDefault()?.ParameterType.GetGenericTypeDefinition() == typeof(IEntityTypeConfiguration<>)));

    foreach (var configType in typeof(MsSqlDbContext).GetTypeInfo().Assembly.GetTypes()

        .Where(t => t.Namespace != null &&
                    namespaces.Any(n => n == t.Namespace) &&
                    t.GetInterfaces().Any(i => i.IsGenericType &&
                                               i.GetGenericTypeDefinition() == typeof(IEntityTypeConfiguration<>))))
    {
        var type = configType.GetInterfaces().First();
        methodInfo.MakeGenericMethod(type.GenericTypeArguments[0]).Invoke(modelBuilder, new[]
        {
            Activator.CreateInstance(configType)
        });
    }
}

In OnModelCreating method of MsSqlDbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var namespaces = new[] { "EfMultipleProviders.Data.Configurations",
        "EfMultipleProviders.Data.Configurations.MsSql" };
    ApplyConfiguration(modelBuilder, namespaces);
}

Load shared configuration from EfMultipleProviders.Data.Configurations namespace and SQL Server configuration from EfMultipleProviders.Data.Configurations.MsSql namespace.

• And for PostgresDbContext:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var namespaces = new[] { "EfMultipleProviders.Data.Configurations",
        "EfMultipleProviders.Data.Configurations.PostgreSql" };
    ApplyConfiguration(modelBuilder, namespaces);
}

• Do not use modelBuilder.ApplyConfigurationsFromAssembly() method to load configuration.
• Inject WeatherDbContext into your repositories, services or controller not MsMsqlDbContext or PostgresSqlDbContext

You can find the source code for this walkthrough on Github.

Source : https://dev.to/moesmp/ef-core-multiple-database-providers-3gb7