private static void RemoveAsTemporalTable(CrocusoftProjeDbContext context, IEntityType entityType)
{
var tableName = entityType.GetTableName();
var schemaName = entityType.GetSchema() ?? "dbo";
var query = RemoveAsTemporalTableSql(tableName, schemaName);
context.Database.ExecuteSqlRaw(query);
}
public static void CreateTemporalTables(CrocusoftProjeDbContext context)
{
var entityTypes = context.Model.GetEntityTypes();
var config = new TemporalTableConfiguration();
foreach (var entityType in entityTypes)
{
if (config.TemporalTypes.Contains(entityType.ClrType))
{
AddAsTemporalTable(context, entityType);
}
else
{
RemoveAsTemporalTable(context, entityType);
}
}
}
private static void AddAsTemporalTable(CrocusoftProjeDbContext context, IEntityType entityType)
{
var tableName = entityType.GetTableName();
var temporalTableName = GetTemporalTableName(entityType);
var schemaName = entityType.GetSchema() ?? "dbo";
string query = $@"
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 SysStartTime datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
constraint DF_{tableName}_SysStartTime DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
, SysEndTime datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
constraint DF_{tableName}_SysEndTime DEFAULT '9999-12-31 23:59:59.9999999'
, PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
ALTER TABLE [{schemaName}].[{tableName}]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [{schemaName}].{temporalTableName}));
END
";
context.Database.ExecuteSqlRaw(query);
}
