The default schema does not exist EF Core Migration

Question

I'm using .Net Core 6 with EF Core (Code First) and Azure SQL. When i run the add-migration command it generates the file and when I run the update-database command I get the below error.

Also the update-database command works when I have my connection string to local DB. The issue comes only when connection string is changed to Azure SQL and that connection string is got from Azure KeyVault.

Tried,

In DbContext:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
     var relationalOptions = RelationalOptionsExtension.Extract(optionsBuilder.Options);
     relationalOptions.WithMigrationsHistoryTableName("EFMigrationHistory");
     relationalOptions.WithMigrationsHistoryTableSchema("dbo");
 }

In Program.cs:

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<NotificationDbContext>();
    db.Database.Migrate();
}

Same error again. Can someone help?

Answer 1

I faced a similar problem and solved using a different approach between design time and runtime as suggested in Design-time DbContext Creation (MS learn).

To work at design time, I defined a class derived from IDesignTimeDbContextFactory. This class is automatically detected by update-database or dotnet CLI when you perform a migration.

public class MyDbContextFactory : IDesignTimeDbContextFactory<MyDbContext> {
    public MyDbContext CreateDbContext(string[] args) {
        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
        optionsBuilder.UseSqlServer("... connection string here ..."
            , options => {
                options.MigrationsHistoryTable("EFMigrationHistory", "dbo");
            });

        return new MyDbContext(optionsBuilder.Options); 
    }
}

To work at run time, e.g. if you want to perform database update at the startup, the previous class is not used. To set the migration history table at run time I set the migration history using sql sqlServerOptionsAction parameter in UseSqlServer method:

builder.Services.AddDbContext<MyDbContext>(options => {
    options.UseSqlServer(builder.Configuration.GetConnectionString("dbConnection"),
        sqlServerOptionsAction: sqlOptions => {
            sqlOptions.MigrationsHistoryTable("EFMigrationHistory", "dbo");
            sqlOptions.MigrationsAssembly(typeof(MyDbContext).Assembly.GetName().Name);
            sqlOptions.EnableRetryOnFailure(maxRetryCount: 15, maxRetryDelay: TimeSpan.FromSeconds(30), errorNumbersToAdd: null);
        });
}, ServiceLifetime.Scoped);