EF Core multiple Database providers

This content originally appeared on DEV Community and was authored by Mohsen Esmailpour

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"

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 override void OnModelCreating(ModelBuilder modelBuilder)
{
    ApplyConfiguration(modelBuilder, "MsSql.Configurations");
}

protected void ApplyConfiguration(ModelBuilder modelBuilder, string configNamespace)
{
    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 &&
                    t.Namespace.Contains(configNamespace) &&
                    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 PsotgresDbContext:

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.

This content originally appeared on DEV Community and was authored by Mohsen Esmailpour

Print Share Comment Cite Upload Translate Updates

APA

Mohsen Esmailpour | Sciencx (2021-05-11T15:13:04+00:00) EF Core multiple Database providers. Retrieved from https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/

MLA

" » EF Core multiple Database providers." Mohsen Esmailpour | Sciencx - Tuesday May 11, 2021, https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/

HARVARD

Mohsen Esmailpour | Sciencx Tuesday May 11, 2021 » EF Core multiple Database providers., viewed ,<https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/>

VANCOUVER

Mohsen Esmailpour | Sciencx - » EF Core multiple Database providers. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/

CHICAGO

" » EF Core multiple Database providers." Mohsen Esmailpour | Sciencx - Accessed . https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/

IEEE

" » EF Core multiple Database providers." Mohsen Esmailpour | Sciencx [Online]. Available: https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/. [Accessed: ]

rf:citation

» EF Core multiple Database providers | Mohsen Esmailpour | Sciencx | https://www.scien.cx/2021/05/11/ef-core-multiple-database-providers/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.