search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.79k stars 3.19k forks source link

Temporal table migration regression from EF Core 8 to 9 #35108

Open Moerup opened 1 day ago

Moerup commented 1 day ago

Migration regression from EF Core 8 to 9

I'm in the process of upgrading an application from .NET 8 to 9.

But I'm running into problems when trying to run existing migrations that have been created with EF Core 8 and working for over a year now on multiple versions of EF Core (8.0.4 to 8.0.11)

I have 2 migrations that throws exceptions and can't complete at all on v9.0.0, but if I revert to 8.0.11 it works fine. It's a series of multiple pretty complex migrations and changes over time, so creating a small repro will take some time, so I hope that's not necessary. Let me know if it is or you need more information!

Include your code

I have Temporal tables enabled for a model: "TestObjective" like this:

public void Configure(EntityTypeBuilder<TestObjective> builder)
{
    builder.ToTable(nameof(MasterCatalogueDbContext.TestObjectives), schema: DatabaseConstants.TestCatalogueSchemaName);

    // Enable history
    builder.Metadata.SetIsTemporal(true);

   // Removed relations, property configurations and Includes for brevity.
}

At some point we renamed a relations table from "TestObjectivesProjectRoles" to "TestObjectivesOwners". All this is auto generated with dotnet-ef migrations add But the migration generated to alter the temporal columns:

migrationBuilder.AlterColumn<DateTime>(
    name: "PeriodStart",
    schema: "testCatalogue",
    table: "TestObjectivesOwners",
    type: "datetime2",
    nullable: false,
    oldClrType: typeof(DateTime),
    oldType: "datetime2")
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "TestObjectivesOwnersHistory")
    .Annotation("SqlServer:TemporalHistoryTableSchema", "testCatalogue")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
    .OldAnnotation("SqlServer:IsTemporal", true)
    .OldAnnotation("SqlServer:TemporalHistoryTableName", "TestObjectivesProjectRolesHistory")
    .OldAnnotation("SqlServer:TemporalHistoryTableSchema", "testCatalogue")
    .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

Results in this error:

