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
toData
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
toData
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 inStartup
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 toData
folder:
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
:
"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 toData
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 toData
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 : 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 makeOnModelCreating
method clean with help of reflection but you can keep it as it is.
To move configurations:
- Add
Configurations
folder toData
folder for common configuration of database providers - Add
WeatherForecastSharedConfiguration
class toConfigurations
folder:
internal class WeatherForecastSharedConfiguration : IEntityTypeConfiguration<WeatherForecast>
{
public void Configure(EntityTypeBuilder<WeatherForecast> builder)
{
builder.Ignore(wf => wf.TemperatureF);
}
}
- Add
MsSql
folder toConfigurations
folder - Add
WeatherForecastMsSqlConfiguration
class toMsSql
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 toConfigurations
folder - Add
WeatherForecastMsSqlConfiguration
class toPostgreSql
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 ofMsSqlDbContext
:
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
orPostgresSqlDbContext
You can find the source code for this walkthrough on Github.
Top comments (12)
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?
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.
Thanks for the reply. I handled it somehow. I'll also look at your repo.
can you add nosql database like azure cosmos documentdb?
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.thanks, i'll check
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"))
@magals you can inject a model that has a connection string property instead of
IConfiguration
to DbContext and get ride of hardcoded key name.Could you please comment why not?
Because
ApplyConfigurationsFromAssembly
scans the assembly and loads all configuration but only provider-specific configuration should be added.you can use filter
Thanks