领先技术 - 临时表的软更新

项目
03/15/2017

2017 年 3 月

第 32 卷，第 3 期

作者 Dino Esposito | 2017 年 3 月

Dino Esposito 当今绝大多数开发者都使用经典关系数据库来存储数据。尽管取而代之的无架构数据存储（统称为“NoSQL 存储”）在各种业务方案中都证明相当有效，但经典关系数据库是沿用了几十年且目前仍适用的方法。每次更新现有表记录时，都不会自动保存旧状态。虽然目前替代现有数据尚不是各家公司需要面临的一大难题，但事态瞬息万变。如今，数据是每家公司的最宝贵资产，也是商业智能输入。

在本专栏的 2016 年 5 月和 6 月刊文章（网址分别为 msdn.com/magazine/mt703431 和 msdn.com/magazine/mt707524）中，我介绍了一种常规用途方法，即通过软更新和删除来提升创建、读取、更新和删除 (CRUD) 系统。软更新是一种标准更新操作，通过某种方式保留记录旧状态的做法除外。这样一来，就会有额外 API 来检索在系统生存期内创建的每个实体的历史记录。

能够解决更新和删除记录，同时保留记录旧状态这一问题的方法不是太多。对于删除操作，这意味着添加额外一列（通常是布尔列）将记录标记为已删除。对于更新操作，最实用的方法是单独创建和维护与跟踪相关的每个记录的历史记录表。为了让数据和历史记录表保持同步，需要使用其他业务和数据访问逻辑，以及用于查询历史记录的专用 API。

ANSI SQL 2011 标准已确定如何管理关系表中的历史记录数据。最新版 SQL Server 支持一项称为临时表的功能，可方便你创建和管理每个选定数据表的影子历史记录表。在本月的这期专栏中，我将深入探讨 SQL Server 2016 临时表及其在实体框架 (EF) 中的应用。

临时表的结构

从概念上来讲，临时数据库与数据库的主要区别在于，经典数据库仅存储当前最新的数据。相反，临时数据库维护各个数据的多个副本。临时表追加了额外几个时间列来指明记录何时处于给定状态。图 1 展示了 SQL Server 2016 Management Studio 中的临时表。

图 1：SQL Server 2016 中的临时表

图中有几个值得注意的地方。一个是 dbo.BookingsHistory 子历史记录表。每当处理用于创建临时表的 T-SQL 指令时，SQL Server 都会自动创建此表。开发者对历史记录表只有读取权限。图 1 中另一个值得注意的地方是，选定表的上下文菜单中没有删除命令。对已创建的临时表进一步执行的所有操作都被严格控制，在某些情况下还是受限的，无论操作是通过 SQL Server Management Studio 界面执行，还是以编程方式执行。例如，无法删除或复制临时表，级联更新和删除也存在限制。若要详细了解影响 SQL Server 2016 临时表的限制，请访问 bit.ly/2iahP1n。

在 SQL Server 2016 中，可以在 CREATE TABLE 指令的末尾使用特殊子句创建临时表。临时表的状态归结起来就是启用和禁用新 SYSTEM_VERSIONING 设置的值。换言之，可以编程方式将所有表都转换成临时表，然后随时恢复原来的非临时状态。之前提到的影响临时表的所有限制在 SYSTEM_VERSIONING 设置遭到禁用后不复存在。

临时表和实体框架

当前许多开发者通过 EF 和（尤其是）EF Code First 服务来使用 SQL Server。然而，EF 暂未提供任何特殊的临时表支持。将在不久的将来提供临时支持。值得庆幸的是，EF 6.x 当前版本仍能提供一些对临时表的基本支持，EF Core 提供的支持更多。不幸的是，实际上只有通过对框架进行低级别的更改，才能实现与 LINQ-to-Entities 的完全集成，尤其是 LINQ-to-Entities 提供程序生成查询 SQL 代码的方式。如果你是 SQL 开发者，最新的 T-SQL 语言提供了操作临时表所需的全部语法工具。

