SQL Server/Azure SQL 时态表

项目
08/24/2023

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 创建两个名为 PeriodEnd 和 PeriodStart 的隐藏 datetime2 列。这些“时间段列”表示行中的数据存在的时间范围。这些列会映射到 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
    });

context.SaveChanges();

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

var employee = context.Employees.Single(e => e.Name == "Rainbow Dash");
context.Remove(employee);
context.SaveChanges();

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

var employees = context.Employees.ToList();
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 = 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")
        })
    .ToList();

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 停止活动。这是因为在该时间从主表中删除了 Rainbow Dash 行。稍后我们将介绍如何还原此数据。

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

var history = 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")
        })
    .ToList();

此查询会返回以下行：

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

还原历史数据

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

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

context.Add(employee);
context.SaveChanges();

此查询会返回指定 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

SQL Server/Azure SQL 时态表

配置时态表

使用时态表

查询历史数据

还原历史数据

反馈

其他资源