TheLazy.Dev
Portfolio GitHub

Blog

entityframework |efcore |dotnet |aspnetcore

EF Core multiple Database providers

moesmp

Mohsen Esmailpour

Posted on May 11, 2021

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; }
}
Enter fullscreen mode Exit fullscreen mode

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"));
    }
}
Enter fullscreen mode Exit fullscreen mode
  • Instead of configuring SQL Server provider in ConfigureService method in Startup class: 
services.AddDbContext<YourDbContext>(options => { options.UseSqlServer(Configuration.GetConnectionString("SomeConnection"); })
Enter fullscreen mode Exit fullscreen mode

I configure provider in OnConfiguring method of MsSqlDbContext:

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

Step 4 - Create PosgreSQL DbContext

  • Add a PostgresDbContext class to Data folder: 
public class PostgresDbContext : WeatherDbContext
{
    public PostgresDbContext(IConfiguration configuration)
        : base(configuration)
    {
    }

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

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"
Enter fullscreen mode Exit fullscreen mode
  • 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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

And for PostgresDbContext:

public class PostgresDbContext : WeatherDbContext
{
    ...
    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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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);
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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);
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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)
        });
    }
}
Enter fullscreen mode Exit fullscreen mode
  • In OnModelCreating method of MsSqlDbContext: 
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var namespaces = new[] { "EfMultipleProviders.Data.Configurations",
        "EfMultipleProviders.Data.Configurations.MsSql" };
    ApplyConfiguration(modelBuilder, namespaces);
}
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode
  • 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.

💖 💪 🙅 🚩
moesmp
Mohsen Esmailpour

Posted on May 11, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

EF Core multiple Database providers
entityframework EF Core multiple Database providers

May 11, 2021