Entity Framework Core (EFCore) Enable System-Versioned Temporal Table with Code First

EnableSystemVersionedTemporalTables.cs

namespace EFCoreEnableSystemVersionedTemporalTables
{
    public static class EnableSystemVersionedTemporalTables
    {
        private static readonly (string SysStartTimeString, string SysEndTimeString) TemporalTableAdditionalFileds = (SysStartTimeString: "SysStartTime", SysEndTimeString: "SysEndTime");

        public static void AddAsSystemVersionedTemporalTable(this MigrationBuilder migrationBuilder, string tableName, string temporalScheme = null, string temporalTableSuffix = @"History")
        {
            var temporalTableName = $"{tableName}{temporalTableSuffix}";
            var schemaName = temporalScheme ?? "dbo";

            migrationBuilder.Sql($@"
                    IF NOT EXISTS (SELECT * FROM sys.[tables] t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = '{tableName}' AND temporal_type = 2 and s.name = '{schemaName}')
                    BEGIN
                        ALTER TABLE [{schemaName}].[{tableName}]   
                        ADD   {TemporalTableAdditionalFileds.SysStartTimeString} datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN constraint DF_{tableName}_{TemporalTableAdditionalFileds.SysStartTimeString} DEFAULT DATEADD(second, -1, SYSUTCDATETIME())  
                            , {TemporalTableAdditionalFileds.SysEndTimeString}   datetime2 (0) GENERATED ALWAYS AS ROW END   HIDDEN constraint DF_{tableName}_{TemporalTableAdditionalFileds.SysEndTimeString}   DEFAULT '9999.12.31 23:59:59.99'  
                            , PERIOD FOR SYSTEM_TIME ({TemporalTableAdditionalFileds.SysStartTimeString}, {TemporalTableAdditionalFileds.SysEndTimeString});   
 
                        ALTER TABLE [{schemaName}].[{tableName}]
                        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [{temporalScheme}].[{temporalTableName}])); 
                    END
                ");
        }
        
        public static void RemoveAsSystemVersionedTemporalTable(this MigrationBuilder migrationBuilder, string tableName, string temporalScheme = null, string temporalTableSuffix = @"History")
        {
            var temporalTableName = $"{tableName}{temporalTableSuffix}";
            var schemaName = temporalScheme ?? "dbo";

            var alterStatement = $@"ALTER TABLE [{tableName}] SET (SYSTEM_VERSIONING = OFF);";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{tableName}] DROP PERIOD FOR SYSTEM_TIME";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{tableName}] DROP CONSTRAINT DF_{tableName}_{TemporalTableAdditionalFileds.SysStartTimeString}, DF_{tableName}_{TemporalTableAdditionalFileds.SysEndTimeString}";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{tableName}] DROP COLUMN {TemporalTableAdditionalFileds.SysStartTimeString}, COLUMN {TemporalTableAdditionalFileds.SysEndTimeString}";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"DROP TABLE [{schemaName}].[{temporalTableName}]";
            migrationBuilder.Sql(alterStatement);
        }
    }
    
    /// Samples. Try modify your `Migrations`.
    /// 1. Do `Add-Migration 'YourMigrationSubject'`  e.g. `Add-Migration AddTemporalTableSupport`
    /// 2. Modify yyyyMMddHHmmss_'YourMigrationSubject'.cs in Migrations.  e.g. `20190117123839_AddTemporalTableSupport.cs`
    /// 3. Do `Update-Database`.
    public partial class AddTemporalTableSupport : Migration
    {
        const string SystemVersionedTemporalTableSchemaName = "dbo";

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(TemporalTableSchemaName);
            migrationBuilder.AddAsSystemVersionedTemporalTable(nameof(<YourTable>), SystemVersionedTemporalTableSchemaName);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(TemporalTableSchemaName);
            migrationBuilder.RemoveAsSystemVersionedTemporalTable(nameof(<YourTable>), SystemVersionedTemporalTableSchemaName);
        }
    }
}