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