对于 EF 开发者，最先进的临时表可以总结如下：首先，在 Code First 中创建临时表非常容易。其次，可以通过 EF 按常规方式执行更新和删除操作。第三，需要创建临时设施，才能执行查询。

我发现最有效的临时表查询方法是根据 ADO.NET 代码使用一小组临时存储库方法。这乍一听可能会令人诧异，但到了最后，如果需要临时表，很有可能还是最需要获得某个特定实体的历史记录。例如，获得订单或发票的所有更改。

因此，说到底还是只需便捷专用且直接为聚合公开的 FirstOrDefault-like 方法。并且我认为存储库类就是很好的容器。

启用临时表的初始化表达式

在 EF Code First 中，只要数据库不存在且 DbContext 类继承自 CreateDatabaseIfNotExists，就会创建新的数据库。这样一来，可以为每个声明的 DbSet 属性新建一个表。能否同时创建临时表？目前，如果不使用临时属性和语法功能，可以执行两步操作来创建临时表。第一步，必须创建常规表；这其实是 Code First 通常所执行的操作。第二步，启用 SYSTEM_VERSIONING 设置。这需要使用临时 ALTER TABLE 语句。图 2 展示了初始化表达式类的 Seed 方法的可能实现，用于先检查基础 SQL Server 的版本，然后更改以前创建的 Booking 表的状态。

图 2：通过 Code First 创建临时表

protected override void Seed(DbContext context)
{
  // Grab the SQL Server version number
  var data = context
    .Database
    .SqlQuery<string>(@"select
  left(cast(serverproperty('productversion')
       as varchar), 4)")
    .FirstOrDefault();
  if (data != null)
  {
    var version = data.ToInt();+
    if (version < 13)
      throw new Exception("Invalid version of SQL Server");
  }
  // Prepare the SQL to turn system versioning on SQL Server 2016
  var cmd = String.Format(SqlSystemVersionedDbFormat, "Bookings");
  context.Database.ExecuteSqlCommand(cmd);
}

图 3 展示了创建临时表所需的实际 T-SQL 命令（或 SQL Server 2016 术语中引用的带有系统版本的表）。

图 3：创建临时表

private const string SqlSystemVersionedDbFormat =
  @"ALTER TABLE dbo.{0}
    ADD SysStartTime datetime2(0)
    GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT
      DF_{0}_SysStart DEFAULT SYSUTCDATETIME(),
    SysEndTime datetime2(0)
    GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT
      DF_{0}_SysEnd DEFAULT CONVERT(datetime2 (0),
      '9999-12-31 23:59:59'),
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
  ALTER TABLE dbo.{0}
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.{0}History))";

图 3 字符串中的 {0} 占位符指的是实际表的名称。表名称为 Booking，如图 1 所示。

生成的历史记录表是主表的副本，外加一对名为 SysStartTime 和 SysEndTime 的 datetime2 列。总体来说，这两列指明了记录的特定状态的有效期。SysStartTime 指明记录何时处于给定状态，SysEndTime 指明状态的有效期何时终止。更新和删除是导致 SysStartTime 和 SysEndTime 值发生变化的数据库操作。

更新和删除

SQL Server 2016 数据库引擎支持不断同步主表及其历史记录的逻辑。无论你如何执行更新，只要主表中的记录有所更新，都会新建历史记录。换言之，无论是直接在 Management Studio 中编辑临时记录的值，还是通过存储过程、ADO.NET 命令或 EF 进行编辑，都会新建历史记录，如图 4 所示。

图 4：更新临时表中的记录

图 4 中的第一个查询表示 ID 为 2 的记录的当前状态。相反，第二个查询表示在历史记录表中找到的 ID 相同的记录。此类可观测的状态是由我直接在 Management Studio 编辑器中执行的两次快速更新决定的。我先将“工时”列的值从 9 更改为 13，然后在几秒钟后，又将“所有者”列的值从 Dino 更改为 Paul。历史记录表中的第一条记录表明，最初创建的记录（我通过 EF 以及调用 SaveChanges 在表中创建）在约五分钟内处于有效状态。然后，记录处于另一种状态几秒钟，最终处于当前状态。如你所见，当前状态没有存储在历史记录表中。图 5 展示了在删除 ID 为 2 的记录后表的状态。

