如何在SqlServerMigrationsSqlGenerator中使用自定义注解添加行级安全策略？

问题核心是实体上的自定义注解不会自动同步到CreateTableOperation中，需要通过自定义迁移模型差异处理器，将实体注解传递到迁移操作里，之后就能在SqlGenerator中正常读取。

步骤1：自定义IMigrationsModelDiffer，传递实体注解 #

创建继承自SqlServerMigrationsModelDiffer的类，重写CreateTable方法，将实体的IsMultiTenant注解复制到生成的CreateTableOperation中：

public class MyMigrationsModelDiffer : SqlServerMigrationsModelDiffer
{
    public MyMigrationsModelDiffer(
        IRelationalTypeMappingSource typeMappingSource,
        IMigrationsAnnotationProvider migrationsAnnotations,
        IChangeDetector changeDetector,
        IUpdateAdapterFactory updateAdapterFactory,
        CommandBatchPreparerDependencies commandBatchPreparerDependencies)
        : base(typeMappingSource, migrationsAnnotations, changeDetector, updateAdapterFactory, commandBatchPreparerDependencies)
    {
    }

    protected override CreateTableOperation CreateTable(
        IEntityType entityType,
        IModel? sourceModel,
        IModel? targetModel,
        DiffContext diffContext)
    {
        var createTableOp = base.CreateTable(entityType, sourceModel, targetModel, diffContext);

        // 读取实体的IsMultiTenant注解并添加到操作对象中
        if (entityType.FindAnnotation("IsMultiTenant") is { Value: bool isMultiTenant } && isMultiTenant)
        {
            createTableOp.AddAnnotation("IsMultiTenant", true);
        }

        return createTableOp;
    }
}

步骤2：注册自定义服务 #

在DbContext的服务配置中，替换默认的IMigrationsModelDiffer和IMigrationsSqlGenerator：

builder.Services.AddDbContext<YourDbContext>(options =>
    options.UseSqlServer("你的连接字符串")
           .ReplaceService<IMigrationsModelDiffer, MyMigrationsModelDiffer>()
           .ReplaceService<IMigrationsSqlGenerator, MySqlServerMigrationSqlGenerator>());

步骤3：优化SqlGenerator中的注解读取逻辑 #

可以将原有的判断逻辑优化为更稳健的写法：

protected override void Generate(CreateTableOperation operation, IModel? model, MigrationCommandListBuilder builder, bool terminate = true)
{
    ArgumentNullException.ThrowIfNull(operation);
    ArgumentNullException.ThrowIfNull(builder);

    base.Generate(operation, model, builder, terminate);

    // 读取操作中的IsMultiTenant注解
    if (operation.Annotations.TryGetValue("IsMultiTenant", out var annotation) && annotation.Value is true)
    {
        var tableName = Dependencies.SqlGenerationHelper.DelimitIdentifier(operation.Name, operation.Schema);
        
        builder.AppendLine("ALTER SECURITY POLICY rls.tenantAccessPolicy");
        using (builder.Indent())
        {
            builder
                .AppendLine($"ADD FILTER PREDICATE rls.fn_tenantAccessPredicate(TenantID) ON {tableName},")
                .Append($"ADD BLOCK PREDICATE rls.fn_tenantAccessPredicate(TenantID) ON {tableName}")
                .AppendLine(Dependencies.SqlGenerationHelper.StatementTerminator);
        }
        EndStatement(builder);
    }
}

补充说明 #

如果需要给已存在的表添加行级安全策略，还需要在MyMigrationsModelDiffer中重写AlterTable方法，判断实体注解的变化，生成对应的AlterTable操作并传递注解，之后在SqlGenerator中处理AlterTableOperation的逻辑。

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