2024-11-14T15:07:43 [15:07:43 INF] Applying migration '20240102112555_RenameTestCatalogueOwnerTables'.
2024-11-14T15:07:44 [15:07:44 ERR] Failed executing DbCommand (22ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2024-11-14T15:07:44 DECLARE @var4 sysname;
2024-11-14T15:07:44 SELECT @var4 = [d].[name]
2024-11-14T15:07:44 FROM [sys].[default_constraints] [d]
2024-11-14T15:07:44 INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
2024-11-14T15:07:44 WHERE ([d].[parent_object_id] = OBJECT_ID(N'[testCatalogue].[TestObjectivesOwners]') AND [c].[name] = N'PeriodStart');
2024-11-14T15:07:44 IF @var4 IS NOT NULL EXEC(N'ALTER TABLE [testCatalogue].[TestObjectivesOwners] DROP CONSTRAINT [' + @var4 + '];');
2024-11-14T15:07:44 ALTER TABLE [testCatalogue].[TestObjectivesOwners] ALTER COLUMN [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL;
2024-11-14T15:07:44 [15:07:44 ERR] Failed to migrate MasterCatalogue database!
2024-11-14T15:07:44 Microsoft.Data.SqlClient.SqlException (0x80131904): Period column 'PeriodStart' in a system-versioned temporal table cannot be altered.
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
2024-11-14T15:07:44    at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.Execute(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.<>c.<ExecuteNonQuery>b__3_1(DbContext _, ValueTuple`6 s)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<Migrate>b__20_1(DbContext c, ValueTuple`4 s)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
2024-11-14T15:07:44    at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
2024-11-14T15:07:44    at RTDT.MasterCatalogue.Web.Program.MigrateAndSeed(WebApplication app) in C:\GitVestas\RTDT\src\Services\RTDT.MasterCatalogue\src\RTDT.MasterCatalogue.Web\Program.cs:line 171
2024-11-14T15:07:44 ClientConnectionId:2bd97670-656a-4168-b8b1-d65d6137ead2
2024-11-14T15:07:44 Error Number:13599,State:1,Class:16

The second migration that fails is another rename of another table where we use rowversion configured like this:

public void Configure(EntityTypeBuilder<Sensor> builder)
{
    builder.ToTable(nameof(MasterCatalogueDbContext.Sensors), schema: DatabaseConstants.MasterDataCatalogueSchemaName);

    // Optimistic Concurrency token
    builder.Property(p => p.RowVersion).IsRowVersion();
}

With this migration created for altering the rowversion column:

migrationBuilder.AlterColumn<byte[]>(
    name: "RowVersion",
    schema: "attributeList",
    table: "MeasurementUnits",
    type: "rowversion",
    rowVersion: true,
    nullable: false,
    defaultValue: Array.Empty<byte>(),
    oldClrType: typeof(byte[]),
    oldType: "rowversion",
    oldRowVersion: true,
    oldDefaultValue: Array.Empty<byte>())
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "MeasurementUnitsHistory")
    .Annotation("SqlServer:TemporalHistoryTableSchema", "attributeList")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
    .OldAnnotation("SqlServer:IsTemporal", true)
    .OldAnnotation("SqlServer:TemporalHistoryTableName", "SensorUnitsHistory")
    .OldAnnotation("SqlServer:TemporalHistoryTableSchema", "attributeList")
    .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");

And that throws this error:

2024-11-14T15:17:05 [15:17:05 INF] Applying migration '20240610063634_RenameSensorUnitToMeasurementUnit'.
2024-11-14T15:17:05 [15:17:05 ERR] Failed executing DbCommand (32ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
2024-11-14T15:17:05 DECLARE @var15 sysname;
2024-11-14T15:17:05 SELECT @var15 = [d].[name]
2024-11-14T15:17:05 FROM [sys].[default_constraints] [d]
2024-11-14T15:17:05 INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
2024-11-14T15:17:05 WHERE ([d].[parent_object_id] = OBJECT_ID(N'[attributeList].[MeasurementUnits]') AND [c].[name] = N'RowVersion');
2024-11-14T15:17:05 IF @var15 IS NOT NULL EXEC(N'ALTER TABLE [attributeList].[MeasurementUnits] DROP CONSTRAINT [' + @var15 + '];');
2024-11-14T15:17:05 ALTER TABLE [attributeList].[MeasurementUnits] ALTER COLUMN [RowVersion] rowversion NOT NULL;
2024-11-14T15:17:05 ALTER TABLE [attributeList].[MeasurementUnits] ADD DEFAULT 0x FOR [RowVersion];
2024-11-14T15:17:05 [15:17:05 ERR] Failed to migrate MasterCatalogue database!
2024-11-14T15:17:05 Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot alter column 'RowVersion' to be data type timestamp.
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean isAsync, Int32 timeout, Boolean asyncWrite)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
2024-11-14T15:17:05    at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.Execute(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable`1 isolationLevel)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.<>c.<ExecuteNonQuery>b__3_1(DbContext _, ValueTuple`6 s)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IReadOnlyList`1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable`1 isolationLevel)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.<Migrate>b__20_1(DbContext c, ValueTuple`4 s)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
2024-11-14T15:17:05    at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
2024-11-14T15:17:05    at RTDT.MasterCatalogue.Web.Program.MigrateAndSeed(WebApplication app) in C:\GitVestas\RTDT\src\Services\RTDT.MasterCatalogue\src\RTDT.MasterCatalogue.Web\Program.cs:line 171
2024-11-14T15:17:05 ClientConnectionId:f4a440da-ec8a-43b2-9051-87cbd87b6faa
2024-11-14T15:17:05 Error Number:4927,State:1,Class:16

Include provider and version information

EF Core version: 9.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 9.0 Operating system: Windows & Linux (Local & CICD) IDE: Visual Studio Enterprise 2022 17.12.0

bhp15973 commented 11 hours ago

I have the same problem. In our CICD we are always recreating db, so we execute always all migrations. I have seen that the difference is near PeriodStart/PeriodEnd GENERATED ALWAYS AS ROW START HIDDEN is added in EF Core 9. Our error says: cannot create generated always column when system_time period is not defined.