SqlServerWithTemporalTablesMigrationsSqlGenerator to use Temporal Tables in migrations with .NET 5.0 and EF Core 5.0

SampleMigration.cs

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.EntityFrameworkCore.Migrations.Operations.Builders;
using System;

namespace ConsoleApp1.Migrations
{
    public partial class Initial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Pessoas",
                columns: table => new
                {
                    Id = table.Column<long>(type: "bigint", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Nome = table.Column<string>(type: "varchar(50)", unicode: false, maxLength: 50, nullable: false),
                    PeriodEnd = table.Column<DateTime>(type: "datetime2", nullable: false)
                        .TemporalColumn(),
                    PeriodStart = table.Column<DateTime>(type: "datetime2", nullable: false)
                        .TemporalColumn()
                },
                constraints: table => table.PrimaryKey("PK_Pessoas", x => x.Id))
                .TemporalTable();
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(name: "Pessoas")
                .TemporalTable();
        }
    }

    public static class TemporalExtensions
    {
        public static OperationBuilder<AddColumnOperation> TemporalColumn(this OperationBuilder<AddColumnOperation> operation, string temporalPeriodEndColumnName = "PeriodEnd", string temporalPeriodStartColumnName = "PeriodStart")
            => operation
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", temporalPeriodEndColumnName)
                .Annotation("SqlServer:TemporalPeriodStartColumnName", temporalPeriodStartColumnName);

        public static OperationBuilder<DropTableOperation> TemporalTable(this OperationBuilder<DropTableOperation> operation, string temporalPeriodEndColumnName = "PeriodEnd", string temporalPeriodStartColumnName = "PeriodStart")
            => operation
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", $"{operation.GetInfrastructure().Name}History")
                .Annotation("SqlServer:TemporalPeriodEndColumnName", temporalPeriodEndColumnName)
                .Annotation("SqlServer:TemporalPeriodStartColumnName", temporalPeriodStartColumnName);

        public static CreateTableBuilder<T> TemporalTable<T>(this CreateTableBuilder<T> operation, string temporalPeriodEndColumnName = "PeriodEnd", string temporalPeriodStartColumnName = "PeriodStart")
            => operation
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", $"{operation.GetInfrastructure().Name}History")
                .Annotation("SqlServer:TemporalPeriodEndColumnName", temporalPeriodEndColumnName)
                .Annotation("SqlServer:TemporalPeriodStartColumnName", temporalPeriodStartColumnName);
    }
}

SqlServerWithTemporalTablesMigrationsSqlGenerator.cs

