Entity Framework Core 3.1 with Temporal Tables - Access SysStartTime and SysEndTime

[Ogglas]

I have created temporal tables based on Microsoft SQL Docs Creating a temporal table with a default history table.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#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:

https://stackoverflow.com/q/62379060/3850405

https://stackoverflow.com/q/42627585/3850405

Tried disabling HIDDEN but it did not help

SQL:

ALTER TABLE [dbo].Questions ALTER COLUMN SysStartTime DROP HIDDEN;

Include provider and version information

EF Core version: 3.1.6
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1
Operating system: Windows 10 Enterprise X64, OS Build 19041.572
IDE: Visual Studio 2019 16.7.7

[ajcvickers]

@Ogglas EF Core should work fine mapping those properties to your existing temporal table, since it has has those columns created automatically. However, EF migrations do not know that these columns already exist, so if you add properties to your model, then migrations is going to try create columns for you. You should be able to just edit the migration and remove the code that creates the columns.

[Ogglas]

@ajcvickers Thanks! For tables that already had the columns I simply removed Up and Down values for the migration and then it worked like you said:

migrationBuilder.AddColumn<DateTime>(
    name: "SysStartTime",
    table: "Questions",
    type: "datetime2(0)",
    nullable: false,
    defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));

I do not want to edit default code generated by migrations later on so I decided to do it like this for new entities:

Add DateTime values normally to migration:

public DateTime SysStartTime { get; set; }

public DateTime SysEndTime { 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;
            }
        }
    }

Migration, if you already have Schema History then remove those rows:

public partial class Temporaltables : Migration
{
    List<string> tablesToUpdate = new List<string>
        {
           "NewTable1",
           "NewTable2",
        };

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql($"CREATE SCHEMA History");
        foreach (var table in tablesToUpdate)
        {
            string alterStatement = $@"ALTER TABLE [{table}] 
                ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])";
            migrationBuilder.Sql(alterStatement);
            alterStatement = $@"ALTER TABLE [{table}] 
                SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.[{table}], DATA_CONSISTENCY_CHECK = ON));";
            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 = $@"DROP TABLE History.[{table}]";
            migrationBuilder.Sql(alterStatement);
        }
        migrationBuilder.Sql($"DROP SCHEMA History");
    }
}

Question on Stackoverflow: https://stackoverflow.com/q/64653497/3850405