EF Core and MS SQL Server: Adding Migrations for a Table that has System-Versioning Enabled

Note: this post on based on EF Core 5.x.

In this post, let's see how we can add EF database migration for a table that has System-Versioning enabled. (If you don't know what System-Versioning enabled tables are in Microsoft SQL Server, you can read Temporal tables for more information, but if you landed here in this post, it's very likely you already know what that is).

In my case, I wanted to add a computed column to a table that has System Versioning enabled. So if I just try to add a column to the target table, when applying migrations EF will throw an error, something like "System-versioned table schema modification failed because adding computed column while system-versioning is ON is not supported". So here what we need is to first turn off the system-versioning, do the necessary schema changes and finally turn on the system-versioning back again.

So let's go by an example. I am adding a new computed column to my target table PropertyAccount and it's already System-Versioned.

public class PropertyAccountConfiguration : IEntityTypeConfiguration<PropertyAccount>
{
    public void Configure(EntityTypeBuilder<PropertyAccount> builder)
    {
        // some other configuration
 
        builder
            .Property(x => x.AccountNumber)
            .HasComputedColumnSql("RIGHT('000000'+CAST(Id AS VARCHAR(6)),6)")
            .HasMaxLength(6);
    }
}

So now if I add a migration, EF will generate something like this.

public partial class PT_AddAccountNumberToPropertyAccount : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccount",
            type: "nvarchar(6)",
            maxLength: 6,
            nullable: true,
            computedColumnSql: "RIGHT('000000'+CAST(Id AS VARCHAR(6)),6)");
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccount");
    }
}

But if we try to apply the migration, it's going to throw the above error.

So now we need to turn off the system-versioning, add the column to target table and its History table, and then turn on the system-versioning again, something like below.

public partial class PT_AddAccountNumberToPropertyAccount : Migration
{
    private const string PropertyAccountTableName = "pt.PropertyAccount";
 
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DisableSystemVersioning(PropertyAccountTableName);
 
        migrationBuilder.AddColumn<string>(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccount",
            type: "nvarchar(6)",
            maxLength: 6,
            nullable: true,
            computedColumnSql: "RIGHT('000000'+CAST(Id AS VARCHAR(6)),6)");
 
        migrationBuilder.AddColumn<string>(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccountHistory",
            type: "varchar(6)",
            maxLength: 6,
            nullable: true);
 
        migrationBuilder.EnableSystemVersioning(PropertyAccountTableName);
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DisableSystemVersioning(PropertyAccountTableName);
 
        migrationBuilder.DropColumn(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccount");
 
        migrationBuilder.DropColumn(
            name: "AccountNumber",
            schema: "pt",
            table: "PropertyAccountHistory");
 
        migrationBuilder.EnableSystemVersioning(PropertyAccountTableName);
    }
}

The 2 methods DisableSystemVersioning and EnableSystemVersioning are some two extension methods I have created on MigrationBuilder.

public static class MigrationBuilderExtensions
{
    public static void DisableSystemVersioning(this MigrationBuilder migrationBuilder, string tableName)
    {
        if (string.IsNullOrEmpty(tableName))
        {
            throw new ArgumentNullException(nameof(tableName));
        }
 
        migrationBuilder.Sql($"ALTER TABLE {tableName} SET (SYSTEM_VERSIONING = OFF);");
    }
 
    public static void EnableSystemVersioning(this MigrationBuilder migrationBuilder, string tableName, string historyTableName = null)
    {
        if (string.IsNullOrEmpty(tableName))
        {
            throw new ArgumentNullException(nameof(tableName));
        }
 
        if (string.IsNullOrEmpty(historyTableName))
        {
            historyTableName = $"{tableName}History";
        }
 
        migrationBuilder.Sql(@$"ALTER TABLE {tableName} SET
            (
                SYSTEM_VERSIONING = ON
                ( HISTORY_TABLE = {historyTableName})
            );
        ");
    }
}

And this works like a charm. 

Hope this helps.

Happy Coding.

Regards,
Jaliya