Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation #35162

Open
meghanmae opened this issue Nov 20, 2024 · 4 comments · May be fixed by #35225
Open

Regression from EF Core 8 to 9: MigrationBuilder.DropTable Causes Issues with Subsequent Table Recreation #35162

meghanmae opened this issue Nov 20, 2024 · 4 comments · May be fixed by #35225
Assignees
@roji
Labels
area-migrations customer-reported regression type-bug

Comments

@meghanmae
Copy link

meghanmae commented Nov 20, 2024 •
edited
Loading

Problem

After upgrading from EF Core 8 to EF Core 9, we encountered a regression where using migrationBuilder.DropTable followed by recreating the same table in a single migration causes issues. The issue seems to be resolved when switching to a raw SQL command (migrationBuilder.Sql("DROP TABLE ...");) to drop the table instead of using migrationBuilder.DropTable.

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Assuming table Employees was previously created...

            // DropTable: causes the migration to fail
            migrationBuilder.DropTable(
                name: "Employees");

            // Uncomment this (and comment out DropTable) to run the migration successfully 
            //migrationBuilder.Sql("""
            //    Drop Table Employees;
            //    """);

            // Recreate table
            migrationBuilder.CreateTable(
                name: "Employees",
                columns: table => new
                {
                    EmployeeId = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Employees", x => x.EmployeeId);
                });
        }

Exception & stack tack traces

System.Collections.Generic.KeyNotFoundException: 'The given key '(Employees, )' was not present in the dictionary.'

 	[Exception] System.Private.CoreLib.dll!System.ThrowHelper.ThrowKeyNotFoundException<T>(T key)	Unknown
 	[Exception] System.Private.CoreLib.dll!System.Collections.Generic.Dictionary<TKey, TValue>.this[TKey].get(TKey key)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.SqlServer.dll!Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.RewriteOperations(System.Collections.Generic.IReadOnlyList<Microsoft.EntityFrameworkCore.Migrations.Operations.MigrationOperation> migrationOperations, Microsoft.EntityFrameworkCore.Metadata.IModel model, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.SqlServer.dll!Microsoft.EntityFrameworkCore.Migrations.SqlServerMigrationsSqlGenerator.Generate(System.Collections.Generic.IReadOnlyList<Microsoft.EntityFrameworkCore.Migrations.Operations.MigrationOperation> operations, Microsoft.EntityFrameworkCore.Metadata.IModel model, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GenerateUpSql(Microsoft.EntityFrameworkCore.Migrations.Migration migration, Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerationOptions options)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.GetMigrationCommandLists.AnonymousMethod__2()	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(Microsoft.EntityFrameworkCore.DbContext context, string targetMigration, Microsoft.EntityFrameworkCore.Migrations.MigrationExecutionState state, bool useTransaction)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate.AnonymousMethod__20_1(Microsoft.EntityFrameworkCore.DbContext c, (Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator Migrator, string TargetMigration, Microsoft.EntityFrameworkCore.Migrations.MigrationExecutionState State, bool UseTransaction) s)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute.AnonymousMethod__0(Microsoft.EntityFrameworkCore.DbContext context, TState state)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementation<TState, TResult>(System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> operation, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> verifySucceeded, TState state)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.dll!Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.Execute<TState, TResult>(TState state, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, TResult> operation, System.Func<Microsoft.EntityFrameworkCore.DbContext, TState, Microsoft.EntityFrameworkCore.Storage.ExecutionResult<TResult>> verifySucceeded)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(string targetMigration)	Unknown
 	[Exception] Microsoft.EntityFrameworkCore.Relational.dll!Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade)	Unknown
>	[Exception] MyApp.Web.dll!Program.<Main>$(string[] args) Line 195	C#

Versions

EF Core version: 9.0.0
Database provider: Microsoft SQL Server (LocalDB)
Target framework: .NET 9.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.12.0
@AndriySvyryd AndriySvyryd removed their assignment Nov 23, 2024
@devedse
Copy link

devedse commented Nov 26, 2024

We are running into the same issue on our customer code base. The workaround also works for us.

@AnthonyDewhirst AnthonyDewhirst mentioned this issue Nov 26, 2024
Closed
@roji
Copy link
Member

roji commented Nov 27, 2024

Poaching (have a bit of extra capacity to help with 9.0 regressions for the 9.0.1 cutoff). Confirmed regression from 8.0 to 9.0.

Repro 
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.MigrateAsync();

public class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();
}

public class Blog
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Generate an initial migration for the above, then add another empty migration, and paste the following into it:

            migrationBuilder.DropTable(
                name: "Blogs");

            migrationBuilder.CreateTable(
                name: "Blogs",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Blogs", x => x.Id);
                });

@roji roji assigned roji and unassigned maumar Nov 27, 2024
@roji
Copy link
Member

roji commented Nov 27, 2024

@meghanmae and @AnthonyDewhirst, can you please confirm how you ended up with a single migration that has a DropTable and then a CreateTable on the same migration? Is this the result of manual editing of the migration, or did EF generate this itself? If it's the latter, can you provide more info on that?

roji added a commit to roji/efcore that referenced this issue Nov 27, 2024
@roji
Temporary fix for DropTable/CreateTable issue around temporal tables
36dc641 
Fixes dotnet#35162
This was referenced Nov 27, 2024
Merged
Draft
@AnthonyDewhirst
Copy link

Hi @roji apologise once more...

By the time I got this all fixed which included rolling back a migration in code to see what 9.0.0 generated, I realise that there should never have been a drop table. This was a case of 2 of my devs doing competing work and checking the code in and fixing it, by instead of deleting the migrations and crafting one again, by hand manipulating the files.

So, yep, our bad, not something should have ever made it passed a PR but had worked previously. I used the raw sql work around. But it's on my todo list to go generate the migrations properly. It's live so I need to be careful, but I think it's straight forward.

So from my perspective, this is user error and not an issue we have with ef core.

I did mean to write this in early and was also on my todo list.

Thanks for the great work, much appreciated!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@roji roji

Labels
area-migrations customer-reported regression type-bug
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Temporary fix for DropTable/CreateTable issue around temporal tables roji/efcore
7 participants
@AnthonyDewhirst @maumar @roji @devedse @cincuranet @AndriySvyryd @meghanmae