Practical multiple providers in Entity Framework Core

I’ve been developing an ASP.Net Core backend. Naturally, Entity Framework (EF) Core is used as the object-database mapper.

One cool thing about EF is that it supports many database providers: sqlite, postgres, mssql, etc. Therefore, I decided to allow configuration-based provider selection. The site administrator can choose a preferred db provider in configuration:

1
2
3
4
{
"DbProvider": "sqlite",
"DbConnString": "DataSource=/tmp/db.sqlite",
}

Unfortunately, this is one of a few things that are, albeit documented, quite poorly described in MSDN. After spending an hour to figure out everything not mentioned in the docs, I came up with my own pattern for this, and this post is to keep a reference for myself. Hopefully, others can also benefit from this.

The usual scenerio

Usually, you have a DbContext subclass, class MyDb : DbContext. Then, it should have a constructor:

1
2
3
4
public partial class MyDb : DbContext {
public MyDb(DbContextOptions<MyDb> options) : base(options) {
}
}

And you call AddDbContext in Startup.ConfigureServices:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
// Configure database
switch (config.DbProvider.ToLower()) {
case "sqlite": {
services.AddDbContext<MyDb>(options => { options.UseSqlite(config.DbConnString); });
}
break;
case "postgres": {
services.AddDbContext<MyDb>(options => options.UseNpgsql(config.DbConnString));
}
break;
default: {
throw new Exception($"DbProvider not recognized: {config.DbProvider}");
}
}

If you don’t use EF migrations, you’re done. This will work perfectly fine, as long as you configure the database and create it in Startup.Configure:

1
2
3
using (var db = services.GetService<MyDb>()) {
db.Database.EnsureCreated();
}

However, for migrations to work, you need more than what the documentation says.

Subclassing DbContext

The documentation says that you should subclass MyDb, and override its OnConfiguring method to use the desired provider. If you do follow it, your code won’t compile: you’ll need an additional default constructor for your MyDb class.

Another missing part in the document is that you’ll need to use your subclasses in service configuration. Therefore, they need constructors expecting DbContextOptions<T>, an appropriate constructor should be added to MyDb as well.

Finally, for the similar reason, your OnConfiguring method must exist conditionally. In particular, it must be there only when the binary is built for EF designer tool. Otherwise, it overwrites any configuration you have in normal builds. Therefore, you have to put the OnConfiguring method in an #if block, and create a project configuration (I called mine DebugEf) that defines the flag DEBUG_EF.

In summary, to support sqlite and npgsql, you need two auxiliary classes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
public class SqliteMyDb : MyDb {
public SqliteMyDb() {}
public SqliteMyDb(DbContextOptions<SqliteMyDb> options) : base(options) {
}
#if DEBUG_EF
protected override void OnConfiguring(DbContextOptionsBuilder options) {
options.UseSqlite("DataSource=");
}
#endif
}

public class NpgsqlMyDb : MyDb {
public NpgsqlMyDb() {}
public NpgsqlMyDb(DbContextOptions<NpgsqlMyDb> options) : base(options) {
}
#if DEBUG_EF
protected override void OnConfiguring(DbContextOptionsBuilder options) {
options.UseNpgsql("DataSource=");
}
#endif
}

And the MyDb class need two extra constructors:

1
2
3
4
5
6
7
public partial class MyDb : DbContext {
public MyDb() {}
public MyDb(DbContextOptions<MyDb> options) : base(options) {
}
protected MyDb(DbContextOptions options) : base(options) {
}
}

Creating migrations

Simply create a migration for each provider. Notice --configuration.

1
2
dotnet ef migrations add InitialCreate --context SqliteMyDb --output-dir Migrations/Sqlite --configuration DebugEf
dotnet ef migrations add InitialCreate --context NpgsqlMyDb --output-dir Migrations/Npgsql --configuration DebugEf

When adding more migrations in the future, --output-dir isn’t required, but --configuration shall be kept.

Adding services

In your application, you should continue to use MyDb. However, for EF to determine which migration to use, you have to call services.GetService<T> where T is one of the subclasses (instead of MyDb).

Therefore, in Startup.ConfigureServices, register the subclasses:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
// Configure database
switch (config.DbProvider.ToLower()) {
case "sqlite": {
services.AddDbContext<MyDb>(options => { options.UseSqlite(config.DbConnString); });
services.AddDbContext<SqliteMyDb>(options => { options.UseSqlite(config.DbConnString); });
}
break;
case "postgres": {
services.AddDbContext<MyDb>(options => options.UseNpgsql(config.DbConnString));
services.AddDbContext<NpgsqlMyDb>(options => options.UseNpgsql(config.DbConnString));
}
break;
default: {
throw new Exception($"DbProvider not recognized: {config.DbProvider}");
}
}

Then, in Startup.Configure, do the migrations:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
void ProcessDb<T>() where T : MyDb {
using var db = services.GetService<T>();
db.Database.Migrate();

// ... perform other startup tasks with db
}

switch (config.DbProvider.ToLower()) {
case "sqlite": {
ProcessDb<SqliteMyDb>();
break;
}
case "postgres": {
ProcessDb<NpgsqlMyDb>();
break;
}
default: {
throw new Exception();
}
}

In summary, for each extra provider, add one subclass, a two-line case in ConfigureServices, and a case in Configure.