Entity Framework Core 2.1 - Multiple Providers

Question

What is the right way to work with multiple providers? My Example:

appsettings.json

{
  "ConnectionStrings": {
    "Sqlite": "Data Source=database.db"
  }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
     services.AddDbContext<DatabaseContext>(options =>
                    options.UseSqlite(Configuration.GetConnectionString("Sqlite")));
}

DatabaseContext.cs

public class DatabaseContext : DbContext
{
    public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }
    public DbSet<TestModel> TestModel{ get; set; }
}

A easy way for multiple providers?

Do they represent the same data structure? Then you **don't** need multiple DbContext implementations.They you just need conditionally call `.UseSqlite`/`.UseSqlServer`/etc registrations based on what provider you want to implement, best solved via additional configuration option in appsettings.json — Tseng, Sep 17 '18 at 09:55
I wouldn't add the `Provider` setting to `ConnectionStrings`, since `ConnectionStrings` has a special meaning: its has `configuration.GetConnectionString("MyConnection")` ([docs](https://learn.microsoft.com/en-us/dotnet/api/microsoft.extensions.configuration.configurationextensions.getconnectionstring?view=aspnetcore-2.1)) method as shorthand of `configuration["ConnectionStrings:MyConnection"]` — Tseng, Sep 17 '18 at 09:59
but Add-Migration does not allow me to use only one DbContext? — Seimann, Sep 17 '18 at 10:02
It shouldn't matter. The migrations **shouldn't** contain provider-specific information (like datatypes). i.e. don't use `VAR(255)` or `NVAR(256)` in your migration files, models and configuration. This ways it creates a provider-agnostic migration file and the migration scripts (SQL etc.) are generated when the migration is applied. Other option is to move the (fluent) configuration of the DbContext outside of the context itself. There has been a github issue on that will need to check after lunch — Tseng, Sep 17 '18 at 10:04
i.e. using `IEntityTypeConfiguration` like in http://anthonygiretti.com/2018/01/11/entity-framework-core-2-entity-type-configuration/ But this would require you to mostly duplicate all of your fluent configuration for each provider. I strongly recommend to create your entities and context configuration agnostic of any provider (dont use special types which only exists on a specific database, like ENUM, SET, NVAR, ROWVERSION etc. — Tseng, Sep 17 '18 at 12:22

Seimann · Accepted Answer · 2018-09-20T22:31:10.770

A solution with only one Context (Example for SQLite + MySQL + MSSQL + PostgreSQL (or others)):

appsettings.json

{
  // Add Provider and ConnectionStrings for your EFC drivers
  // Providers: SQLite, MySQL, MSSQL, PostgreSQL, or other provider...
  "Provider": "SQLite",
  "ConnectionStrings": {
    "SQLite": "Data Source=mydatabase.db",
    "MySQL": "server=localhost;port=3306;database=mydatabase;user=root;password=root",
    "MSSQL": "Server=(localdb)\\mssqllocaldb;Database=mydatabase;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PostgreSQL": "Host=localhost;Database=mydatabase;Username=root;Password=root"
  }
}

Single DatabaseContext.cs

public class DatabaseContext : DbContext
{
        public DatabaseContext(DbContextOptions<DatabaseContext> options) : base(options) { }

        // add Models...
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    // Check Provider and get ConnectionString
    if (Configuration["Provider"] == "SQLite")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("SQLite")));
    }
    else if (Configuration["Provider"] == "MySQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseMySql(Configuration.GetConnectionString("MySQL")));
    }
    else if (Configuration["Provider"] == "MSSQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("MSSQL")));
    }
    else if (Configuration["Provider"] == "PostgreSQL")
    {
        services.AddDbContext<DatabaseContext>(options =>
            options.UseNpgsql(Configuration.GetConnectionString("PostgreSQL")));
    }
    // Exception
    else
    { throw new ArgumentException("Not a valid database type"); }
}

Now we can do a singel migration

Add-Migration InitialCreate

Only edit every output of Add-Migration and add driver specific attributes:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
        name: "Mytable",
        columns: table => new
        {
            Id = table.Column<int>(nullable: false)
            // Add for SQLite
            .Annotation("Sqlite:Autoincrement", true)
            // Add for MySQL
            .Annotation("MySql:ValueGenerationStrategy", MySqlValueGenerationStrategy.IdentityColumn)
            // Add for MSSQL
            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
            // Add for PostgreSQL
            .Annotation("Npgsql:ValueGenerationStrategy", NpgsqlValueGenerationStrategy.SerialColumn),
            // Or other provider...
            Name = table.Column<string>(maxLength: 50, nullable: false),
            Text = table.Column<string>(maxLength: 100, nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Mytable", x => x.Id);
        });
    }

EDIT: or you use string ID "DatabaseGenerated" so you would not have to edit migrationBuilder and the add migration is multiple providers capable without ".Annotation"

EXAMPLE Model:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace WebApplication.Models
{
    public class Mytable
    {
        // This generate a String ID
        // No ID modification needed for providers
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public string Id { get; set; }

        // ....
    }
}

