Running Update-Database in a database with multiple EF managed schemas

Question

Imagine I have a database, in that database there is a schema, called MyGreatApp1.

MyGreatApp1 was created with EF Core using code-first migrations. I use a connection string with a 'SearchPath' defined as 'MyGreatApp1', this means it runs all its queries in that schema.

I then create another app, called MyGreatApp2, also using EF Core code-first. I use a connection string with a 'SearchPath' of 'MyGreatApp2'. However, when running the Update-Database command (or dotnet CLI equivalent), it fails with the error

Failed executing DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "MigrationId", "ProductVersion" FROM "__EFMigrationsHistory" ORDER BY "MigrationId";

It's trying to select from the Migrations table in the schema MyGreatApp2, but that doesn't exist yet. EF is meant to create it, but it hasn't.

Further up in the output I see the following SQL executed

SELECT EXISTS (SELECT 1 
               FROM pg_catalog.pg_class c 
               JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
               WHERE c.relname = '__EFMigrationsHistory');

You can see that this is just asking the pg_catalog if a table called __EFMigrationsHistory exists, which it does, but in the other schema. So it doesn't bother creating the MigrationsHistory table, and the Update-Database command fails.

If I manually create the __EFMigrationsHistory table, then run Update-Database, all is well.

In this (admittedly slightly convoluted) scenario, is there a better way of handling this?

Answer 1

Generally you should redefine and explicitly set schema/name for History Table, so it is recognized correctly (this is common strategy for use cases like yours):

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options.UseSqlServer(
        _connectionString,
        x => x.MigrationsHistoryTable("__MyMigrationsHistory", "mySchema"));

https://learn.microsoft.com/en-us/ef/core/managing-schemas/migrations/history-table

Here you can find HistoryRepository for NpgSQL: https://github.com/npgsql/efcore.pg/blob/main/src/EFCore.PG/Migrations/Internal/NpgsqlHistoryRepository.cs

and if that code doesn't work using MSDN link above just redefine it:

Basic snip of history class:

internal class MyHistoryRepository : SqlServerHistoryRepository
{
    public MyHistoryRepository(HistoryRepositoryDependencies dependencies)
        : base(dependencies)
    {
    }

    protected override void ConfigureTable(EntityTypeBuilder<HistoryRow> history)
    {
        base.ConfigureTable(history);

        history.Property(h => h.MigrationId).HasColumnName("Id");
    }
}

Registration:

protected override void OnConfiguring(DbContextOptionsBuilder options)
    => options
        .UseSqlServer(_connectionString)
        .ReplaceService<IHistoryRepository, MyHistoryRepository>();