EF Core Multiple migration sets

Question

I'm currently working on a .Net Core app using EF Core Code First Migrations. I'm jumping between Windows and OS X to do my development (Windows at home, Mac on the go) and I'm trying to figure out how I can use database migrations for SQL Server when I am on Windows and SQLite when on my Mac.

Is it possible to maintain two sets of migrations and select which set to apply (without needing to specify each migration individually)? Or would I need to create separate .Net Core applications/assemblies and specify the --assembly option for the dotnet ef database update command?

Answer 1

You have two options:

1. Use two sets of Migrations
2. Manually edit the migrations to be provider-agnostic

Multiple sets

Each set needs to be in its own assembly. To get started, generate the first migration and move it into a new project. You would configure the migrations assembly when you configured the provider.

optionsBuilder.UseSqlServer(
    mssqlConnectionString
    , x => x.MigrationsAssembly("MyApp.Migrations.SqlServer"));
//optionsBuilder.UseSqlite(
//    sqliteConnectionString,
//    x => x.MigrationsAssembly("MyApp.Migrations.Sqlite"));

I have a sample that puts migrations in their own assembly that you might also want to use as a reference.

Provider-agnostic

To make the migrations provider-agnostic, merge what each provider would generate. For example, the following has annotations for both SQL Server and SQLite.

migrationBuilder.CreateTable(
    name: "People",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("Sqlite:Autoincrement", true)
            .Annotation("SqlServer:ValueGenerationStrategy",
                SqlServerValueGenerationStrategy.IdentityColumn)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_People", x => x.Id);
    });