Applying a rename migration with a Temporal Table gets SqlException (0x80131904) mismatching number of BEGIN and COMMIT statements

Question

We have a model-first approach in a recently migrated project from .NET5 to .NET8 and to EF 6. Our model has an entity with a SQL Server Temporal Table related.

The problem arises when we want to rename a column/property from our entity. The migration script is generated with a call to the sp_rename store procedure but we get:

Microsoft.Data.SqlClient.SqlException (0x80131904): Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

I think this script is not handling the fact that the related temporal table has to be migrated too.

We generate the migration-script and tried to apply it directly to the DB and obviously we got the same error.

This is a completely new column, so we decided to drop it a recreate it but with a different context this would not be possible.

Any ideas about it?

Answer 1

Here is a possible solution that I think of. The key step is to disable system versioning, apply the schema changes, and then re-enable system versioning.

ALTER TABLE YourMainTable SET (SYSTEM_VERSIONING = OFF);

sp_rename 'YourMainTable.OldColumnName', 'NewColumnName', 'COLUMN';
sp_rename 'YourHistoryTable.OldColumnName', 'NewColumnName', 'COLUMN';

ALTER TABLE YourMainTable 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourHistoryTable, DATA_CONSISTENCY_CHECK = ON));

Update: EF6 doesn't inherently support SQL Server temporal tables directly within its migrations or model configuration. The above solution to manually alter the system versioning during migrations is currently the most straightforward approach to managing schema changes involving temporal tables in EF6.

However, you may still automate this process with EF6 by adding some custom scripts in migration. Inside your migration's Up() and Down() methods, you can use the Sql() method to run raw SQL commands that implement the steps outlined above.

Ex:

public override void Up()
{
    Sql("ALTER TABLE YourMainTable SET (SYSTEM_VERSIONING = OFF);");

    Sql("EXEC sp_rename 'YourMainTable.OldColumnName', 'NewColumnName', 'COLUMN';");
    Sql("EXEC sp_rename 'YourHistoryTable.OldColumnName', 'NewColumnName', 'COLUMN';");

    Sql("ALTER TABLE YourMainTable SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourHistoryTable, DATA_CONSISTENCY_CHECK = ON));");
}

public override void Down()
{
    
}

I hope this helps.