EF Core multiple Database providers
Tags: entityframework, efcore, dotnet, aspnetcore
Published: 2021-05-11 15:13:04

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:
```csharp
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:
```csharp
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:
```csharp
services.AddDbContext<YourDbContext>(options => { options.UseSqlServer(Configuration.GetConnectionString("SomeConnection"); })
```

I configure provider in `OnConfiguring` method of `MsSqlDbContext`:
```csharp
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
    options.UseSqlServer(Configuration.GetConnectionString("MsSqlConnection"));
}
```

####Step 4 - Create PosgreSQL DbContext
- Add a `PostgresDbContext` class to `Data` folder:
```csharp
public class PostgresDbContext : WeatherDbContext
{
    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`:
```json
"DatabaseProvider": "MsSql"
```
- Register database provider in `ConfigureServices` based on the selected provider:
```csharp
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:
```powershell
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:
```powershell
Add-Migration InitialDbPostgres -Context PostgresDbContext -OutputDir Data\Migrations\PostgreSql
```

####Step 4 - Entity Configuration
Here is the `WeatherForecast` class:
```csharp
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:
```csharp
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:
```csharp
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:

```csharp
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);
    }
}
```

I prefer to move specific configuration from `OnModelCreating` to configuration classes and make`OnModelCreating` 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:

```csharp
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:

```csharp
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:
```csharp
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:
```csharp
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`:
```csharp
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`:
```csharp
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](https://github.com/mo-esmp/ef-multiple-providers).