Now ready for Update-Database

I don't see how the Annotations are generated for the different providers when using the second example with `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]`. — serious, Jan 21 '19 at 10:19

score 1 · Answer 2 · answered Nov 19 '18 at 21:43

You may want to consider a utility like AdaptiveClient. AdaptiveClient allows you to create a single DbContext with multiple provider-specific implementations of your services (MSSQL, MySQL, SQLite, etc). AdaptiveClient injects the correct implementation based on the connection string in use.

AdaptiveClient also allows you to inject transport-specific service implementations. For example many applications run both locally (same LAN as database server) and remotely (use WCF or REST). When running locally AdaptiveClient will inject an implementation of your service that talks directly to your database. This gives a ~10x performance improvement. When running remotely AdaptiveClient injects a WCF or REST implementation.

See also:

AdaptiveClient.EntityFrameworkCore

Demo Application

AdaptiveClient is available as a nuget package.

Disclaimer: I am the author of AdaptiveClient.

score 1 · Answer 3 · answered Jun 05 '20 at 22:25

Seimann's answer is good but I found working with migrations to be a pain. I wanted little or no manual work to get it working. I found the easiest way was to create a separate assembly for each provider and add an implementation of IDesignTimeDbContextFactory.

Another solution is to create a design time assembly but selecting which provider to use for migrations turned out to be difficult, at least until this feature is implemented here. I tried the suggested method of setting an environment variable before executing the migrations but I found using compiler constants to select the correct provider to be easier.

I organized this by creating a shared project to be used by all providers. Here is an example implementation that pulls your main projects configuration settings. This class will support both methods explained above so it can be simplified depending on your needs.

#if DEBUG
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;
using System;
using System.IO;

namespace Database.DesignTime
{
    public class ApplicationDbContextDesignTimeFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {
        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var configuration = new ConfigurationBuilder()
                 .SetBasePath(Path.GetFullPath(@"..\MainProjectDirectory"))
                 .AddJsonFile("appsettings.json")
                 .AddJsonFile("appsettings.Development.json")
                 .Build();

            // Determine provider from environment variable or use compiler constants below
            var databaseProvider = Environment.GetEnvironmentVariable("DatabaseProvider");

#if SQLSERVER
            databaseProvider = "SqlServer";
#endif
#if POSTGRESQL
            databaseProvider = "PostgreSql";
#endif

            var connectionString = configuration.GetConnectionString($"{databaseProvider}Connection");

            var contextBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();

            switch (databaseProvider)
            {
#if SQLSERVER
                case "SqlServer":
                    contextBuilder.UseSqlServer(connectionString, dbOptions =>
                    {
                        dbOptions.MigrationsAssembly("Database.SqlServer");
                    });
                    break;
#endif

#if POSTGRESQL
                case "PostgreSql":
                    contextBuilder.UseNpgsql(connectionString, dbOptions =>
                    {
                        dbOptions.MigrationsAssembly("Database.PostgreSql");
                    });
                    break;
#endif

                default:
                    throw new NotSupportedException(databaseProvider);
            }

            return new ApplicationDbContext(contextBuilder.Options);
        }
    }
}

#endif

Then in your database migration project add the compiler constant for each provider. For example:

Database.SqlServer.csproj <DefineConstants>SQLSERVER</DefineConstants>

Database.PostgreSql.csproj <DefineConstants>POSTGRESQL</DefineConstants>

When you want to add migrations from within VS, open the Package Manager Console and select the migration project as the Default project. When executing the command, you need to specify the project containing the implementation of IDesignTimeDbContextFactory you want to use.

Add-Migration Initial -StartupProject "Database.SqlServer"

Now you can switch back to your main project and use it as normal. Just for reference this is my relevant appsettings.json and startup code.

{
  "DatabaseProvider": "SqlServer",
  "ConnectionStrings": {
    "SqlServerConnection": "Server=(localdb)\\mssqllocaldb;Database=DatabaseName;Trusted_Connection=True;MultipleActiveResultSets=true",
    "PostgreSqlConnection": "Host=host;Database=DatabaseName;User ID=Test;Password=secrectPass"  
}

            services.AddDbContext<ApplicationDbContext>(options =>
            {
                switch (Configuration["DatabaseProvider"])
                {
                    case "SqlServer":
                        options.UseSqlServer(Configuration.GetConnectionString("SqlServerConnection"), dbOptions =>
                        {
                            dbOptions.MigrationsAssembly("Database.SqlServer");
                        });
                        break;

                    case "PostgreSql":
                        options.UseNpgsql(Configuration.GetConnectionString("PostgreSqlConnection"), dbOptions =>
                        {
                            dbOptions.MigrationsAssembly("Database.PostgreSql");
                        });
                        break;
                }
            });

There is another suggested way to accomplish this as explained here but I found creating derived classes means the migration will only work for instances of the derived class and not the base class. So you would need to specify the derived class type in AddDbContext. The other method mentioned requires manual work which I want to avoid.

Entity Framework Core 2.1 - Multiple Providers

3 Answers3

Linked