Calling Migrate on a database containing tables but no history throws

[ajcvickers]

This repro was extracted from this comment by @PureIso.

This is a case where there is a regression in behavior between EF8 and EF9, but there are likely to be other root causes for the exception. I was not able to find a case where Up/Down are empty using this repro, although a better understanding of the root cause may reveal this.

Consider code that creates tables in the DbContext constructor:

    public ContextOfDoom()
    {
        if (Database.GetService<IDatabaseCreator>() is not RelationalDatabaseCreator dbCreate) return;
        // Create Database 
        if (!dbCreate.CanConnect())
        {
            dbCreate.Create();
        }
        
        // Create Tables
        if (!dbCreate.HasTables())
        {
            dbCreate.CreateTables();
        }
    }

This creates any tables that don't exist, but does not create the migrations history table:

>>>>>>>>>>>>>>>>>>>> Starting tables

warn: 11/22/2024 10:59:37.598 CoreEventId.SensitiveDataLoggingEnabledWarning[10400] (Microsoft.EntityFrameworkCore.Infrastructure) 
      Sensitive data logging is enabled. Log entries and exception messages may include sensitive application data; this mode should only be enabled during development.
info: 11/22/2024 10:59:37.875 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 11/22/2024 10:59:37.893 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      
      IF EXISTS
          (SELECT *
           FROM [sys].[objects] o
           WHERE [o].[type] = 'U'
           AND [o].[is_ms_shipped] = 0
           AND NOT EXISTS (SELECT *
               FROM [sys].[extended_properties] AS [ep]
               WHERE [ep].[major_id] = [o].[object_id]
                   AND [ep].[minor_id] = 0
                   AND [ep].[class] = 1
                   AND [ep].[name] = N'microsoft_database_tools_support'
          )
      )
      SELECT 1 ELSE SELECT 0

>>>>>>>>>>>>>>>>>>>> Done tables

Then, when the application starts, it calls Database.Migrate:

using (var context = new ContextOfDoom())
{
    context.Database.Migrate();
}

In EF8, this creates the migration history table:

>>>>>>>>>>>>>>>>>>>> Starting migrate

info: 11/22/2024 11:01:49.741 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 11/22/2024 11:01:49.745 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: 11/22/2024 11:01:49.745 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 11/22/2024 11:01:49.819 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE [__EFMigrationsHistory] (
          [MigrationId] nvarchar(150) NOT NULL,
          [ProductVersion] nvarchar(32) NOT NULL,
          CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
      );
info: 11/22/2024 11:01:49.820 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT 1
info: 11/22/2024 11:01:49.821 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT OBJECT_ID(N'[__EFMigrationsHistory]');
info: 11/22/2024 11:01:49.824 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [MigrationId], [ProductVersion]
      FROM [__EFMigrationsHistory]
      ORDER BY [MigrationId];
info: 11/22/2024 11:01:49.834 RelationalEventId.MigrationsNotApplied[20405] (Microsoft.EntityFrameworkCore.Migrations) 
      No migrations were applied. The database is already up to date.

>>>>>>>>>>>>>>>>>>>> Done Migrate

But in EF9, it throws:

>>>>>>>>>>>>>>>>>>>> Starting migrate

