ABP VNext + SQL Server Temporal Tables：审计与时序数据管理

ABP VNext + SQL Server Temporal Tables：审计与时序数据管理 🚀

---

---

📝 一、引言

TL;DR

1. ✨ SQL Server 原生时序表 + EF Core 一行 Fluent API，自动记录实体历史
2. 🔒 零侵入：无需触发器/手写审计表；F12 级调试可见版本化
3. 🕰️ “时间旅行”查询 + 全量历史枚举，支持分页 & 流式返回
4. 🛠️ 支持 SQL Server 2017+ 保留策略、Standard 版行/页压缩、分区滑动窗口清理等高级优化 (Microsoft Learn)

背景与动机

• 传统审计：分散触发器 & 手写历史表，易漏失、难维护
• SQL Server 2016+ 系统版本化表（System-Versioned Temporal Tables）内置行级版本管理
• EF Core 6.0+ + ABP VNext，几行配置即可贯通 DB→ORM→API 全链路 (Microsoft for Developers)

---

🔧 二、环境与依赖

• .NET：6.x

• ABP：VNext 6.x

• EF Core：Microsoft.EntityFrameworkCore.SqlServer ≥ 6.0 (Microsoft for Developers)

• SQL Server：

  • 基础功能：2016+
  • 保留策略 (HISTORY_RETENTION_PERIOD)：2017+ (Microsoft Learn)
  • 行/页压缩：2016 SP1+ Standard/Enterprise 均可

• NuGet：

  dotnet add package Microsoft.EntityFrameworkCore.SqlServer
  dotnet add package Volo.Abp.EntityFrameworkCore
  

---

📊 三、流程图示

---

🔨 四、启用时序表的数据库准备

4.1 手动 SQL（2016+ 通用）

ALTER TABLE dbo.Orders
ADDSysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN,SysEndTime   datetime2 GENERATED ALWAYS AS ROW END   HIDDEN,PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);ALTER TABLE dbo.Orders
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

⚠️ 若已有数据或外键、约束，建议先备份或手动迁移旧历史。

4.2 EF Core 迁移自动化（推荐）

在自定义 YourDbContext : AbpDbContext<YourDbContext> 中：

protected override void OnModelCreating(ModelBuilder builder)
{base.OnModelCreating(builder); // 确保 ABP & 插件默认配置生效builder.Entity<Order>(b =>{b.ToTable("Orders", tb => tb.IsTemporal(ttb =>{ttb.HasHistoryTable("OrdersHistory");ttb.HasPeriodStart("SysStartTime");ttb.HasPeriodEnd("SysEndTime");}));});
}

dotnet ef migrations add EnableOrderTemporal
dotnet ef database update

— EF Core 将生成并执行等效的 SQL 脚本。

---

🧩 五、在 ABP 项目中集成

• 📂 集中化配置：将所有 IsTemporal(...) 逻辑放在 YourDbContext.OnModelCreating
• 🔗 无 Module 改动：ABP 自带 EF Core 支持，时序表配置自动纳入迁移脚本

---

⚙️ 六、自动审计与版本查询 API

6.1 时间旅行查询

public async Task<OrderDto> GetSnapshotAsync(Guid orderId,DateTime asOf,CancellationToken cancellationToken)
{try{return await _dbContext.Orders.TemporalAsOf(asOf).Where(o => o.Id == orderId).Select(o => new OrderDto {Id        = o.Id,Status    = o.Status,Timestamp = EF.Property<DateTime>(o, "SysStartTime")}).FirstOrDefaultAsync(cancellationToken).ConfigureAwait(false);}catch (OperationCanceledException){throw new UserFriendlyException("请求已取消");}catch (InvalidOperationException ex){_logger.LogError(ex, "TemporalAsOf 查询异常");throw new UserFriendlyException("查询历史快照失败");}
}

6.2 全历史枚举（分页 & 流式）

public IAsyncEnumerable<OrderHistoryDto> StreamHistoryAsync(Guid orderId,CancellationToken cancellationToken)
{return _dbContext.Orders.TemporalAll().Where(o => o.Id == orderId).OrderBy(o => EF.Property<DateTime>(o, "SysStartTime")).Select(o => new OrderHistoryDto {Id           = o.Id,Status       = o.Status,SysStartTime = EF.Property<DateTime>(o, "SysStartTime"),SysEndTime   = EF.Property<DateTime>(o, "SysEndTime")}).AsAsyncEnumerable();
}

