Skip to content

Instantly share code, notes, and snippets.

@AlbertoMonteiro

AlbertoMonteiro/SampleMigration.cs

Last active December 12, 2021 03:40
Download ZIP

Revisions

  1. AlbertoMonteiro revised this gist Dec 12, 2021. 1 changed file with 0 additions and 2 deletions.
    2 changes: 0 additions & 2 deletions SampleMigration.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -10,7 +10,6 @@ public partial class Initial : Migration
    {
    protected override void Up(MigrationBuilder migrationBuilder)
    {
    _ = migrationBuilder.IsSqlServer();
    migrationBuilder.CreateTable(
    name: "Pessoas",
    columns: table => new
    @@ -29,7 +28,6 @@ protected override void Up(MigrationBuilder migrationBuilder)

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    _ = migrationBuilder.IsSqlServer();
    migrationBuilder.DropTable(name: "Pessoas")
    .TemporalTable();
    }
  2. AlbertoMonteiro revised this gist Dec 12, 2021. 1 changed file with 10 additions and 10 deletions.
    20 changes: 10 additions & 10 deletions SampleMigration.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,8 +1,8 @@
    using System;
    using Microsoft.EntityFrameworkCore.Infrastructure;
    using Microsoft.EntityFrameworkCore.Migrations;
    using Microsoft.EntityFrameworkCore.Migrations.Operations;
    using Microsoft.EntityFrameworkCore.Migrations.Operations.Builders;
    using System;

    namespace ConsoleApp1.Migrations
    {
    @@ -37,24 +37,24 @@ protected override void Down(MigrationBuilder migrationBuilder)

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

    public static OperationBuilder<DropTableOperation> TemporalTable(this OperationBuilder<DropTableOperation> operation)
    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", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    .Annotation("SqlServer:TemporalPeriodEndColumnName", temporalPeriodEndColumnName)
    .Annotation("SqlServer:TemporalPeriodStartColumnName", temporalPeriodStartColumnName);

    public static CreateTableBuilder<T> TemporalTable<T>(this CreateTableBuilder<T> operation)
    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", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    .Annotation("SqlServer:TemporalPeriodEndColumnName", temporalPeriodEndColumnName)
    .Annotation("SqlServer:TemporalPeriodStartColumnName", temporalPeriodStartColumnName);
    }
    }
  3. AlbertoMonteiro revised this gist Dec 12, 2021. 1 changed file with 29 additions and 14 deletions.
    43 changes: 29 additions & 14 deletions SampleMigration.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,8 @@
    using System;
    using Microsoft.EntityFrameworkCore.Infrastructure;
    using Microsoft.EntityFrameworkCore.Migrations;
    using Microsoft.EntityFrameworkCore.Migrations.Operations;
    using Microsoft.EntityFrameworkCore.Migrations.Operations.Builders;

    namespace ConsoleApp1.Migrations
    {
    @@ -16,30 +19,42 @@ protected override void Up(MigrationBuilder migrationBuilder)
    .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)
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart"),
    .TemporalColumn(),
    PeriodStart = table.Column<DateTime>(type: "datetime2", nullable: false)
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
    .TemporalColumn()
    },
    constraints: table => table.PrimaryKey("PK_Pessoas", x => x.Id))
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "PessoasHistory")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    .TemporalTable();
    }

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

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

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

    public static CreateTableBuilder<T> TemporalTable<T>(this CreateTableBuilder<T> operation)
    => operation
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", $"{operation.GetInfrastructure().Name}History")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    }
    }
  4. AlbertoMonteiro revised this gist Dec 12, 2021. 2 changed files with 91 additions and 38 deletions.
    45 changes: 45 additions & 0 deletions SampleMigration.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    using System;
    using Microsoft.EntityFrameworkCore.Migrations;

    namespace ConsoleApp1.Migrations
    {
    public partial class Initial : Migration
    {
    protected override void Up(MigrationBuilder migrationBuilder)
    {
    _ = migrationBuilder.IsSqlServer();
    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)
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart"),
    PeriodStart = table.Column<DateTime>(type: "datetime2", nullable: false)
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
    },
    constraints: table => table.PrimaryKey("PK_Pessoas", x => x.Id))
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "PessoasHistory")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    _ = migrationBuilder.IsSqlServer();
    migrationBuilder.DropTable(
    name: "Pessoas")
    .Annotation("SqlServer:IsTemporal", true)
    .Annotation("SqlServer:TemporalHistoryTableName", "PessoasHistory")
    .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
    .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
    }
    }
    }
    84 changes: 46 additions & 38 deletions SqlServerWithTemporalTablesMigrationsSqlGenerator.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -11,8 +11,17 @@

    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(
    @@ -35,9 +44,9 @@ protected override void Generate(
    throw new ArgumentException(SqlServerStrings.CannotProduceUnterminatedSQLWithComments(nameof(CreateTableOperation)));
    }

    if (operation["SqlServer:IsTemporal"] as bool? == true)
    if (operation[SqlServerIsTemporal] as bool? == true)
    {
    var historyTableSchema = operation["SqlServer:TemporalHistoryTableSchema"] as string
    var historyTableSchema = operation[SqlServerTemporalHistoryTableSchema] as string
    ?? model?.GetDefaultSchema();
    var needsExec = historyTableSchema == null;
    var subBuilder = needsExec
    @@ -54,8 +63,8 @@ protected override void Generate(
    CreateTableColumns(operation, model, subBuilder);
    CreateTableConstraints(operation, model, subBuilder);
    subBuilder.AppendLine(",");
    var startColumnName = operation["SqlServer:TemporalPeriodStartColumnName"] as string;
    var endColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] as string;
    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})");
    @@ -74,7 +83,7 @@ protected override void Generate(
    .Append(execBody);
    }

    var historyTableName = operation["SqlServer:TemporalHistoryTableName"] as string;
    var historyTableName = operation[SqlServerTemporalHistoryTableName] as string;
    string historyTable;
    if (needsExec)
    {
    @@ -175,8 +184,8 @@ protected override void ColumnDefinition(
    .Append(operation.Collation);
    }

    var periodStartColumnName = operation["SqlServer:TemporalPeriodStartColumnName"] as string;
    var periodEndColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] as string;
    var periodStartColumnName = operation[SqlServerTemporalPeriodStartColumnName] as string;
    var periodEndColumnName = operation[SqlServerTemporalPeriodEndColumnName] as string;

    if (name == periodStartColumnName
    || name == periodEndColumnName)
    @@ -250,7 +259,7 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    availableSchemas.Add(ensureSchemaOperation.Name);
    }

    var isTemporal = operation["SqlServer:IsTemporal"] as bool? == true;
    var isTemporal = operation[SqlServerIsTemporal] as bool? == true;
    if (isTemporal)
    {
    string? table = null;
    @@ -263,11 +272,11 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    }

    schema ??= model?.GetDefaultSchema();
    var historyTableName = operation["SqlServer:TemporalHistoryTableName"] as string;
    var historyTableSchema = operation["SqlServer:TemporalHistoryTableSchema"] as string
    var historyTableName = operation[SqlServerTemporalHistoryTableName] as string;
    var historyTableSchema = operation[SqlServerTemporalHistoryTableSchema] as string
    ?? model?.GetDefaultSchema();
    var periodStartColumnName = operation["SqlServer:TemporalPeriodStartColumnName"] as string;
    var periodEndColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] as string;
    var periodStartColumnName = operation[SqlServerTemporalPeriodStartColumnName] as string;
    var periodEndColumnName = operation[SqlServerTemporalPeriodEndColumnName] as string;

    switch (operation)
    {
    @@ -311,7 +320,7 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    break;

    case AlterTableOperation alterTableOperation:
    var oldIsTemporal = alterTableOperation.OldTable["SqlServer:IsTemporal"] as bool? == true;
    var oldIsTemporal = alterTableOperation.OldTable[SqlServerIsTemporal] as bool? == true;
    if (!oldIsTemporal)
    {
    periodMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
    @@ -322,9 +331,9 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    else
    {
    var oldHistoryTableName =
    alterTableOperation.OldTable["SqlServer:TemporalHistoryTableName"] as string;
    alterTableOperation.OldTable[SqlServerTemporalHistoryTableName] as string;
    var oldHistoryTableSchema =
    alterTableOperation.OldTable["SqlServer:TemporalHistoryTableSchema"] as string
    alterTableOperation.OldTable[SqlServerTemporalHistoryTableSchema] as string
    ?? alterTableOperation.OldTable.Schema
    ?? model?[RelationalAnnotationNames.DefaultSchema] as string;

    @@ -366,12 +375,12 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    // 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["SqlServer:IsTemporal"] as bool? == true
    && alterColumnOperation.OldColumn["SqlServer:IsTemporal"] is null)
    if (alterColumnOperation[SqlServerIsTemporal] as bool? == true
    && alterColumnOperation.OldColumn[SqlServerIsTemporal] is null)
    {
    alterColumnOperation.RemoveAnnotation("SqlServer:IsTemporal");
    alterColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodStartColumnName");
    alterColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodEndColumnName");
    alterColumnOperation.RemoveAnnotation(SqlServerIsTemporal);
    alterColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodStartColumnName);
    alterColumnOperation.RemoveAnnotation(SqlServerTemporalPeriodEndColumnName);

    // TODO: test what happens if default value just changes (from temporal to temporal)
    }
    @@ -407,11 +416,11 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    // 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["SqlServer:IsTemporal"] as bool? == true)
    if (addColumnOperation[SqlServerIsTemporal] as bool? == true)
    {
    addColumnOperation.RemoveAnnotation("SqlServer:IsTemporal");
    addColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodStartColumnName");
    addColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodEndColumnName");
    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)
    @@ -433,17 +442,17 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    else
    {
    if (operation is AlterTableOperation alterTableOperation
    && alterTableOperation.OldTable["SqlServer:IsTemporal"] as bool? == true)
    && alterTableOperation.OldTable[SqlServerIsTemporal] as bool? == true)
    {
    var historyTableName = alterTableOperation.OldTable["SqlServer:TemporalHistoryTableName"] as string;
    var historyTableSchema = alterTableOperation.OldTable["SqlServer:TemporalHistoryTableSchema"] as string
    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["SqlServer:TemporalPeriodStartColumnName"] as string;
    alterTableOperation.OldTable[SqlServerTemporalPeriodStartColumnName] as string;
    var periodEndColumnName =
    alterTableOperation.OldTable["SqlServer:TemporalPeriodEndColumnName"] as string;
    alterTableOperation.OldTable[SqlServerTemporalPeriodEndColumnName] as string;

    DisableVersioning(alterTableOperation.Name, alterTableOperation.Schema, historyTableName!, historyTableSchema);
    DisablePeriod(alterTableOperation.Name, alterTableOperation.Schema, periodStartColumnName!, periodEndColumnName!);
    @@ -464,7 +473,7 @@ private IReadOnlyList<MigrationOperation> RewriteOperations(
    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?["SqlServer:IsTemporal"] as bool? != true
    if (alterColumnOperation.OldColumn?[SqlServerIsTemporal] as bool? != true
    || !CanSkipAlterColumnOperation(alterColumnOperation.OldColumn, alterColumnOperation))
    {
    operations.Add(operation);
    @@ -625,7 +634,6 @@ static bool ColumnPropertiesAreTheSame(ColumnOperation first, ColumnOperation se
    && first.IsDestructiveChange == second.IsDestructiveChange
    && first.IsFixedLength == second.IsFixedLength
    && first.IsNullable == second.IsNullable
    //&& first.IsReadOnly == second.IsReadOnly
    && first.IsRowVersion == second.IsRowVersion
    && first.IsStored == second.IsStored
    && first.IsUnicode == second.IsUnicode
    @@ -650,13 +658,13 @@ static bool ColumnOperationsOnlyDifferByTemporalTableAnnotation(ColumnOperation
    }

    return unmatched.All(
    a => a.Name is "SqlServer:IsTemporal"
    or "SqlServer:TemporalHistoryTableName"
    or "SqlServer:TemporalHistoryTableSchema"
    or "SqlServer:TemporalPeriodStartPropertyName"
    or "SqlServer:TemporalPeriodEndPropertyName"
    or "SqlServer:TemporalPeriodStartColumnName"
    or "SqlServer:TemporalPeriodEndColumnName");
    a => a.Name is SqlServerIsTemporal
    or SqlServerTemporalHistoryTableName
    or SqlServerTemporalHistoryTableSchema
    or SqlServerTemporalPeriodStartPropertyName
    or SqlServerTemporalPeriodEndPropertyName
    or SqlServerTemporalPeriodStartColumnName
    or SqlServerTemporalPeriodEndColumnName);
    }
    }
    }
  5. AlbertoMonteiro created this gist Dec 12, 2021.
    663 changes: 663 additions & 0 deletions SqlServerWithTemporalTablesMigrationsSqlGenerator.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,663 @@
    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 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["SqlServer:IsTemporal"] as bool? == true)
    {
    var historyTableSchema = operation["SqlServer:TemporalHistoryTableSchema"] 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["SqlServer:TemporalPeriodStartColumnName"] as string;
    var endColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] 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["SqlServer:TemporalHistoryTableName"] 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["SqlServer:TemporalPeriodStartColumnName"] as string;
    var periodEndColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] 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["SqlServer:IsTemporal"] 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["SqlServer:TemporalHistoryTableName"] as string;
    var historyTableSchema = operation["SqlServer:TemporalHistoryTableSchema"] as string
    ?? model?.GetDefaultSchema();
    var periodStartColumnName = operation["SqlServer:TemporalPeriodStartColumnName"] as string;
    var periodEndColumnName = operation["SqlServer:TemporalPeriodEndColumnName"] 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["SqlServer:IsTemporal"] as bool? == true;
    if (!oldIsTemporal)
    {
    periodMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
    (periodStartColumnName!, periodEndColumnName!);
    versioningMap[(alterTableOperation.Name, alterTableOperation.Schema)] =
    (historyTableName!, historyTableSchema);
    }
    else
    {
    var oldHistoryTableName =
    alterTableOperation.OldTable["SqlServer:TemporalHistoryTableName"] as string;
    var oldHistoryTableSchema =
    alterTableOperation.OldTable["SqlServer:TemporalHistoryTableSchema"] 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["SqlServer:IsTemporal"] as bool? == true
    && alterColumnOperation.OldColumn["SqlServer:IsTemporal"] is null)
    {
    alterColumnOperation.RemoveAnnotation("SqlServer:IsTemporal");
    alterColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodStartColumnName");
    alterColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodEndColumnName");

    // 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["SqlServer:IsTemporal"] as bool? == true)
    {
    addColumnOperation.RemoveAnnotation("SqlServer:IsTemporal");
    addColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodStartColumnName");
    addColumnOperation.RemoveAnnotation("SqlServer:TemporalPeriodEndColumnName");

    // 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["SqlServer:IsTemporal"] as bool? == true)
    {
    var historyTableName = alterTableOperation.OldTable["SqlServer:TemporalHistoryTableName"] as string;
    var historyTableSchema = alterTableOperation.OldTable["SqlServer:TemporalHistoryTableSchema"] as string
    ?? alterTableOperation.OldTable.Schema
    ?? model?[RelationalAnnotationNames.DefaultSchema] as string;

    var periodStartColumnName =
    alterTableOperation.OldTable["SqlServer:TemporalPeriodStartColumnName"] as string;
    var periodEndColumnName =
    alterTableOperation.OldTable["SqlServer:TemporalPeriodEndColumnName"] 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?["SqlServer:IsTemporal"] 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.IsReadOnly == second.IsReadOnly
    && 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 "SqlServer:IsTemporal"
    or "SqlServer:TemporalHistoryTableName"
    or "SqlServer:TemporalHistoryTableSchema"
    or "SqlServer:TemporalPeriodStartPropertyName"
    or "SqlServer:TemporalPeriodEndPropertyName"
    or "SqlServer:TemporalPeriodStartColumnName"
    or "SqlServer:TemporalPeriodEndColumnName");
    }
    }
    }
    }