efcore: Temporal table migration regression from EF Core 8 to 9
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 a migration 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
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
About this issue
- Original URL
- State: closed
- Created 2 years ago
- Reactions: 15
- Comments: 18 (7 by maintainers)
Commits related to this issue
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the number of anno... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the number of anno... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the number of anno... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the number of anno... — committed to dotnet/efcore by maumar 2 years ago
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 (#35283) In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the numbe... — committed to samiizadeh/efcore by maumar 2 years ago
- Fix to #35108 - Temporal table migration regression from EF Core 8 to 9 (#35289) In 9 we changed the way we process migration of temporal tables. One of the changes was drastically reducing the numbe... — committed to dotnet/efcore by maumar 2 years ago
fixed for 9.0.2, closing
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 HIDDENis added in EF Core 9. Our error says:cannot create generated always column when system_time period is not defined.I get same error as @bhp15973 The generated migration is: ALTER TABLE [InsuranceQuotes] ADD [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT ‘9999-12-31T23:59:59.9999999’;
Resulting in this error from SQL: Cannot create generated always column when SYSTEM_TIME period is not defined.
As far as I can tell this is a standard migration of a non-temporal table to a temporal table. This migration works in efcore 8.
I believe the issue is the ADD COLUMN and PERIOD FOR SYSTEM_TIME need to all happen at the same time:
ALTER TABLE dbo.YourTable ADD SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
in EF8 “GENERATED ALWAYS” is not specified.
as an example, Up method from @danroot 's repro migration in the file 20241121163049_Temporal.cs should look like this:
thanks @danroot for the great repros! It was very helpful and fix is on the way. Several people are hitting this so we will try to fit this into the next available patch release (likely 9.0.2)
In the meantime, here is the workaround. It’s a bit of work and may not be realistic if affected migrations are large enough, but hopefully will unblock some folks.
For the affected migration code (that work on temporal tables or convert from regular to temporal) look for
migrationBuilder.AlterColumn, andmigrationBuilder.CreateColumnthat contain these annotations (should be pretty much all of the operations):For AlterColumnOperation, remove all those annotations (as well as .OldAnnotations entries for those 5 values)
After removing those annotations, if there are no other changes in that operation (i.e. or of the “old” properties have the same value as the new ones) - remove the
migrationBuilder.AlterColumncode entirely for that column.For CreateColumn, also remove those 5 annotations, but additionally for the period start column add:
and for period end column add:
If the migration creates a temporal table right away (rather than converting regular table into temporal), you also need to fix annotations on the CreateTable columns:
this is how the operation should look like initially:
this is what it should be changed to:
do not remove annotations on the CreateTable operation itself, just on the columns
Also note that period start and end columns each have an annotation added just like
migrationBuilder.CreateColumnmentioned before.I have created a repo that presents a minimal reproduction of what I think is the same or similar issue as OP. @Moerup let me know if this is a different issue and I will create a new issue. This is a blocking issue for my team adopting EFCore 9, which we really want to do!
https://github.com/danroot/Spike.EFCore9TemporalTable
Steps to reproduce with this repo:
Steps to reproduce from scratch:
@Moerup I indeed missed the 2nd issue - but it would be better to have that as a separate issue, as it indeed seems unrelated.
@roji I can see you have changed the title to only indicate that it’s temporal table related, but the second issue in my post does not seem related at all to temporal tables, but rowversion and it’s default value.
Maybe I’m wrong tho and they are both related to temporal, just wanted to make sure that part doesn’t get overlooked.