6.3 RESTful Controller 示例

[ApiController]
[Route("api/orders")]
public class OrdersController : AbpController
{private readonly IOrderAppService _service;public OrdersController(IOrderAppService service) => _service = service;[HttpGet("{id}/snapshot")]public Task<OrderDto> GetSnapshot(Guid id,DateTime asOf,CancellationToken cancellationToken)=> _service.GetSnapshotAsync(id, asOf, cancellationToken);[HttpGet("{id}/history/stream")]public IAsyncEnumerable<OrderHistoryDto> GetHistoryStream(Guid id,CancellationToken cancellationToken)=> _service.StreamHistoryAsync(id, cancellationToken);
}

---

🗄️ 七、性能与存储考量

1. 索引 & 多租户隔离

   • 主表：聚集索引包含 Id, TenantId

   • 历史表：非聚集索引 (SysStartTime, TenantId)

     CREATE NONCLUSTERED INDEX IX_OH_SysStart_TenantON dbo.OrdersHistory (SysStartTime, TenantId);
     

2. 保留策略（2017+）

   -- 启用数据库级保留
   ALTER DATABASE [YourDb] SET TEMPORAL_HISTORY_RETENTION ON;
   -- 表级自动清理
   ALTER TABLE dbo.OrdersSET (SYSTEM_VERSIONING = ON (HISTORY_TABLE            = dbo.OrdersHistory,HISTORY_RETENTION_PERIOD = 30 DAYS));
   

3. 手动清理脚本（2016–2019）

   ALTER TABLE dbo.Orders SET (SYSTEM_VERSIONING = OFF);
   DELETE FROM dbo.OrdersHistoryWHERE SysEndTime < DATEADD(DAY, -30, SYSUTCDATETIME());
   ALTER TABLE dbo.OrdersSET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
   

4. 分区滑动窗口

   CREATE PARTITION FUNCTION pf_OH (datetime2)AS RANGE RIGHT FOR VALUES ('2023-01-01','2024-01-01');
   CREATE PARTITION SCHEME ps_OHAS PARTITION pf_OH TO ([PRIMARY],[FG_Hist_2023],[FG_Hist_2024]);
   ALTER TABLE dbo.OrdersHistorySWITCH PARTITION 1 TO dbo.OrdersHistoryArchive PARTITION 1;
   

5. 存储压缩

   ALTER TABLE dbo.OrdersHistoryREBUILD PARTITION = ALLWITH (DATA_COMPRESSION = PAGE);
   

6. 写入开销

   • 每次 DML 都写历史，IO 成本上升；建议仅对关键实体启用

---

🛠️ 八、CI/CD & 自动化测试

GitHub Actions 示例 (.github/workflows/ci.yml)：

name: CIon:push:branches: [ main ]pull_request:jobs:build_and_test:runs-on: ubuntu-latestservices:sqlserver:image: mcr.microsoft.com/mssql/server:2019-latestenv:ACCEPT_EULA: YSA_PASSWORD: Your_strong!Passw0rdports:- 1433:1433steps:- uses: actions/checkout@v3- name: Setup .NETuses: actions/setup-dotnet@v3with:dotnet-version: '7.x'- name: Restorerun: dotnet restore- name: Buildrun: dotnet build --no-restore --configuration Release- name: Apply Migrationsrun: dotnet ef database update --project src/YourProject/YourProject.csproj- name: Testrun: dotnet test --no-build --configuration Release --verbosity normal

---

🏁 九、简单演示

1. 样例工程

   abp new AbpTemporalDemo -t app -u ef
   cd AbpTemporalDemo
   

2. Docker Compose

   services:sqlserver:image: mcr.microsoft.com/mssql/server:2019-latestenvironment:- ACCEPT_EULA=Y- SA_PASSWORD=Your_strong!Passw0rdports:- "1433:1433"
   

   docker-compose up -d
   

3. 迁移 & 运行

   dotnet ef migrations add InitTemporal
   dotnet ef database update
   

4. 验证

   • 模拟多次 UPDATE Orders SET Status = …
   • 调用 /api/orders/{id}/snapshot?asOf=… & /api/orders/{id}/history/stream

---