EF Core 8迁移脚本中IsTemporal缺失DATA_CONSISTENCY_CHECK的咨询

一、DATA_CONSISTENCY_CHECK开关缺失的原因 #

既不是EF Core 8的变更问题，也和SQL Server 2022版本无关。这个开关是SQL Server系统版本化表的可选配置项，EF Core的IsTemporal()方法默认只会生成基础的系统版本化开启语句，不会自动带上这个参数。

二、不使用原生SQL添加该参数的办法 #

EF Core 8本身的API没有直接提供配置DATA_CONSISTENCY_CHECK的入口，但可以通过自定义迁移SQL生成器来实现，不用手动写原生SQL：

1. 自定义SQL Server迁移SQL生成器，重写生成临时表的逻辑：

using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Sql;

public class CustomSqlServerMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public CustomSqlServerMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, 
        IRelationalAnnotationProvider annotations)
        : base(dependencies, annotations)
    {
    }

    protected override void Generate(CreateTableOperation operation)
    {
        // 先移除默认的系统版本化开启注解，避免重复生成语句
        var temporalAnnotation = operation.FindAnnotation("SqlServer:Temporal");
        if (temporalAnnotation != null && operation.IsTemporal())
        {
            operation.RemoveAnnotation("SqlServer:Temporal");
            base.Generate(operation);
            
            // 重新生成带DATA_CONSISTENCY_CHECK的系统版本化配置语句
            var historyTableAnnotation = operation.FindAnnotation("SqlServer:TemporalHistoryTable");
            var historyTable = historyTableAnnotation?.Value.ToString();
            var schemaPrefix = string.IsNullOrEmpty(operation.Schema) ? "" : $"[{operation.Schema}].";
            var targetTableName = $"[{operation.Name}]";
            var fullHistoryTableName = string.IsNullOrEmpty(historyTable) 
                ? $"{schemaPrefix}[{operation.Name}_History]" 
                : historyTable;
            
            Statement($"ALTER TABLE {schemaPrefix}{targetTableName} ADD PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]);");
            Statement($"ALTER TABLE {schemaPrefix}{targetTableName} SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {fullHistoryTableName}, DATA_CONSISTENCY_CHECK = ON));");
        }
        else
        {
            base.Generate(operation);
        }
    }
}

2. 在你的DbContext配置里替换默认的生成器服务：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("你的数据库连接字符串")
        .ReplaceService<IMigrationsSqlGenerator, CustomSqlServerMigrationsSqlGenerator>();
}

这样之后生成的迁移脚本就会自动带上DATA_CONSISTENCY_CHECK = ON参数，全程不用手动编写原生SQL。

内容的提问来源于stack exchange，提问作者mdzieg