带有时态表的 Entity Framework Core 3.1 - 访问 SysStartTime 和 SysEndTime

我已经基于 Microsoft SQL 文档创建了时态表使用默认历史表创建临时表.

https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#creating-a-temporal-table-带有默认历史表 https://learn.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

移民：

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");
    }
}

如何设置时态表的完整示例：

https://stackoverflow.com/a/64244548/3850405 https://stackoverflow.com/a/64244548/3850405

这非常有效，但现在我想访问的值SysStartTime.

我尝试过的：

[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();
});

每次迁移都会导致以下结果：

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

这当然会导致以下错误Update-Database命令：

每个表中的列名必须是唯一的。列名称“SysStartTime”位于 表 '' 被指定多次。

我一直在阅读这些问题，它似乎一直在 Entity Framework Core 2.2 中工作：

Net Core：实体框架和 SQL Server 时态表、自动脚手架 https://stackoverflow.com/q/62379060/3850405

Entity Framework Core 和 SQL Server 2016 时态表 https://stackoverflow.com/q/42627585/3850405

尝试禁用隐藏但没有帮助

SQL:

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

对于已经具有我简单删除的列的表Up and Down迁移的值然后它起作用了：

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

由于我不想稍后编辑迁移生成的默认代码，因此我决定对新实体这样做：

Add DateTime通常迁移的值：

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;
            }
        }
    }

迁移，如果您已经有Schema History然后删除这些行：

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");
    }
}

GitHub 上的讨论：

https://github.com/dotnet/efcore/issues/23184 https://github.com/dotnet/efcore/issues/23184