I have created temporal tables based on Microsoft SQL Docs Creating a temporal table with a default history table.
Migration:
public partial class Temporaltables : Migration { List<string> tablesToUpdate = new List<string> { "Images", "Languages", "Questions", "Texts", "Medias", }; protected override void Up(MigrationBuilder migrationBuilder) { migrationBuilder.Sql($"CREATE SCHEMA History"); foreach (var table in tablesToUpdate) { string alterStatement = $@"ALTER TABLE [{table}] ADD SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_{table}_SysStart DEFAULT GETDATE(), SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_{table}_SysEnd DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59'), PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)"; migrationBuilder.Sql(alterStatement); alterStatement = $@"ALTER TABLE [{table}] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.[{table}]));"; migrationBuilder.Sql(alterStatement); } } protected override void Down(MigrationBuilder migrationBuilder) { foreach (var table in tablesToUpdate) { string alterStatement = $@"ALTER TABLE [{table}] SET (SYSTEM_VERSIONING = OFF);"; migrationBuilder.Sql(alterStatement); alterStatement = $@"ALTER TABLE [{table}] DROP PERIOD FOR SYSTEM_TIME"; migrationBuilder.Sql(alterStatement); alterStatement = $@"ALTER TABLE [{table}] DROP DF_{table}_SysStart, DF_{table}_SysEnd"; migrationBuilder.Sql(alterStatement); alterStatement = $@"ALTER TABLE [{table}] DROP COLUMN SysStartTime, COLUMN SysEndTime"; migrationBuilder.Sql(alterStatement); alterStatement = $@"DROP TABLE History.[{table}]"; migrationBuilder.Sql(alterStatement); } migrationBuilder.Sql($"DROP SCHEMA History"); } } Complete example how Temporal tables was set up:
https://stackoverflow.com/a/64244548/3850405
This works really well but now I want to access the value for SysStartTime.
What I have tried:
[DatabaseGenerated(DatabaseGeneratedOption.Computed)] public DateTime SysStartTime { get; set; } ApplicationDbContext.cs:
protected override void OnModelCreating(ModelBuilder modelBuilder) {... foreach (var et in modelBuilder.Model.GetEntityTypes()) { foreach (var prop in et.GetProperties()) { if (prop.Name == "SysStartTime" || prop.Name == "SysEndTime") { prop.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate; } } } and
modelBuilder.Entity<Question>(e => { e.Property(p => p.SysStartTime).ValueGeneratedOnAddOrUpdate(); }); Every migration leads to the following:
migrationBuilder.AddColumn<DateTime>( name: "SysStartTime", table: "Questions", type: "datetime2(0)", nullable: false, defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified)); This of course leads to the error below on Update-Database command:
Column names in each table must be unique. Column name 'SysStartTime' in table '' is specified more than once.
I have been reading both these questions and it seems to have been working in Entity Framework Core 2.2:
Net Core: Entity Framework and SQL Server Temporal Tables, Automatic Scaffolding
Entity Framework Core and SQL Server 2016 temporal tables
Tried disabling HIDDEN but it did not help
SQL:
ALTER TABLE [dbo].Questions ALTER COLUMN SysStartTime DROP HIDDEN; 
