带有时态表的Entity Framework Core 3.1-访问SysStartTime和SysEndTime [英] Entity Framework Core 3.1 with Temporal Tables - Access SysStartTime and SysEndTime
问题描述
我已经基于Microsoft SQL文档创建了时态表.使用默认历史记录表创建时态表.
I have created temporal tables based on Microsoft SQL Docs 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");
}
}
关于如何建立时态表的完整示例:
Complete example how Temporal tables was set up:
https://stackoverflow.com/a/64244548/3850405
这确实很好，但是现在我想访问 SysStartTime 的值.
This works really well but now I want to access the value for SysStartTime.
我尝试过的事情:
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime SysStartTime { get; set; }
ApplicationDbContext.cs:
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();
});
每次迁移都会导致以下结果:
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));
这当然会导致在 Update-Database 命令上出现以下错误:
This of course leads to the error below on Update-Database command:
每个表中的列名必须唯一.中的列名"SysStartTime"表"已多次指定.
Column names in each table must be unique. Column name 'SysStartTime' in table '' is specified more than once.
我一直在阅读这两个问题，并且似乎已经在Entity Framework Core 2.2中运行:
I have been reading both these questions and it seems to have been working in Entity Framework Core 2.2:
尝试禁用隐藏"功能，但无济于事
Tried disabling HIDDEN but it did not help
SQL:
ALTER TABLE [dbo].Questions ALTER COLUMN SysStartTime DROP HIDDEN;
推荐答案
对于已经具有列的表，我只是删除了 Up 和 Down 值以进行迁移，然后它起作用了:
For tables that already had the columns I simply removed Up and Down values for the migration and then it worked:
migrationBuilder.AddColumn<DateTime>(
name: "SysStartTime",
table: "Questions",
type: "datetime2(0)",
nullable: false,
defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
由于我以后不想编辑迁移生成的默认代码，因此决定对新实体执行以下操作:
Since I do not want to edit default code generated by migrations later on I decided to do it like this for new entities:
通常将 DateTime 值添加到迁移中:
Add DateTime values normally to migration:
public DateTime SysStartTime { get; set; }
public DateTime SysEndTime { get; set; }
ApplicationDbContext.cs:
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 ，则删除这些行:
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");
}
}
在GitHub上的讨论:
Discussion on GitHub:
https://github.com/dotnet/efcore/issues/23184
这篇关于带有时态表的Entity Framework Core 3.1-访问SysStartTime和SysEndTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
