带有时态表的实体框架核心3.1-访问SysStartTime和SysEndTime

Question

我已经基于Microsoft SQL Docs创建了一个具有默认历史记录表的时态表。

https://learn.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-ver15#创建具有默认历史表的临时表

迁移：

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

这非常有效，但现在我想访问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;
}
}
}

和

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"表"被指定了多次。

我一直在阅读这两个问题，它似乎一直在实体框架核心2.2:中工作

Net Core：实体框架和SQL Server时态表，自动支架

实体框架核心和SQL Server 2016时态表

尝试禁用HIDDEN，但对没有帮助

SQL:

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

Answer 1

对于已经具有列的表，我只需删除Up和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));

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

将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