图 5：删除临时表中的记录

查询 ID 为 2 的记录时，主表返回空结果集。而现在历史记录表有第三条记录，从上次更新到执行删除时有效。

查询特定实体

跟踪所有状态更改非常有用，因为这样就不会错过系统中发生的任何一件事。你可以查看所有数据库操作的完整（和可用）日志。更棒的是，这样可以生成状态更改的完整列表，这具有比 SQL 语句的普通日志更为相关的业务角色。换言之，从概念上来说，临时表非常接近事件溯源；我敢说，临时表是基于 CRUD 的事件溯源形式。让我们来看看如何查询给定聚合的过往状态。

尽管历史记录嵌套表就能够进行查询，但 SQL Server 2016 仍提供了用于查询给定记录的临时数据的直接语法。下面展示了用于在给定时间间隔内检索 ID 为 2 的记录版本的示例命令架构：

var sql = @"SELECT * FROM Bookings 
  FOR SYSTEM_TIME BETWEEN '{0}' AND '{1}'
  WHERE ID=2";

临时表是常规查询，外加设置要考虑的时间间隔的 FOR SYSTEM_TIME 子句。数据库引擎会通过查看历史记录表中的其他列以及主表和嵌套表的内容来解析查询。查询应返回记录列表。如何强制 EF 像这样运行查询？在 EF 6 中，只能利用 DbSet 类的 SqlQuery 方法：

using (var db = new EF6Context())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .SqlQuery("SELECT * FROM dbo.Bookings
          FOR SYSTEM_TIME AS OF {0} WHERE Id = 1", time)
    .SingleOrDefault();
}

请注意，对于 EF 6，查询中返回的列名必须与类中的属性名匹配。这是因为 SqlQuery 不使用映射。如果列名和属性名不匹配，需要在 SELECT 列表中为列指定别名，而不直接使用 SELECT *。

使用 EF Core，在某些方面操作会变得更为简单。在 EF Core 中，使用的方法是 FromSql。首先，FromSql 方法使用映射；也就是说，如果列名和属性名不匹配，也不用担心要指定别名：

using (var db = new EFCoreContext())
{
  var current = db.Bookings.Single(b => b.Id == 1);
  var time = DateTime.Now.AddMinutes(-5);
  var old = db.Bookings
    .FromSql("SELECT * FROM dbo.Bookings
              FOR SYSTEM_TIME AS OF {0}", time)
    .SingleOrDefault(b => b.Id == 1);
}

其次，可以使用 LINQ 在初始选择的基础上进行撰写。也就是说，可以使用 Where、OrderBy、GroupBy 或其他任何 LINQ 运算符，这些运算符通常会转换成窗体查询：

SELECT projection
FROM (SELECT * FROM dbo.Bookings FOR SYSTEM_TIME AS OF {0}) as Bookings
WHERE condition

尽管如此，如果你愿意，可以始终使用普通 ADO.NET 和数据读取器来访问临时表中存储的数据。

总结

当然可以在大部分基于 EF 的数据层中使用临时表，即使偶尔使用普通 ADO.NET 进行查询，也可以利用 LINQ-to-Objects 提高内存复杂度。实体框架团队路线图表明，在接下来的几个月里，要对临时表实施一些工作项。那么，让我们拭目以待吧。

Dino Esposito*是《Microsoft .NET：构建面向企业的应用程序》（Microsoft Press，2014 年）和《使用 ASP.NET 构建新型 Web 应用程序》（Microsoft Press，2016 年）的作者。Esposito 是 JetBrains 公司 .NET 和 Android 平台的技术推广专家，经常在全球性行业活动上发表演讲，他在 software2cents.wordpress.com 和 Twitter: @despos.*上分享了他的软件构想。

衷心感谢以下 Microsoft 技术专家对本文的审阅： Rowan Miller

临时表的结构

临时表和实体框架

启用临时表的初始化表达式

更新和删除

查询特定实体

总结

其他资源