SQL Server/Azure SQL 时态表

即使数据已更新或删除，SQL Server 临时表也会自动跟踪表中存储的所有数据。这是通过创建并行的“历史记录表”来实现的，每当对主表进行更改时，将存储带时间戳的历史数据。这样就可以查询历史数据，例如进行审核或还原，例如在意外突变或删除后进行恢复。

EF Core 支持：

使用EF Core 迁移创建时间表
使用迁移再次将现有表转换为临时表
查询历史数据
从过去某个时间点恢复数据以重置到原始状态

配置时态表

模型生成器可用于将表配置为临时表。例如：

modelBuilder
    .Entity<Employee>()
    .ToTable("Employees", b => b.IsTemporal());

小窍门

此处显示的代码来自 TemporalTablesSample.cs。

使用 EF Core 创建数据库时，新表配置为具有时间戳和历史记录表的 SQL Server 默认值的临时表。例如，请考虑实体 Employee 类型：

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
}

创建的临时表如下所示：

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistory]))');

请注意，SQL Server 会创建两个隐藏列，分别名为datetime2和PeriodEnd。这些“时间段列”表示行中的数据存在的时间范围。这些列映射到 EF Core 模型中的阴影属性，允许它们在查询中使用，如下所示。

重要

这些列中的时间始终是 SQL Server 生成的 UTC 时间。 UTC 时间用于涉及时间表的所有操作，例如在如下所示的查询中。

另请注意，一个名为EmployeeHistory的关联历史记录表会自动创建。可以通过对模型构建器进行额外配置来更改时间段列和历史记录表的名称。例如：

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart("ValidFrom");
                b.HasPeriodEnd("ValidTo");
                b.UseHistoryTable("EmployeeHistoricalData");
            }));

这反映在 SQL Server 创建的表中：

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistoricalData]))');

使用时态表

大多数情况下，临时表的使用方式与任何其他表一样。也就是说，SQL Server 会透明地处理期间列和历史数据，使应用程序可以忽略它们。例如，可以正常方式将新实体保存到数据库：

context.AddRange(
    new Employee
    {
        Name = "Pinky Pie",
        Address = "Sugarcube Corner, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Party Organizer",
        AnnualSalary = 100.0m
    },
    new Employee
    {
        Name = "Rainbow Dash",
        Address = "Cloudominium, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Ponyville weather patrol",
        AnnualSalary = 900.0m
    },
    new Employee
    {
        Name = "Fluttershy",
        Address = "Everfree Forest, Equestria",
        Department = "DevDiv",
        Position = "Animal caretaker",
        AnnualSalary = 30.0m
    });

await context.SaveChangesAsync();

然后，可以按正常方式查询、更新和删除此数据。例如：

var employee = await context.Employees.SingleAsync(e => e.Name == "Rainbow Dash");
context.Remove(employee);
await context.SaveChangesAsync();

此外，进行正常的跟踪查询后，可以从跟踪实体访问当前数据中的时间列的值。例如：

var employees = await context.Employees.ToListAsync();
foreach (var employee in employees)
{
    var employeeEntry = context.Entry(employee);
    var validFrom = employeeEntry.Property<DateTime>("ValidFrom").CurrentValue;
    var validTo = employeeEntry.Property<DateTime>("ValidTo").CurrentValue;

    Console.WriteLine($"  Employee {employee.Name} valid from {validFrom} to {validTo}");
}

这将打印：

Starting data:
  Employee Pinky Pie valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Rainbow Dash valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Fluttershy valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM

请注意， ValidTo 列（默认情况下调用 PeriodEnd）包含 datetime2 最大值。对于表格中的当前行，情况始终如此。默认情况下称为ValidFrom的PeriodStart列包含插入该行的 UTC 时间。

查询历史数据

EF Core 支持通过多个专用查询运算符包含历史数据的查询：

TemporalAsOf：返回给定 UTC 时间处于活动状态（当前）的行。这是给定主键的当前表或历史记录表中的单个行。
TemporalAll：返回历史数据中的所有行。这通常是历史记录表和/或给定主键的当前表中的许多行。
TemporalFromTo：返回在两个给定 UTC 时间之间处于活动状态的所有行。这可能是历史记录表和/或给定主键的当前表中的许多行。
TemporalBetween：与 TemporalFromTo 相同，除了包括在上边界上变为活动状态的行。
TemporalContainedIn：返回开始处于活动状态且在两个给定 UTC 时间之间结束处于活动状态的所有行。这可能是历史记录表和/或给定主键的当前表中的许多行。

注释

有关每个运算符包含哪些行的详细信息，请参阅 SQL Server 临时表文档。

例如，在对数据进行一些更新和删除后，可以使用查询 TemporalAll 来查看历史数据：

var history = await context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

foreach (var pointInTime in history)
{
    Console.WriteLine(
        $"  Employee {pointInTime.Employee.Name} was '{pointInTime.Employee.Position}' from {pointInTime.ValidFrom} to {pointInTime.ValidTo}");
}

请注意如何使用 EF.Property 方法来访问时间列中的值。 OrderBy 条款用于对数据进行排序，然后在映射中将这些值包含在返回的数据中。

此查询返回以下数据：

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM

请注意，返回的最后一行在 2021/8/26 下午 4：44：59 停止处于活动状态。这是因为当时从主表中删除了彩虹小马的行。稍后我们将了解如何还原此数据。

可以编写类似的查询，使用TemporalFromTo、TemporalBetween或TemporalContainedIn。例如：

var history = await context
    .Employees
    .TemporalBetween(timeStamp2, timeStamp3)
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

此查询返回以下行：

Historical data for Rainbow Dash between 8/26/2021 4:41:14 PM and 8/26/2021 4:42:44 PM:
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM

还原历史数据

如上所述，Rainbow Dash 已从 Employees 表中删除。这显然是一个错误，所以让我们回到某个时间点，并从该时间点还原缺少的行。

var employee = await context
    .Employees
    .TemporalAsOf(timeStamp2)
    .SingleAsync(e => e.Name == "Rainbow Dash");

context.Add(employee);
await context.SaveChangesAsync();

此查询返回在给定 UTC 时间的 Rainbow Dash 的单行。默认情况下，使用时态运算符的所有查询都不会跟踪，因此不会跟踪此处返回的实体。这很有意义，因为它当前不存在于主表中。若要将实体重新插入主表中，只需将其 Added 标记为然后调用 SaveChanges。

重新插入行Rainbow Dash后，查询历史数据显示该行在给定的 UTC 时间被还原：

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:44:59 PM to 12/31/9999 11:59:59 PM

反馈

此页面是否有帮助？

Last updated on 2025-06-09