using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.EntityFrameworkCore.SqlServer.Internal;
using Microsoft.EntityFrameworkCore.SqlServer.Metadata.Internal;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Microsoft.EntityFrameworkCore.Migrations
{

    public class SqlServerWithTemporalTablesMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
    {
        private const string SqlServerIsTemporal = "SqlServer:IsTemporal";
        private const string SqlServerTemporalPeriodStartColumnName = "SqlServer:TemporalPeriodStartColumnName";
        private const string SqlServerTemporalPeriodEndColumnName = "SqlServer:TemporalPeriodEndColumnName";
        private const string SqlServerTemporalHistoryTableSchema = "SqlServer:TemporalHistoryTableSchema";
        private const string SqlServerTemporalHistoryTableName = "SqlServer:TemporalHistoryTableName";
        private const string SqlServerTemporalPeriodStartPropertyName = "SqlServer:TemporalPeriodStartPropertyName";
        private const string SqlServerTemporalPeriodEndPropertyName = "SqlServer:TemporalPeriodEndPropertyName";

        private IReadOnlyList<MigrationOperation> _operations = null!;

        public SqlServerWithTemporalTablesMigrationsSqlGenerator(
            MigrationsSqlGeneratorDependencies dependencies,
            IRelationalAnnotationProvider migrationsAnnotations)
            : base(dependencies, migrationsAnnotations)
        {
        }

        protected override void Generate(
            CreateTableOperation operation,
            IModel? model,
            MigrationCommandListBuilder builder,
            bool terminate = true)
        {
            var hasComments = operation.Comment != null || operation.Columns.Any(c => c.Comment != null);

            if (!terminate && hasComments)
            {
                throw new ArgumentException(SqlServerStrings.CannotProduceUnterminatedSQLWithComments(nameof(CreateTableOperation)));
            }

            if (operation[SqlServerIsTemporal] as bool? == true)
            {
                var historyTableSchema = operation[SqlServerTemporalHistoryTableSchema] as string
                    ?? model?.GetDefaultSchema();
                var needsExec = historyTableSchema == null;
                var subBuilder = needsExec
                    ? new MigrationCommandListBuilder(Dependencies)
                    : builder;

                subBuilder
                    .Append("CREATE TABLE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema))
                    .AppendLine(" (");

                using (subBuilder.Indent())
                {
                    CreateTableColumns(operation, model, subBuilder);
                    CreateTableConstraints(operation, model, subBuilder);
                    subBuilder.AppendLine(",");
                    var startColumnName = operation[SqlServerTemporalPeriodStartColumnName] as string;
                    var endColumnName = operation[SqlServerTemporalPeriodEndColumnName] as string;
                    var start = Dependencies.SqlGenerationHelper.DelimitIdentifier(startColumnName!);
                    var end = Dependencies.SqlGenerationHelper.DelimitIdentifier(endColumnName!);
                    subBuilder.AppendLine($"PERIOD FOR SYSTEM_TIME({start}, {end})");
                }

                if (needsExec)
                {
                    subBuilder
                        .EndCommand();

                    var execBody = subBuilder.GetCommandList().Single().CommandText.Replace("'", "''");

                    builder
                        .AppendLine("DECLARE @historyTableSchema sysname = SCHEMA_NAME()")
                        .Append("EXEC(N'")
                        .Append(execBody);
                }

                var historyTableName = operation[SqlServerTemporalHistoryTableName] as string;
                string historyTable;
                if (needsExec)
                {
                    historyTable = Dependencies.SqlGenerationHelper.DelimitIdentifier(historyTableName!);
                    builder.Append($") WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].{historyTable}))')");
                }
                else
                {
                    historyTable = Dependencies.SqlGenerationHelper.DelimitIdentifier(historyTableName!, historyTableSchema);
                    builder.Append($") WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {historyTable}))");
                }
            }
            else
            {
                base.Generate(operation, model, builder, terminate: false);
            }

            var memoryOptimized = IsMemoryOptimized(operation);
            if (memoryOptimized)
            {
                builder.AppendLine();
                using (builder.Indent())
                {
                    builder.AppendLine("WITH");
                    using (builder.Indent())
                    {
                        builder.Append("(MEMORY_OPTIMIZED = ON)");
                    }
                }
            }

            if (hasComments)
            {
                //Check.DebugAssert(terminate, "terminate is false but there are comments");

                builder.AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);

                var firstDescription = true;
                if (operation.Comment != null)
                {
                    AddDescription(builder, operation.Comment, operation.Schema, operation.Name);

                    firstDescription = false;
                }

                foreach (var column in operation.Columns)
                {
                    if (column.Comment == null)
                    {
                        continue;
                    }

                    AddDescription(
                        builder, column.Comment,
                        operation.Schema,
                        operation.Name,
                        column.Name,
                        omitVariableDeclarations: !firstDescription);

                    firstDescription = false;
                }

                builder.EndCommand(suppressTransaction: memoryOptimized);
            }
            else if (terminate)
            {
                builder
                    .AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator)
                    .EndCommand(suppressTransaction: memoryOptimized);
            }
        }

        protected override void ColumnDefinition(
        string? schema,
        string table,
        string name,
        ColumnOperation operation,
        IModel? model,
        MigrationCommandListBuilder builder)
        {
            if (operation.ComputedColumnSql != null)
            {
                ComputedColumnDefinition(schema, table, name, operation, model, builder);

                return;
            }

            var columnType = operation.ColumnType ?? GetColumnType(schema, table, name, operation, model)!;
            builder
                .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(name))
                .Append(" ")
                .Append(columnType);

            if (operation.Collation != null)
            {
                builder
                    .Append(" COLLATE ")
                    .Append(operation.Collation);
            }

            var periodStartColumnName = operation[SqlServerTemporalPeriodStartColumnName] as string;
            var periodEndColumnName = operation[SqlServerTemporalPeriodEndColumnName] as string;

            if (name == periodStartColumnName
                || name == periodEndColumnName)
            {
                builder.Append(" GENERATED ALWAYS AS ROW ");
                builder.Append(name == periodStartColumnName ? "START" : "END");
                builder.Append(" HIDDEN");
            }

            builder.Append(operation.IsNullable ? " NULL" : " NOT NULL");

            if (!string.Equals(columnType, "rowversion", StringComparison.OrdinalIgnoreCase)
                && !string.Equals(columnType, "timestamp", StringComparison.OrdinalIgnoreCase))
            {
                // rowversion/timestamp columns cannot have default values, but also don't need them when adding a new column.
                DefaultValue(operation.DefaultValue, operation.DefaultValueSql, columnType, builder);
            }

            var identity = operation[SqlServerAnnotationNames.Identity] as string;
            if (identity != null
                || operation[SqlServerAnnotationNames.ValueGenerationStrategy] as SqlServerValueGenerationStrategy?
                == SqlServerValueGenerationStrategy.IdentityColumn)
            {
                builder.Append(" IDENTITY");

                if (!string.IsNullOrEmpty(identity)
                    && identity != "1, 1")
                {
                    builder
                        .Append("(")
                        .Append(identity)
                        .Append(")");
                }
            }
        }

        public override IReadOnlyList<MigrationCommand> Generate(
            IReadOnlyList<MigrationOperation> operations,
            IModel? model = null,
            MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default)
        {
            _operations = operations;
            try
            {
                return base.Generate(RewriteOperations(operations, model, options), model, options);
            }
            finally
            {
                _operations = null!;
            }
        }

        private static bool IsMemoryOptimized(Annotatable annotatable)
            => annotatable[SqlServerAnnotationNames.MemoryOptimized] as bool? == true;

        private IReadOnlyList<MigrationOperation> RewriteOperations(
            IReadOnlyList<MigrationOperation> migrationOperations,
            IModel? model,
            MigrationsSqlGenerationOptions options)
        {
            var operations = new List<MigrationOperation>();

            var versioningMap = new Dictionary<(string?, string?), (string, string?)>();
            var periodMap = new Dictionary<(string?, string?), (string, string)>();
            var availableSchemas = new List<string>();

            foreach (var operation in migrationOperations)
            {
                if (operation is EnsureSchemaOperation ensureSchemaOperation)
                {
                    availableSchemas.Add(ensureSchemaOperation.Name);
                }

                var isTemporal = operation[SqlServerIsTemporal] as bool? == true;
                if (isTemporal)
                {
                    string? table = null;
                    string? schema = null;

                    if (operation is ITableMigrationOperation tableMigrationOperation)
                    {
                        table = tableMigrationOperation.Table;
                        schema = tableMigrationOperation.Schema;
                    }

                    schema ??= model?.GetDefaultSchema();
                    var historyTableName = operation[SqlServerTemporalHistoryTableName] as string;
                    var historyTableSchema = operation[SqlServerTemporalHistoryTableSchema] as string
                        ?? model?.GetDefaultSchema();
                    var periodStartColumnName = operation[SqlServerTemporalPeriodStartColumnName] as string;
                    var periodEndColumnName = operation[SqlServerTemporalPeriodEndColumnName] as string;

                    switch (operation)
                    {
                        case CreateTableOperation createTableOperation:
                            if (historyTableSchema != createTableOperation.Schema
                                && historyTableSchema != null
                                && !availableSchemas.Contains(historyTableSchema))
                            {
                                operations.Add(new EnsureSchemaOperation { Name = historyTableSchema });
                                availableSchemas.Add(historyTableSchema);
                            }

                            operations.Add(operation);
                            break;

                        case DropTableOperation:
                            DisableVersioning(table!, schema, historyTableName!, historyTableSchema);
                            operations.Add(operation);

                            versioningMap.Remove((table, schema));
                            periodMap.Remove((table, schema));
                            break;

                        case RenameTableOperation renameTableOperation:
                            DisableVersioning(table!, schema, historyTableName!, historyTableSchema);
                            operations.Add(operation);

                            // since table was renamed, remove old entry and add new entry
                            // marked as versioning disabled, so we enable it in the end for the new table
                            versioningMap.Remove((table, schema));
                            versioningMap[(renameTableOperation.NewName, renameTableOperation.NewSchema)] =
                                (historyTableName!, historyTableSchema);

                            // same thing for disabled system period - remove one associated with old table and add one for the new table
                            if (periodMap.TryGetValue((table, schema), out var result))
                            {
                                periodMap.Remove((table, schema));
                                periodMap[(renameTableOperation.NewName, renameTableOperation.NewSchema)] = result;
                            }

                            break;

                        case AlterTableOperation alterTableOperation:
                            var oldIsTemporal = alterTableOperation.OldTable[SqlServerIsTemporal] as bool? == true;
                            if (!oldIsTemporal)
                            {
                                periodMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
                                    (periodStartColumnName!, periodEndColumnName!);
                                versioningMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
                                    (historyTableName!, historyTableSchema);
                            }
                            else
                            {
                                var oldHistoryTableName =
                                    alterTableOperation.OldTable[SqlServerTemporalHistoryTableName] as string;
                                var oldHistoryTableSchema =
                                    alterTableOperation.OldTable[SqlServerTemporalHistoryTableSchema] as string
                                    ?? alterTableOperation.OldTable.Schema
                                    ?? model?[RelationalAnnotationNames.DefaultSchema] as string;

                                if (oldHistoryTableName != historyTableName
                                    || oldHistoryTableSchema != historyTableSchema)
                                {
                                    if (historyTableSchema != null
                                        && !availableSchemas.Contains(historyTableSchema))
                                    {
                                        operations.Add(new EnsureSchemaOperation { Name = historyTableSchema });
                                        availableSchemas.Add(historyTableSchema);
                                    }

                                    operations.Add(
                                        new RenameTableOperation
                                        {
                                            Name = oldHistoryTableName!,
                                            Schema = oldHistoryTableSchema,
                                            NewName = historyTableName,
                                            NewSchema = historyTableSchema
                                        });

                                    if (versioningMap.ContainsKey((alterTableOperation.Name, alterTableOperation.Schema)))
                                    {
                                        versioningMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
                                            (historyTableName!, historyTableSchema);
                                    }
                                }
                            }

                            operations.Add(operation);
                            break;

                        case AlterColumnOperation alterColumnOperation:
                            // if only difference is in temporal annotations being removed or history table changed etc - we can ignore this operation
                            if (!CanSkipAlterColumnOperation(alterColumnOperation.OldColumn, alterColumnOperation))
                            {
                                // when modifying a period column, we need to perform the operations as a normal column first, and only later enable period
                                // removing the period information now, so that when we generate SQL that modifies the column we won't be making them auto generated as period
                                // (making column auto generated is not allowed in ALTER COLUMN statement)
                                // in later operation we enable the period and the period columns get set to auto generated automatically
                                if (alterColumnOperation[SqlServerIsTemporal] as bool? == true
                                    && alterColumnOperation.OldColumn[SqlServerIsTemporal] is null)
                                {
                                    alterColumnOperation.RemoveAnnotation(SqlServerIsTemporal);
                                    alterColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodStartColumnName);
                                    alterColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodEndColumnName);

                                    // TODO: test what happens if default value just changes (from temporal to temporal)
                                }

                                operations.Add(operation);
                            }

                            break;

                        case DropPrimaryKeyOperation:
                        case AddPrimaryKeyOperation:
                            DisableVersioning(table!, schema, historyTableName!, historyTableSchema);
                            operations.Add(operation);
                            break;

                        case DropColumnOperation dropColumnOperation:
                            DisableVersioning(table!, schema, historyTableName!, historyTableSchema);
                            if (dropColumnOperation.Name == periodStartColumnName
                                || dropColumnOperation.Name == periodEndColumnName)
                            {
                                // period columns can be null here - it doesn't really matter since we are never enabling the period back
                                // if we remove the period columns, it means we will be dropping the table also or at least convert it back to regular
                                // which will clear the entry in the periodMap for this table
                                DisablePeriod(table!, schema, periodStartColumnName!, periodEndColumnName!);
                            }

                            operations.Add(operation);

                            break;

                        case AddColumnOperation addColumnOperation:
                            // when adding a period column, we need to add it as a normal column first, and only later enable period
                            // removing the period information now, so that when we generate SQL that adds the column we won't be making them auto generated as period
                            // it won't work, unless period is enabled
                            // but we can't enable period without adding the columns first - chicken and egg
                            if (addColumnOperation[SqlServerIsTemporal] as bool? == true)
                            {
                                addColumnOperation.RemoveAnnotation(SqlServerIsTemporal);
                                addColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodStartColumnName);
                                addColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodEndColumnName);

                                // model differ adds default value, but for period end we need to replace it with the correct one - DateTime.MaxValue
                                if (addColumnOperation.Name == periodEndColumnName)
                                {
                                    addColumnOperation.DefaultValue = DateTime.MaxValue;
                                }
                            }

                            operations.Add(addColumnOperation);
                            break;

                        default:
                            // CreateTableOperation
                            // RenameColumnOperation
                            operations.Add(operation);
                            break;
                    }
                }
                else
                {
                    if (operation is AlterTableOperation alterTableOperation
                        && alterTableOperation.OldTable[SqlServerIsTemporal] as bool? == true)
                    {
                        var historyTableName = alterTableOperation.OldTable[SqlServerTemporalHistoryTableName] as string;
                        var historyTableSchema = alterTableOperation.OldTable[SqlServerTemporalHistoryTableSchema] as string
                            ?? alterTableOperation.OldTable.Schema
                            ?? model?[RelationalAnnotationNames.DefaultSchema] as string;

                        var periodStartColumnName =
                            alterTableOperation.OldTable[SqlServerTemporalPeriodStartColumnName] as string;
                        var periodEndColumnName =
                            alterTableOperation.OldTable[SqlServerTemporalPeriodEndColumnName] as string;

                        DisableVersioning(alterTableOperation.Name, alterTableOperation.Schema, historyTableName!, historyTableSchema);
                        DisablePeriod(alterTableOperation.Name, alterTableOperation.Schema, periodStartColumnName!, periodEndColumnName!);

                        if (historyTableName != null)
                        {
                            operations.Add(
                                new DropTableOperation { Name = historyTableName, Schema = alterTableOperation.OldTable.Schema });
                        }

                        operations.Add(operation);

                        // when we disable versioning and period earlier, we marked it to be re-enabled
                        // since table is no longer temporal we don't need to do that anymore
                        versioningMap.Remove((alterTableOperation.Name, alterTableOperation.Schema));
                        periodMap.Remove((alterTableOperation.Name, alterTableOperation.Schema));
                    }
                    else if (operation is AlterColumnOperation alterColumnOperation)
                    {
                        // if only difference is in temporal annotations being removed or history table changed etc - we can ignore this operation
                        if (alterColumnOperation.OldColumn?[SqlServerIsTemporal] as bool? != true
                            || !CanSkipAlterColumnOperation(alterColumnOperation.OldColumn, alterColumnOperation))
                        {
                            operations.Add(operation);
                        }
                    }
                    else
                    {
                        operations.Add(operation);
                    }
                }
            }

            foreach (var periodMapEntry in periodMap)
            {
                EnablePeriod(periodMapEntry.Key.Item1!, periodMapEntry.Key.Item2, periodMapEntry.Value.Item1, periodMapEntry.Value.Item2);
            }

            foreach (var versioningMapEntry in versioningMap)
            {
                EnableVersioning(
                    versioningMapEntry.Key.Item1!, versioningMapEntry.Key.Item2, versioningMapEntry.Value.Item1,
                    versioningMapEntry.Value.Item2);
            }

            return operations;

            void DisableVersioning(string table, string? schema, string historyTableName, string? historyTableSchema)
            {
                if (!versioningMap.TryGetValue((table, schema), out _))
                {
                    versioningMap[(table, schema)] = (historyTableName, historyTableSchema);

                    operations.Add(
                        new SqlOperation
                        {
                            Sql = new StringBuilder()
                                .Append("ALTER TABLE ")
                                .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))
                                .AppendLine(" SET (SYSTEM_VERSIONING = OFF)")
                                .ToString()
                        });
                }
            }

            void EnableVersioning(string table, string? schema, string historyTableName, string? historyTableSchema)
            {
                var stringBuilder = new StringBuilder();

                if (historyTableSchema == null)
                {
                    // need to run command using EXEC to inject default schema
                    stringBuilder.AppendLine("DECLARE @historyTableSchema sysname = SCHEMA_NAME()");
                    stringBuilder.Append("EXEC(N'");
                }

                var historyTable = historyTableSchema != null
                    ? Dependencies.SqlGenerationHelper.DelimitIdentifier(historyTableName, historyTableSchema)
                    : Dependencies.SqlGenerationHelper.DelimitIdentifier(historyTableName);

                stringBuilder
                    .Append("ALTER TABLE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema));

                if (historyTableSchema != null)
                {
                    stringBuilder.AppendLine($" SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {historyTable}))");
                }
                else
                {
                    stringBuilder.AppendLine(
                        $" SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].{historyTable}))')");
                }

                operations.Add(
                    new SqlOperation { Sql = stringBuilder.ToString() });
            }

            void DisablePeriod(string table, string? schema, string periodStartColumnName, string periodEndColumnName)
            {
                if (!periodMap.TryGetValue((table, schema), out _))
                {
                    periodMap[(table, schema)] = (periodStartColumnName, periodEndColumnName);

                    operations.Add(
                        new SqlOperation
                        {
                            Sql = new StringBuilder()
                                .Append("ALTER TABLE ")
                                .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))
                                .AppendLine(" DROP PERIOD FOR SYSTEM_TIME")
                                .ToString()
                        });
                }
            }

            void EnablePeriod(string table, string? schema, string periodStartColumnName, string periodEndColumnName)
            {
                var addPeriodSql = new StringBuilder()
                    .Append("ALTER TABLE ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))
                    .Append(" ADD PERIOD FOR SYSTEM_TIME (")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(periodStartColumnName))
                    .Append(", ")
                    .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(periodEndColumnName))
                    .Append(')')
                    .ToString();

                if (options.HasFlag(MigrationsSqlGenerationOptions.Idempotent))
                {
                    addPeriodSql = new StringBuilder()
                        .Append("EXEC(N'")
                        .Append(addPeriodSql.Replace("'", "''"))
                        .Append("')")
                        .ToString();
                }

                operations.Add(
                    new SqlOperation { Sql = addPeriodSql });

                operations.Add(
                    new SqlOperation
                    {
                        Sql = new StringBuilder()
                            .Append("ALTER TABLE ")
                            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))
                            .Append(" ALTER COLUMN ")
                            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(periodStartColumnName))
                            .Append(" ADD HIDDEN")
                            .ToString()
                    });

                operations.Add(
                    new SqlOperation
                    {
                        Sql = new StringBuilder()
                            .Append("ALTER TABLE ")
                            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(table, schema))
                            .Append(" ALTER COLUMN ")
                            .Append(Dependencies.SqlGenerationHelper.DelimitIdentifier(periodEndColumnName))
                            .Append(" ADD HIDDEN")
                            .ToString()
                    });
            }

            static bool CanSkipAlterColumnOperation(ColumnOperation first, ColumnOperation second)
                => ColumnPropertiesAreTheSame(first, second)
                    && ColumnOperationsOnlyDifferByTemporalTableAnnotation(first, second)
                    && ColumnOperationsOnlyDifferByTemporalTableAnnotation(second, first);

            static bool ColumnPropertiesAreTheSame(ColumnOperation first, ColumnOperation second)
                => first.ClrType == second.ClrType
                    && first.Collation == second.Collation
                    && first.ColumnType == second.ColumnType
                    && first.Comment == second.Comment
                    && first.ComputedColumnSql == second.ComputedColumnSql
                    && Equals(first.DefaultValue, second.DefaultValue)
                    && first.DefaultValueSql == second.DefaultValueSql
                    && first.IsDestructiveChange == second.IsDestructiveChange
                    && first.IsFixedLength == second.IsFixedLength
                    && first.IsNullable == second.IsNullable
                    && first.IsRowVersion == second.IsRowVersion
                    && first.IsStored == second.IsStored
                    && first.IsUnicode == second.IsUnicode
                    && first.MaxLength == second.MaxLength
                    && first.Precision == second.Precision
                    && first.Scale == second.Scale;

            static bool ColumnOperationsOnlyDifferByTemporalTableAnnotation(ColumnOperation first, ColumnOperation second)
            {
                var unmatched = first.GetAnnotations().ToList();
                foreach (var annotation in second.GetAnnotations())
                {
                    var index = unmatched.FindIndex(
                        a => a.Name == annotation.Name
                            && StructuralComparisons.StructuralEqualityComparer.Equals(a.Value, annotation.Value));
                    if (index == -1)
                    {
                        continue;
                    }

                    unmatched.RemoveAt(index);
                }

                return unmatched.All(
                    a => a.Name is SqlServerIsTemporal
                        or SqlServerTemporalHistoryTableName
                        or SqlServerTemporalHistoryTableSchema
                        or SqlServerTemporalPeriodStartPropertyName
                        or SqlServerTemporalPeriodEndPropertyName
                        or SqlServerTemporalPeriodStartColumnName
                        or SqlServerTemporalPeriodEndColumnName);
            }
        }
    }
}