DEV Community

Mohsen Esmailpour
Mohsen Esmailpour

Posted on • Edited on

EF Core multiple Database providers

#entityframework #efcore #dotnet #aspnetcore

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.

Top comments (12)

Collapse
 
lmcgs profile image
Can Ürek •

I couldn't use this approach with the IdentityDbContext. TContextService and TContextImplementation logic don't work as expected. And I got an error like this;

"An error occurred while accessing the Microsoft.Extensions.Hosting services. Continuing without the application service provider. Error: Some services are not able to be constructed (Error while validating the service descriptor..."

Do you have any idea to fix this situation?

Collapse
 
moesmp profile image
Mohsen Esmailpour • • Edited

I upgraded the sample project in GitHub to .NET 7.0 and it's working fine. Please follow the sample project or share your codes by a git repo.

Collapse
 
lmcgs profile image
Can Ürek •

Thanks for the reply. I handled it somehow. I'll also look at your repo.

Collapse
 
nomada2 profile image
nomada2 •

can you add nosql database like azure cosmos documentdb?

Collapse
 
moesmp profile image
Mohsen Esmailpour •

Sure, it's just like PostgresSQL but it does not need to generate migration at all, and by calling context.Database.EnsureCreated() database will be created. Check out the sample project on github.

Collapse
 
nomada2 profile image
nomada2 •

thanks, i'll check

Collapse
 
magals profile image
magals •

Is there a way to get rid of options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection")); to MsSqlDbContext and pass the connection string when services.AddDbContext(); ?

I ask because if the work with DbContext is transferred to a separate project as a library, then it will not be correct for this library to have a strictly fixed entry to the path to the Database. (Configuration.GetConnectionString("MsSqlConnection"))

Collapse
 
moesmp profile image
Mohsen Esmailpour •

@magals you can inject a model that has a connection string property instead of IConfiguration to DbContext and get ride of hardcoded key name.

public class SqlServerDbContext(ConnectionStringModel model)
{
    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
       options.UseSqlServer(_model.ConnectionString);
    }
}
Enter fullscreen mode Exit fullscreen mode
Collapse
 
mblataric profile image
Mario Blatarić •

Do not use modelBuilder.ApplyConfigurationsFromAssembly() method to load configuration.

Could you please comment why not?

Collapse
 
moesmp profile image
Mohsen Esmailpour •

Because ApplyConfigurationsFromAssembly scans the assembly and loads all configuration but only provider-specific configuration should be added.

Collapse
 
minh_nguynng_be868cea profile image
Minh Nguyễn Đăng •

you can use filter

Collapse
 
hussein_nm profile image
Hussein NM •

Thanks

View full discussion (12 comments)

Read next

moh_moh701 profile image

C# Advanced: Unsubscribing from Events

mohamed Tayel -

moh_moh701 profile image

C# Advanced: Passing Data into Event Handlers

mohamed Tayel -

juarezasjunior profile image

C# Design Pattern: Observer

Juarez Júnior -

skttl profile image

Make your own Umbraco Starter Kit without making a Starter Kit

Søren Kottal -