EF Core 6.0 temporal table migration when altering computed column not generating correct script

[siby-george]

File a bug

Step 1 Make table temporal
Step 2 Add migration for that
Step 3 Add new computed column to the entity
step 4 Add Migration for altered column
setp 5 Generate script for migration

Include your code

added new computed column to existing Temporal Table

entity.Property(x => x.Level).HasComputedColumnSql("Id.GetLevel() PERSISTED");

Generate migration

dotnet ef migrations add addinglevel

Generate migration script

dotnet ef migrations script 20210624210145_AddTemporalSupport 20220419235406_addinglevel
Build started...
Build succeeded.
BEGIN TRANSACTION;
GO

ALTER TABLE [HierarchyNode] ADD [Level] AS Id.GetLevel() PERSISTED;
GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20220419235406_addinglevel', N'6.0.4');
GO

COMMIT;
GO

When running migration in SQL Studio

Msg 13724, Level 16, State 1, Line 1
System-versioned table schema modification failed because adding computed column while system-versioning is ON is not supported.

Expected

Migration script to turn of versioning and alter both table and turn versioning on

EF Core version: 6.0.4
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 6.0)
Operating system: Windows
IDE: (e.g. Visual Studio 2022 17.2)

[omarfq]

Had a similar issue while adding a new migration after altering a column name or adding a new column.
Steps are similar to @siby-george's:

1. Make temporal table.
2. Add new migration for that temporal table.
3. Alter any column name or add new one to the existing entity.
4. Add migration for altered/added column.
5. Generate migration script.

Code

Original entity looked like this:

public class ExonerationDetermination : AuditEntity
  {
      public long Id { get; set; }
      public string Determination { get; set; }
      public string Reason { get; set; }
      public string Comments { get; set; }
      public virtual User CreatedByUser { get; set; }
  }

After modification:

public class ExonerationDetermination : AuditEntity
  {
      public long Id { get; set; }
      public string Determination { get; set; }
      public string Reason { get; set; }
      public string AdditionalComments { get; set; } // Modified
      public virtual User CreatedByUser { get; set; }
      public string PreviousStatus { get; set; } // Added
      public string NewStatus { get; set; } // Added
  }

Generate migration with command:

dotnet ef migrations add ExonerationRequests

When building and starting the project, the following error is displayed on the output window:

Setting SYSTEM_VERSIONING to ON failed because table 'PEJC.exoneration.Determination' has 14 columns and table 'PEJC.exoneration.DeterminationHistory' has 11 columns.

The three columns that are missing from the table 'PEJC.exoneration.DeterminationHistory' are precisely the ones we added/modified. We verified this in the Down method in the migration file generated by EF Core.

Expected

Migration script should be able to turn off versioning, alter/add columns as specified on the entity, then turn versioning back on.

Additional Info

EF Core version: 6.0.9
Database provider: SQL Server
Target framework: .NET 6
Operating system: Windows
IDE: Visual Studio 2022 V17.3.4

[James-Jackson-South]

Could someone post an example of what the correct migration script should be for such a situation (Add/Remove columns)? It would be useful as a workaround example for people waiting for a fix.

[ajcvickers]

/cc @maumar

[ajcvickers]

@James-Jackson-South The correct script in this case would need to make the table not system-versioned, add the computed column, then make it system-versioned again. However, after discussion we're not sure if this will work either. It may not be possible to make the table system-versioned if it has a computed column, and even if it is, it's not clear how that computed column would work when looking at historical data. What is the computed column definition that you want to use?

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[James-Jackson-South]

@James-Jackson-South The correct script in this case would need to make the table not system-versioned, add the computed column, then make it system-versioned again. However, after discussion we're not sure if this will work either. It may not be possible to make the table system-versioned if it has a computed column, and even if it is, it's not clear how that computed column would work when looking at historical data. What is the computed column definition that you want to use?

Sorry @ajcvickers Just saw this. I wasn't using a computed column in my case but changing the column type. The workaround you suggested is the approach I eventually figured out, but it took me a rather long while to do so.