Unhandled exception. System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Migrations.PendingModelChangesWarning': The model for context 'ContextOfDoom' has pending changes. Add a new migration before updating the database. This exception can be suppressed or logged by passing event ID 'RelationalEventId.PendingModelChangesWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'.
   at Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition`1.Log[TLoggerCategory](IDiagnosticsLogger`1 logger, TParam arg)
   at Microsoft.EntityFrameworkCore.Diagnostics.RelationalLoggerExtensions.PendingModelChangesWarning(IDiagnosticsLogger`1 diagnostics, Type contextType)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
   at Program.<Main>$(String[] args) in D:\code\DoomSlug\DoomSlug\Program.cs:line 35
   at Program.<Main>(String[] args)

Process finished with exit code -532,462,766.

Full code:

using (var context = new ContextOfDoom())
{
    Console.WriteLine();
    Console.WriteLine(">>>>>>>>>>>>>>>>>>>> Starting migrate");
    Console.WriteLine();
    context.Database.Migrate();
    Console.WriteLine();
    Console.WriteLine(">>>>>>>>>>>>>>>>>>>> Done Migrate");
    Console.WriteLine();
    
    //await context.Database.EnsureDeletedAsync();
}

public class ContextOfDoom : DbContext
{
    public ContextOfDoom()
    {
        Console.WriteLine();
        Console.WriteLine(">>>>>>>>>>>>>>>>>>>> Starting tables");
        Console.WriteLine();

        if (Database.GetService<IDatabaseCreator>() is not RelationalDatabaseCreator dbCreate) return;
        // Create Database 
        if (!dbCreate.CanConnect())
        {
            dbCreate.Create();
        }
        
        // Create Tables
        if (!dbCreate.HasTables())
        {
            dbCreate.CreateTables();
        }

        Console.WriteLine();
        Console.WriteLine(">>>>>>>>>>>>>>>>>>>> Done tables");
        Console.WriteLine();
    }

    public DbSet<Product> Products => Set<Product>();

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .EnableSensitiveDataLogging()
            .LogTo(Console.WriteLine, LogLevel.Information)
            .UseSqlServer(
                "Server=localhost;Database=DoomSlug;Trusted_Connection=True;TrustServerCertificate=True");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>();
    }
}

public class Product
{
    public int Id { get; set; }
}

[ajcvickers]

/cc @bricelam Do you remember anything about Migrate creating the history table in this case?

[sbwalker]

Repro:

1. Clone dev branch from https://github.com/oqtane/oqtane.framework
2. Open Oqtane.sln
3. Navigate to Oqtane.Server\Extensions\DbContextOptionsBuilderExtensions.cs
4. Remove ".ConfigureWarnings(warnings => warnings.Log(RelationalEventId.PendingModelChangesWarning));"
5. Build solution and run

The system will display an "Installation Wizard" screen:

For simplicity you can choose SQLite for the Database option
Enter some values for the initial user account which will be created ie:

Username: host
Password: P@ssw0rd
Email [email protected]
Template: Default Site Template

Click Install

Error message will be displayed:

"An Error Occurred Provisioning The Master Database. This Is Usually Related To The Master Database Not Being In A Supported State. System.InvalidOperationException: An error was generated for warning 'Microsoft.EntityFrameworkCore.Migrations.PendingModelChangesWarning': The model for context 'MasterDBContext' has pending changes. Add a new migration before updating the database. This exception can be suppressed or logged by passing event ID 'RelationalEventId.PendingModelChangesWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'. at Microsoft.EntityFrameworkCore.Diagnostics.EventDefinition1.Log[TLoggerCategory](IDiagnosticsLogger1 logger, TParam arg) at Microsoft.EntityFrameworkCore.Diagnostics.RelationalLoggerExtensions.PendingModelChangesWarning(IDiagnosticsLogger`1 diagnostics, Type contextType) at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration) at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade) at Oqtane.Infrastructure.DatabaseManager.MigrateMaster(InstallConfig install) in C:\Source\Projects\oqtane.framework\Oqtane.Server\Infrastructure\DatabaseManager.cs:line 271"

Note that the application has a full set of migrations which it runs, that are in the following folder:

Oqtane.Server\Migrations

Note that one unique thing that it does is create a custom __EFMigrationsHistory table:

It does this because it is really helpful to know when a migration was executed and what version of the framework it was run on.

Note that this installation approach has worked from .NET 3.1 to 8.0... and now throws an exception on .NET 9.0 (unless the .ConfigureWarnings is added)

To rerun, simply edit the appsettings.json file and set "DefaultConnection": ""

[bricelam]

Create followed by CreateTables bypasses migrations to create the schema. Migrate will create the history table when it applies the first migration. If there are no migrations, I don’t think it created the history table. (But I could be wrong.)

[AndriySvyryd]

@PureIso That's the intended behavior. If your app does not have any migrations or the last one is out-of-date Database.Migrate will now throw an exception.

[PureIso]

@PureIso That's the intended behavior. If your app does not have any migrations or the last one is out-of-date Database.Migrate will now throw an exception.

Any way to avoid it apart from warning suppress?

[AndriySvyryd]

Any way to avoid it apart from warning suppress?

You need to create a migration

[AndriySvyryd]

@sbwalker Are you using the same migrations for different providers (SQL Server, Sqlite, etc.)?
If so, that's an unsupported scenario and the solution is to call warnings.Log(RelationalEventId.PendingModelChangesWarning) like you did

[sbwalker]

Yes, we are using the same migrations for SQL Server, SQLite, MySQL, and PostgreSQL. It's been a real challenge... most developers naively believe that EF Core abstracts all of the complexities of supporting multiple databases... but definitely NOT when it comes to migrations (all relational databases are not created equal)