前言

数据库并发，数据审计和软删除一直是数据持久化方面的经典问题。早些时候，这些工作需要手写复杂的SQL或者通过存储过程和触发器实现。手写复杂SQL对软件可维护性构成了相当大的挑战，随着SQL字数的变多，用到的嵌套和复杂语法增加，可读性和可维护性的难度是几何级暴涨。因此如何在实现功能的同时控制这些SQL的复杂度是一个很有价值的问题。而且这个问题同时涉及应用软件和数据库两个相对独立的体系，平行共管也是产生混乱的一大因素。

EF Core作为 .NET平台的高级ORM框架，可以托管和数据库的交互，同时提供了大量扩展点方便自定义。以此为基点把对数据库的操作托管后便可以解决平行共管所产生的混乱，利用LINQ则可以最大程度上降低软件代码的维护难度。

由于项目需要，笔者先后开发并发布了通用的基于EF Core存储的国际化服务和基于EF Core存储的Serilog持久化服务，不过这两个功能包并没有深度利用EF Core，虽然主要是因为没什么必要。但是项目还需要提供常用的数据审计和软删除功能，因此对EF Core进行了一些更深入的研究。

起初有考虑过是否使用现成的ABP框架来处理这些功能，但是在其他项目的使用体验来说并不算好，其中充斥着大量上下文依赖的功能，而且这些依赖信息能轻易藏到和最终业务代码相距十万八千里的地方（特别是代码还是别人写的时候），然后在不经意间给你一个大惊喜。对于以代码正交性、非误导性，纯函数化为追求的一介码农（看过我发布的那两个功能包的朋友应该有感觉，一个功能笔者也要根据用途划分为不同的包，确保解决方案中的各个项目都能按需引用，不会残留无用的代码），实在是喜欢不起来ABP这种全家桶。

鉴于项目规模不大，笔者决定针对这些需求做一个专用功能，目标是尽可能减少依赖，方便将来复用到其他项目，降低和其他功能功能冲突的风险。现在笔者将用一系列博客做成果展示。由于这些功能没有经过大范围测试，不确定是否存在未知缺陷，因此暂不打包发布。

新书宣传

有关新书的更多介绍欢迎查看《C#与.NET6 开发从入门到实践》上市，作者亲自来打广告了！

正文

由于这些功能设计的代码量和知识点较多，为控制篇幅，本文介绍软删除功能。

引言

多年以前就听说过软删除也进行过一些了解，也思考过如何才能优雅地实现，但都因为知识储备不足和需求不紧急而搁置了，这次箭在弦上不得不发了，所幸这些年的积累也为解决这个问题铺平了道路。

关系数据库的一大功能就是确保数据完整性，毕竟关系数据库一开始就是为金融等对数据有严格要求的场景而设计。但是这种功能在某些时候也会带来一些麻烦，例如经典的博客评论关系中，评论一定是要属于某个博客的，这种强依赖会导致如果删除博客，评论的存在意义会同时消失。为保障数据完整性，这些孤儿评论应该同时删除或者至少把外键设置为null以明确表示评论所对应的博客已经不存在。但这种过于强硬的要求会导致没有后悔药可以吃，为了不把事做绝，软删除方案应运而生。通过额外的字段标记数据已被删除，然后在查询时用条件排除此类记录。

看上去一切都很美好，如果不出意外的话就该出意外了。查询语句复杂度陡然上升、唯一约束错乱、级联删除的恢复等让人头痛欲裂的问题接踵而至。为了研究这些问题的解决方案也是翻阅了大量资料，聊的相对深入的一些例如软删除之痛、在EF Core中使用拦截器实现自动软删除、深入理解 EF Core：使用查询过滤器实现数据软删除 。但是这些资料都没能同时解决这些问题，或者解决方式存在缺陷，当年搁置软删除问题的研究大抵也是出于这些原因。

解决方案

这次借助EF Core的一些功能，总算是用一种比较优雅的方式基本解决这些痛点。

• 唯一约束错乱：问题的根源在于数据库中的Null值特殊的相等性判定。因此笔者的解决方案是首先禁止删除标记为Null，然后用精确到微秒的时间类型基本解决不可重复删除问题。
• 软删除的级联和恢复错乱：上文提到的文章使用删除计数来识别记录被删除过几次，但是这种方案对于计数为1的记录依然存在这个1究竟是主动删除导致的还是被级联删除导致的无法准确区分。其次，这种计数方式对事务性的要求较高，需要把主表和可能相关的子表一并锁定避免计数错乱，还可能把删除子表数据的代码蔓延到业务代码中产生大量代码噪音。如果使用触发器还需要解决潜在的递归嵌套溢出和架构迁移维护问题，无论如何都不是个优雅的方案。再次，如果需要直接操作数据库进行数据维护，一旦脑子不清醒忘记更新部分子表的计数器，数据完整性会遭到致命破坏而且很可能无法恢复，因为这种修改很难溯源，无法知道某个计数器更新是何原因也就无法正确回滚。
  笔者的解决方案则是使用独立的删除标记和传递删除标记来实现软删除的自动级联，分离后将不再需要计数器，因此事务问题和架构维护问题自然消失，主动删除和传递删除的恢复问题也一并消失。现在问题也变成了传递删除标记的值要如何产生。对此，笔者的方法是使用视图自动计算，虽然这样可能导致一些查询性能损失，但是这是不可避免的代价，鱼与熊掌不可兼得。随之而来的下一个问题就是视图的查询SQL如何维护，幸好EF Core本身就包含迁移功能来管理数据库架构的变更，那只要把视图定义SQL的维护放到迁移中即可。最后问题就变成如何实现视图定义SQL的自动化维护，否则整个方案都会很难用，而这就是本文的重要内容之一。
• 查询复杂度陡然上升和已删除数据被意外包含在结果中：查询复杂度的飞速上升主要是因为需要大量增加对传递删除的查询，随着上一个问题的解决，这个问题自然消失，因为这些复杂的查询都被封装在视图定义中了。EF Core新版的表视图多重映射和全局查询过滤器功能更是能在业务层彻底屏蔽软删除的查询问题。而且就算是需要手写SQL查询的地方也可以极大减轻心智负担，因为视图存在于数据库中，就算脱离应用程序依然可以使用。

代码实现

基础接口

/// <summary>
/// 逻辑删除接口
/// </summary>
public interface ILogicallyDeletable
{
    /// <summary>
    /// 逻辑删除标记
    /// </summary>
    /// <remarks>推荐存活的记录使用<see cref="DateTimeOffset.MinValue"/>标记</remarks>
    DateTimeOffset DeletedAt { get; set; }
}

/// <summary>
/// 依赖项逻辑删除接口
/// </summary>
public interface IDependencyLogicallyDeletable
{
    /// <summary>
    /// 依赖项逻辑删除标记
    /// </summary>
    /// <remarks>
    /// <para>用于模拟逻辑删除的级联删除或置Null。</para>
    /// <para>对于必选关系，应该引用主记录的逻辑删除和依赖项逻辑删除标记值中较大的一方，确保级联删除模拟可以自动传播。</para>
    /// <para>推荐使用EF Core的查询视图映射把属性值映射到查询视图并配置全局查询过滤器简化级联逻辑删除的查询。</para>
    /// <para>EF Core实现中表映射到Null值计算列，由查询视图计算实际值。</para>
    /// </remarks>
    DateTimeOffset? DependencyDeletedAt { get; set; }
}

传递删除标记由视图计算，但是EF Core无法配置某个属性只存在于视图中，因此只能使用计算列在表中生成一个同名列以符合EF Core的映射要求。

模型配置扩展

/// <summary>
/// 实体模型配置扩展
/// </summary>
public static class EntityModelBuilderExtensions
{
    internal const string _logicallyDeleteableAnnotationName = "CoreDX.Entity.Property:LogicallyDeleteable";
    internal const string _dependencyLogicallyDeleteableAnnotationName = "CoreDX.Entity.Property:DependencyLogicallyDeleteable";

    internal const string _queryViewAnnotationName = "CoreDX.Relational:QueryView";
    internal const string _queryViewNamePrefixes = "QueryView_";
    internal const string _treeQueryViewNamePrefixes = $"{_queryViewNamePrefixes}Tree_";

    /// <summary>
    /// 使用<see langword="default" />（<see cref="DateTimeOffset.MinValue"/>）
    /// </summary>
    private static readonly DateTimeOffset _aliveEntityTime = default;

    /// <summary>
    /// 配置可逻辑删除实体的查询过滤器让EF Core自动添加查询条件过滤已被逻辑删除的记录。<br/>存活的记录使用<see cref="DateTimeOffset.MinValue"/>标记。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    /// <returns>实体类型构造器</returns>
    public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForILogicallyDelete<TEntity>(this EntityTypeBuilder<TEntity> builder)
        where TEntity : class, ILogicallyDeletable
    {
        ArgumentNullException.ThrowIfNull(builder);

        // 配置数据库默认值和EF Core哨兵值
        builder.Property(e => e.DeletedAt)
            .HasDefaultValue(_aliveEntityTime)
            .HasSentinel(_aliveEntityTime)
            .HasAnnotation(_logicallyDeleteableAnnotationName, true);

        ConfigQueryViewAnnotationForLogicallyDeletable(builder);

        return builder.HasQueryFilter(e => e.DeletedAt == EF.Constant(_aliveEntityTime));
    }

    /// <summary>
    /// 配置依赖项逻辑删除实体的查询过滤器让EF Core自动添加查询条件过滤已被逻辑删除的记录。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    /// <param name="nullValueSql">依赖项逻辑删除在表中的计算列Null值生成Sql</param>
    /// <returns>实体类型构造器</returns>
    public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForIDependencyLogicallyDelete<TEntity>(
        this EntityTypeBuilder<TEntity> builder,
        IDependencyLogicallyDeletableNullValueSql nullValueSql)
        where TEntity : class, IDependencyLogicallyDeletable
    {
        ArgumentNullException.ThrowIfNull(builder);
        ArgumentNullException.ThrowIfNull(nullValueSql);

        // 配置表的依赖项逻辑删除标记列为值永远为NULL的计算列
        builder.Property(e => e.DependencyDeletedAt)
            .HasComputedColumnSql(nullValueSql.DependencyDeleteAtNullComputedValueSql)
            .HasAnnotation(_dependencyLogicallyDeleteableAnnotationName, true);

        ConfigQueryViewAnnotationForDependencyLogicallyDeletable(builder);

        return builder.HasQueryFilter(e => e.DependencyDeletedAt == null || e.DependencyDeletedAt == EF.Constant(_aliveEntityTime));
    }

    /// <summary>
    /// 配置可逻辑删除和依赖项逻辑删除实体的查询过滤器让EF Core自动添加查询条件过滤已被逻辑删除的记录。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    /// <param name="nullValueSql">依赖项逻辑删除在表中的计算列Null值生成Sql</param>
    /// <returns>实体类型构造器</returns>
    public static EntityTypeBuilder<TEntity> ConfigureQueryFilterForILogicallyAndIDependencyLogicallyDelete<TEntity>(
        this EntityTypeBuilder<TEntity> builder,
        IDependencyLogicallyDeletableNullValueSql nullValueSql)
        where TEntity : class, ILogicallyDeletable, IDependencyLogicallyDeletable
    {
        ArgumentNullException.ThrowIfNull(builder);
        ArgumentNullException.ThrowIfNull(nullValueSql);

        // 配置数据库默认值和EF Core哨兵值
        builder.Property(e => e.DeletedAt)
            .HasDefaultValue(_aliveEntityTime)
            .HasSentinel(_aliveEntityTime)
            .HasAnnotation(_logicallyDeleteableAnnotationName, true);

        // 配置表的依赖项逻辑删除标记列为值永远为NULL的计算列
        builder.Property(e => e.DependencyDeletedAt)
            .HasComputedColumnSql(nullValueSql.DependencyDeleteAtNullComputedValueSql)
            .HasAnnotation(_dependencyLogicallyDeleteableAnnotationName, true);

        ConfigQueryViewAnnotationForLogicallyDeletable(builder);
        ConfigQueryViewAnnotationForDependencyLogicallyDeletable(builder);

        return builder.HasQueryFilter(e => e.DeletedAt == EF.Constant(_aliveEntityTime) && (e.DependencyDeletedAt == null || e.DependencyDeletedAt == EF.Constant(_aliveEntityTime)));
    }

    /// <summary>
    /// 批量配置可逻辑删除和依赖项逻辑删除实体的查询过滤器让EF Core自动添加查询条件过滤已被逻辑删除或传递依赖删除的记录
    /// </summary>
    /// <param name="modelBuilder">模型构造器</param>
    /// <param name="nullValueSql">依赖项逻辑删除在表中的计算列Null值生成Sql</param>
    /// <returns>模型构造器</returns>
    public static ModelBuilder ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(
        this ModelBuilder modelBuilder,
        IDependencyLogicallyDeletableNullValueSql nullValueSql)
    {
        ArgumentNullException.ThrowIfNull(modelBuilder);
        ArgumentNullException.ThrowIfNull(nullValueSql);

        foreach (var entity
            in modelBuilder.Model.GetEntityTypes()
                .Where(e => e.ClrType.IsDerivedFrom<ILogicallyDeletable>() || e.ClrType.IsDerivedFrom<IDependencyLogicallyDeletable>()))
        {
            var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);

            var isILogicallyDeletable = entity.ClrType.IsDerivedFrom<ILogicallyDeletable>();
            var isIDependencyLogicallyDeletable = entity.ClrType.IsDerivedFrom<IDependencyLogicallyDeletable>();

            var logicallyDeleteQueryFilterMethod = (isILogicallyDeletable, isIDependencyLogicallyDeletable) switch
            {
                (true, false) => GetEntityTypeConfigurationMethod(
                    nameof(ConfigureQueryFilterForILogicallyDelete),
                    1,
                    entity.ClrType),
                (false, true) => GetEntityTypeConfigurationMethod(
                    nameof(ConfigureQueryFilterForIDependencyLogicallyDelete),
                    2,
                    entity.ClrType),
                (true, true) => GetEntityTypeConfigurationMethod(
                    nameof(ConfigureQueryFilterForILogicallyAndIDependencyLogicallyDelete),
                    2,
                    entity.ClrType),
                // 不可能进入此分支
                (false, false) => throw new InvalidOperationException()
            };

            if (isIDependencyLogicallyDeletable) logicallyDeleteQueryFilterMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null), nullValueSql]);
            else logicallyDeleteQueryFilterMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null)]);
        }

        return modelBuilder;
    }

    /// <summary>
    /// 配置实体查询视图。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    /// <returns>实体类型构造器</returns>
    /// <remarks>
    /// <para>如果实体类型实现<see cref="IDependencyLogicallyDeletable"/>，但不实现<see cref="ITreeEntity{TKey, TEntity}"/>，生成并映射查询到以<see cref="_queryViewNamePrefixes"/>开头的视图。</para>
    /// <para>如果实体类型实现<see cref="ITreeEntity{TKey, TEntity}"/>，但不实现<see cref="IDependencyLogicallyDeletable"/>，生成并映射查询到以<see cref="_treeQueryViewNamePrefixes"/>开头的视图。</para>
    /// <para>如果实体类型同时实现<see cref="IDependencyLogicallyDeletable"/>和<see cref="ITreeEntity{TKey, TEntity}"/>，同时生成以<see cref="_queryViewNamePrefixes"/>和<see cref="_treeQueryViewNamePrefixes"/>开头的视图。<br/>
    /// 实体查询映射到以<see cref="_treeQueryViewNamePrefixes"/>开头的视图，并且<see cref="_treeQueryViewNamePrefixes"/>开头的视图以<see cref="_queryViewNamePrefixes"/>开头的视图为数据源。</para>
    /// </remarks>
    public static EntityTypeBuilder<TEntity> ConfigEntityQueryView<TEntity>(this EntityTypeBuilder<TEntity> builder)
        where TEntity : class
    {
        ArgumentNullException.ThrowIfNull(builder);

        if (builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value is List<Type> annotationValue)
        {
            var isDependencyLogicallyDeletableEntity = annotationValue.Any(static x => x == typeof(IDependencyLogicallyDeletable));
            var isTreeEntity = annotationValue.Any(static x => x == typeof(ITreeEntity<,>));

            var tableName = builder.Metadata.GetTableName();
            builder.ToTable(tableName);

            if(isTreeEntity || isDependencyLogicallyDeletableEntity)
            {
                builder.ToView($"{(isTreeEntity ? _treeQueryViewNamePrefixes : _queryViewNamePrefixes)}{tableName}");
            }
        }

        return builder;
    }

    /// <summary>
    /// 批量配置实体查询视图
    /// </summary>
    /// <param name="modelBuilder">模型构造器</param>
    /// <returns>模型构造器</returns>
    /// <remarks>配置规则同<see cref="ConfigEntityQueryView{TEntity}"/></remarks>
    public static ModelBuilder ConfigEntityQueryView(this ModelBuilder modelBuilder)
    {
        ArgumentNullException.ThrowIfNull(modelBuilder);

        foreach (var entity
            in modelBuilder.Model.GetEntityTypes()
                .Where(static e => e.FindAnnotation(_queryViewAnnotationName) is not null))
        {
            var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);
            var entityQueryViewMethod = GetEntityTypeConfigurationMethod(
                nameof(ConfigEntityQueryView),
                1,
                entity.ClrType);

            entityQueryViewMethod.Invoke(null, [entityTypeBuilderMethod.Invoke(modelBuilder, null)]);
        }

        return modelBuilder;
    }

    /// <summary>
    /// 配置逻辑删除实体的查询视图注解
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    private static void ConfigQueryViewAnnotationForLogicallyDeletable<TEntity>(EntityTypeBuilder<TEntity> builder)
        where TEntity : class, ILogicallyDeletable
    {
        var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
        var typedAnnotationValue = annotationValue as List<Type>;
        if (annotationValue is not null && typedAnnotationValue is null)
        {
            throw new InvalidOperationException($@"模型注解名称""{_queryViewAnnotationName}""已被占用，请把占用此名称的注解修改为其他名称。");
        }
        else if (typedAnnotationValue is null)
        {
            builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(ILogicallyDeletable) });
        }
        else if (typedAnnotationValue.Find(static x => x is ILogicallyDeletable) is null)
        {
            typedAnnotationValue.Add(typeof(ILogicallyDeletable));
        }
    }

    /// <summary>
    /// 配置依赖项逻辑删除实体的查询视图注解
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="builder">实体类型构造器</param>
    private static void ConfigQueryViewAnnotationForDependencyLogicallyDeletable<TEntity>(EntityTypeBuilder<TEntity> builder)
        where TEntity : class, IDependencyLogicallyDeletable
    {
        var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
        var typedAnnotationValue = annotationValue as List<Type>;
        if (annotationValue is not null && typedAnnotationValue is null)
        {
            throw new InvalidOperationException($@"模型注解名称""{_queryViewAnnotationName}""已被占用，请把占用此名称的注解修改为其他名称。");
        }
        else if (typedAnnotationValue is null)
        {
            builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(IDependencyLogicallyDeletable) });
        }
        else if (typedAnnotationValue.Find(static x => x is IDependencyLogicallyDeletable) is null)
        {
            typedAnnotationValue.Add(typeof(IDependencyLogicallyDeletable));
        }
    }
}

SQL模版

相关接口

/// <summary>
/// 依赖项逻辑删除实体的视图列在表中的Null值计算列映射
/// </summary>
public interface IDependencyLogicallyDeletableNullValueSql
{
    string DependencyDeleteAtNullComputedValueSql { get; }
}

public interface ITableOrColumnNameFormattable
{
    /// <summary>
    /// 格式化表或列名
    /// <para>例如为SqlServer列名包裹方括号、MySql包裹反引号</para>
    /// </summary>
    /// <param name="name">表或列名</param>
    /// <returns>格式化的表或列名</returns>
    [return: NotNullIfNotNull(nameof(name))]
    string? FormatTableOrColumnName(string? name);
}

/// <summary>
/// 依赖项逻辑删除实体的视图SQL模板
/// </summary>
public interface IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate : ITableOrColumnNameFormattable
{
    /// <summary>
    /// 创建视图的模板
    /// </summary>
    string CreateSqlTemplate { get; }

    /// <summary>
    /// 删除视图的模板
    /// </summary>
    string DropSqlTemplate { get; }

    /// <summary>
    /// 连接数据源模版
    /// </summary>
    /// <remarks>LEFT JOIN {principalDataSource} ON {tableName}.{foreignKey} = {principalDataSource}.{principalKey}</remarks>
    string JoinTargetTemplate { get; }
    string PrincipalLogicallyDeleteColumnTemplate { get; }
}

接口实现（以SqlServer为例）

public class DefaultSqlServerDependencyLogicallyDeletableNullValueSql : IDependencyLogicallyDeletableNullValueSql
{
    public static DefaultSqlServerDependencyLogicallyDeletableNullValueSql Instance => new();

    private const string _dependencyDeleteAtNullComputedValueSql = "CAST(NULL AS datetimeoffset)";

    public string DependencyDeleteAtNullComputedValueSql => _dependencyDeleteAtNullComputedValueSql;

    private DefaultSqlServerDependencyLogicallyDeletableNullValueSql() { }
}

public class DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate : IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate
{
    public static DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate Instance => new();

    private const string _viewNameTemplate = $$"""{{EntityModelBuilderExtensions._queryViewNamePrefixes}}{tableName}""";

    // SqlServer 2022起才支持GREATEST函数
    private const string _createSqlTemplate =
        $$"""
        --创建或重建依赖项逻辑删除实体查询视图
        {{_dropSqlTemplate}}
        CREATE VIEW {{_viewNameTemplate}}
        AS
        SELECT {columns},
            (SELECT MAX([DeleteTimeTable].[DeletedAt])
                FROM (VALUES {principalLogicallyDeleteColumns}) AS DeleteTimeTable([DeletedAt])) AS {dependencyDeletedAtColumn}
        FROM [{tableName}]
        {joinTargets};
        GO
        """;

    private const string _principalLogicallyDeleteColumnTemplate = "({principalLogicallyDeleteColumn})";

    private const string _joinTargetTemplate =
        $$"""
        LEFT JOIN {principalDataSource}
        ON {joinCondition}
        """;

    private const string _dropSqlTemplate =
        $"""
        --删除可能存在的过时依赖项逻辑删除实体查询视图
        IF EXISTS(SELECT * FROM [sysobjects] WHERE [id] = OBJECT_ID(N'{_viewNameTemplate}') AND objectproperty(id, N'IsView') = 1)
        BEGIN
            DROP VIEW [{_viewNameTemplate}]
        END
        GO
        """;

    public string CreateSqlTemplate => _createSqlTemplate;

    public string DropSqlTemplate => _dropSqlTemplate;

    public string PrincipalLogicallyDeleteColumnTemplate => _principalLogicallyDeleteColumnTemplate;

    public string JoinTargetTemplate => _joinTargetTemplate;

    public string? FormatTableOrColumnName(string? name)
    {
        if (name is null) return null;

        return $"[{name}]";
    }

    private DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate() { }
}

SqlServer 2022才支持取最大值函数，因此这里使用兼容性较高的表值生成函数配合MAX聚合实现取最大值。

数据库迁移扩展

/// <summary>
/// 依赖项逻辑删除实体视图迁移扩展
/// </summary>
public static class DependencyLogicallyDeletableEntityMigrationBuilderExtensions
{
    /// <summary>
    /// 自动扫描迁移模型并配置依赖项逻辑删除实体查询视图
    /// </summary>
    /// <param name="migrationBuilder">迁移构造器</param>
    /// <param name="thisVersion">当前版本的迁移</param>
    /// <param name="previousVersion">上一个版本的迁移</param>
    /// <param name="isUp">是否为升级迁移</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>迁移构造器</returns>
    public static MigrationBuilder ApplyDependencyLogicallyDeletableEntityQueryView(
        this MigrationBuilder migrationBuilder,
        Migration thisVersion,
        Migration? previousVersion,
        bool isUp,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(thisVersion);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

        var thisVersionEntityTypes = thisVersion.TargetModel.GetEntityTypes()
            .Where(static et =>
                (et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                ?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true
            );

        var previousVersionEntityTypes = previousVersion?.TargetModel.GetEntityTypes()
            .Where(static et =>
                (et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                ?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true
            );

        var pendingViewOperations = new List<(IEntityType? entity, string? tableName, bool isCreate)>();

        var tempViewOperationsDict = new Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>>();
        // 表操作
        foreach (var tableOperation in
            migrationBuilder.Operations.Where(static op =>
            {
                var opType = op.GetType();
                return opType.IsDerivedFrom<TableOperation>() || opType.IsDerivedFrom<DropTableOperation>();
            }))
        {
            if (tableOperation is CreateTableOperation createTable)
            {
                // 升级创建表，创建视图
                if (isUp && thisVersionEntityTypes.Any(et => et.GetTableName() == createTable.Name))
                {
                    var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == createTable.Name);
                    AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                }

                // 回滚创建表，说明必然存在上一版迁移，以上一个版本的模型创建视图
                if (!isUp)
                {
                    EnsureMigrationOfPreviousVersion(previousVersion);
                    if (previousVersionEntityTypes?.Any(et => et.GetTableName() == createTable.Name) is true)
                    {
                        var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == createTable.Name);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
            }
            else if (tableOperation is AlterTableOperation alterTable)
            {
                // 升级修改表（仅当修改表名时），重建视图
                // 因为修改表操作可能是修改表注释
                if (isUp)
                {
                    // 如果上一版本中这个实体是依赖项逻辑删除实体，删除旧视图
                    if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterTable.OldTable.Name) is true)
                    {
                        // 由于升级修改表名需要同时完成删除基于旧表名的视图和创建基于新表名的视图两个操作
                        // 删除旧视图的操作直接添加到挂起操作列表，修改表名的操作也不会在迁移中重复出现，没有重复添加相同操作的问题
                        pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
                    }

                    if (thisVersionEntityTypes.Any(et => et.GetTableName() == alterTable.Name))
                    {
                        var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == alterTable.Name);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
                // 回滚修改表（仅当修改表名时），说明必然存在上一版迁移，以上一个版本的模型重建视图
                else
                {
                    // 如果当前版本中这个实体也是依赖项逻辑删除实体，删除旧视图
                    if (thisVersionEntityTypes.Any(et => et.GetTableName() == alterTable.OldTable.Name))
                    {
                        // 由于回滚修改表名需要同时完成删除基于新表名的视图和创建基于旧表名的视图两个操作
                        // 删除旧视图的操作直接添加到挂起操作列表，修改表名的操作也不会在迁移中重复出现，没有重复添加相同操作的问题
                        pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
                    }

                    EnsureMigrationOfPreviousVersion(previousVersion);
                    if (previousVersionEntityTypes!.Any(et => et.GetTableName() == alterTable.Name) && alterTable.OldTable.Name != alterTable.Name)
                    {
                        var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == alterTable.Name);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
            }
            // 迁移操作需要删除表，则代表同样需要删除视图
            else if (tableOperation is DropTableOperation dropTable)
            {
                if (isUp)
                {
                    EnsureMigrationOfPreviousVersion(previousVersion);
                    if (previousVersionEntityTypes!.Any(en => en.GetTableName() == dropTable.Name))
                    {
                        AddTableDropTableViewToTempDict(tempViewOperationsDict, dropTable.Name);
                    }
                }
                else if (thisVersionEntityTypes.Any(en => en.GetTableName() == dropTable.Name))
                {
                    AddTableDropTableViewToTempDict(tempViewOperationsDict, dropTable.Name);
                }
            }
        }

        // 列操作，每个表可能操作多个列，需要聚合处理
        foreach (var tableColumnOperationsGrouping in
            migrationBuilder.Operations
                .Where(static op =>
                {
                    var opType = op.GetType();
                    return opType.IsDerivedFrom<ColumnOperation>() || opType.IsDerivedFrom<DropColumnOperation>();
                })
                .GroupBy(static op => (op as ColumnOperation)?.Table ?? (op as DropColumnOperation)!.Table))
        {
            foreach (var columnOperation in tableColumnOperationsGrouping)
            {
                if (columnOperation is AddColumnOperation addColumn)
                {
                    // 升级添加列，如果是依赖项逻辑删除，重建视图
                    if (isUp && thisVersionEntityTypes?.Any(et => et.GetTableName() == addColumn.Table) is true)
                    {
                        var entity = thisVersionEntityTypes.Single(en => en.GetTableName() == addColumn.Table);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }

                    // 回滚添加列，说明必然存在上一版迁移，如果上一版是依赖项逻辑删除实体，重建视图
                    if (!isUp)
                    {
                        EnsureMigrationOfPreviousVersion(previousVersion);
                        if (previousVersionEntityTypes?.Any(et => et.GetTableName() == addColumn.Table) is true)
                        {
                            var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == addColumn.Table);
                            AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                        }
                    }
                }
                else if (columnOperation is AlterColumnOperation alterColumn)
                {
                    // 升级修改列，重建视图
                    // 因为修改列操作可能是修改列注释
                    if (isUp)
                    {
                        if (thisVersionEntityTypes!.Any(et => et.GetTableName() == alterColumn.Table))
                        {
                            var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == alterColumn.Table);
                            AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                        }
                        else if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterColumn.Table) is true)
                        {
                            AddTableDropTableViewToTempDict(tempViewOperationsDict, alterColumn.Table);
                        }
                    }
                    // 回滚修改列（仅当修改列名时），说明必然存在上一版迁移，如果上一版是依赖项逻辑删除实体，重建视图
                    // 因为修改列操作可能是修改列注释
                    else
                    {
                        EnsureMigrationOfPreviousVersion(previousVersion);
                        if (previousVersionEntityTypes?.Any(et => et.GetTableName() == alterColumn.Table) is true)
                        {
                            var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == alterColumn.Table);
                            AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                        }
                        else if (thisVersionEntityTypes!.Any(et => et.GetTableName() == alterColumn.Table))
                        {
                            AddTableDropTableViewToTempDict(tempViewOperationsDict, alterColumn.Table);
                        }
                    }
                }
                else if (columnOperation is DropColumnOperation dropColumn)
                {
                    // 升级删除列
                    if (isUp)
                    {
                        // 当前版本仍然是依赖项逻辑删除实体，说明被删除的列和依赖项逻辑删除无关，重建视图
                        if (thisVersionEntityTypes!.Any(et => et.GetTableName() == dropColumn.Table))
                        {
                            var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == dropColumn.Table);
                            AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                        }
                        // 被删除的列是依赖项逻辑删除（上一版本的实体是依赖项逻辑删除，但当前版本不是），删除视图
                        else if (previousVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
                        {
                            AddTableDropTableViewToTempDict(tempViewOperationsDict, dropColumn.Table);
                        }
                    }

                    // 回滚删除列
                    if (!isUp)
                    {
                        EnsureMigrationOfPreviousVersion(previousVersion);
                        // 上一版本是依赖项逻辑删除实体，说明被删除的列和依赖项逻辑删除无关，重建视图
                        if (previousVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
                        {
                            var entity = previousVersionEntityTypes.Single(en => en.GetTableName() == dropColumn.Table);
                            AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                        }
                        // 被删除的列是依赖项逻辑删除（上一版本的实体不是依赖项逻辑删除，但当前版本是），删除视图
                        else if (thisVersionEntityTypes?.Any(et => et.GetTableName() == dropColumn.Table) is true)
                        {
                            AddTableDropTableViewToTempDict(tempViewOperationsDict, dropColumn.Table);
                        }
                    }
                }
            }
        }

        // 聚合所有操作，然后选择其中合理的一个作为最终操作
        foreach (var entityViewOperations in tempViewOperationsDict)
        {
            // 理论上来说如果上面的代码没有问题，针对同一张表的操作应该不会出现矛盾
            Debug.Assert(entityViewOperations.Value.All(x => x.isCreate == entityViewOperations.Value.First().isCreate));
            pendingViewOperations.Add(entityViewOperations.Value.First());
        }

        // 检查是依赖项逻辑删除但没有出现在操作列表中的实体
        // 这种实体可能由于依赖的主实体存在迁移操作，导致现有视图过时，需要追加到重建视图的操作列表中
        // 这种情况只会出现在所依赖的主实体从同时是逻辑删除和依赖项逻辑删除实体变成只是其中一种或者从原来是其中一种变成另外一种
        // 或者逻辑删除和依赖项逻辑删除列被改名
        // 主实体的其他列改动与当前实体无关，当前实体的视图对主实体的依赖仅限于主实体的逻辑删除（来自表或视图）和依赖项逻辑删除（一定是视图）（如果主实体也依赖于它的主实体）
        // 主实体从都不是变成至少是其中一种需要在从实体上添加依赖项逻辑删除接口以实现功能，会导致迁移至少会增加一列，因此从实体自身必然会出现在添加列操作中
        // 主实体从至少是其中一种变成都不是，模型构建阶段从实体上的依赖项逻辑删除接口就会抛出异常提示取消对接口的实现，会导致迁移至少会删除一列，因此从实体自身必然会出现在删除列操作中

        // 收集所有添加、删除和改名列操作并按照表分组备用
        var tableColumnOperationsGroupingforTransitiveDependencyCheck = migrationBuilder.Operations
            .Where(static op =>
            {
                var opType = op.GetType();
                return opType.IsDerivedFrom<AddColumnOperation>()
                    // 如果是修改列名，也可能需要重建视图
                    // 在模型上单独配置接口属性的列名映射时需要重建视图
                    // 其他无关列的改名无关紧要，但是因为在这里没有对应的实体属性信息，改名后无法确定哪个是逻辑删除属性对应的列名，只能全部收集后在之后匹配
                    || opType.IsDerivedFrom<AlterColumnOperation>()
                    || opType.IsDerivedFrom<DropColumnOperation>();
            });

        // 收集存在视图操作的实体
        var pendingViewOperationEntities = pendingViewOperations
            .Select(pop =>
                pop.entity ?? (isUp ? thisVersion.TargetModel : previousVersion?.TargetModel)
                ?.GetEntityTypes()
                .SingleOrDefault(et => et.GetTableName()! == (pop.tableName ?? pop.entity!.GetTableName())))
            .Where(static pop => pop is not null)
            .ToList();

        // 收集自身不在操作列表中，但依赖的主实体在操作列表中的实体
        // 升级迁移收集当前版本的迁移实体，回滚迁移收集上一版本的迁移实体
        var principalInPendingViewOperationEntities = (isUp ? thisVersionEntityTypes : previousVersionEntityTypes)
            ?.Where(et => !pendingViewOperationEntities.Contains(et))
            .Where(et =>
                et.GetForeignKeys()
                    .Select(static fk => fk.PrincipalEntityType)
                    .Any(pet => pendingViewOperationEntities.Contains(pet))
            )
            .ToList() ?? [];

        // 把这些实体加入视图重建列表
        pendingViewOperations.AddRange(principalInPendingViewOperationEntities?.Select(et => ((IEntityType?)et, (string?)null, true)) ?? []);

        foreach (var (entity, tableName, _) in pendingViewOperations.Where(op => !op.isCreate))
        {
            if (entity is not null) migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(entity, sqlTemplate);
            else if (tableName is not null) migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(tableName, sqlTemplate);
            else throw new InvalidOperationException("迁移实体类型和迁移表名不能同时为 null。");
        }

        migrationBuilder.CreateDependencyLogicallyDeletableEntityQueryViewsByDataSourceDependency(
            pendingViewOperations.Where(op => op.isCreate).Select(op => op.entity!),
            sqlTemplate);

        return migrationBuilder;

        /// <summary>
        /// 确保提供了上一版本的迁移
        /// </summary>
        static void EnsureMigrationOfPreviousVersion(Migration? previousVersion)
        {
            if (previousVersion is null) throw new InvalidOperationException($"回滚操作指出存在更早版本的迁移，但未提供上一版本的迁移。");
        }

        /// <summary>
        /// 向按表分组的临时操作存放字典添加创建实体视图命令
        /// </summary>
        static void AddEntityCreateEntityViewToTempDict(Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>> tempViewOperationsDict, IEntityType entity)
        {
            if (!tempViewOperationsDict.TryGetValue(entity.GetTableName()!, out var result))
            {
                result ??= [];
                tempViewOperationsDict.Add(entity.GetTableName()!, result);
            }
            result.Add((entity, null, true));
        }

        /// <summary>
        /// 向按表分组的临时操作存放字典添加删除表视图命令
        /// </summary>
        static void AddTableDropTableViewToTempDict(Dictionary<string, List<(IEntityType? entity, string? tableName, bool isCreate)>> tempViewOperationsDict, string tableName)
        {
            if (!tempViewOperationsDict.TryGetValue(tableName, out var result))
            {
                result ??= [];
                tempViewOperationsDict.Add(tableName, result);
            }
            result.Add((null, tableName, false));
        }
    }

    /// <summary>
    /// 创建依赖项逻辑删除实体查询视图
    /// </summary>
    /// <param name="migrationBuilder">迁移构造器</param>
    /// <param name="entityType">实体类型</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>迁移构造器</returns>
    public static MigrationBuilder CreateDependencyLogicallyDeletableEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

        var (sql, _, _) = CreateDependencyLogicallyDeletableEntityQueryViewSql(entityType, sqlTemplate);
        migrationBuilder.Sql(sql);
        return migrationBuilder;
    }

    /// <summary>
    /// 创建依赖项逻辑删除视图并对视图创建进行排序，确保被依赖的主实体视图优先创建
    /// </summary>
    /// <param name="migrationBuilder">迁移构造器</param>
    /// <param name="entityTypes">实体类型集合</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>迁移构造器</returns>
    public static MigrationBuilder CreateDependencyLogicallyDeletableEntityQueryViewsByDataSourceDependency(
        this MigrationBuilder migrationBuilder,
        IEnumerable<IEntityType> entityTypes,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        var viewSqls = entityTypes
            .Select(e => CreateDependencyLogicallyDeletableEntityQueryViewSql(e, sqlTemplate))
            .ToImmutableList();

        var viewNames = viewSqls
            .Select(vs => vs.viewName)
            .ToImmutableList();
        var rootViewSqls = viewSqls.Where(x =>
            x.principalDataSourceNames
                .All(ds => !ds.isViewDependency || !viewNames.Contains(ds.principalDataSourceName)));
        var viewSqlTrees = rootViewSqls.Select(rv =>
            rv.AsHierarchical(v =>
                viewSqls.Where(vs =>
                    vs.principalDataSourceNames
                        .Select(static dsn => dsn.principalDataSourceName)
                        .Contains(v.viewName))));
        var orderedViewSqls = viewSqlTrees
            .SelectMany(tr => tr.AsEnumerable())
            .GroupBy(h => h.Current.viewName)
            .Select(hg => hg.OrderByDescending(h => h.Level).First())
            .OrderBy(h => h.Level)
            .Select(h => h.Current);

        foreach (var (sql, _, _) in orderedViewSqls) migrationBuilder.Sql(sql);

        return migrationBuilder;
    }

    /// <summary>
    /// 删除依赖项逻辑删除实体查询视图
    /// </summary>
    /// <param name="migrationBuilder">迁移构造器</param>
    /// <param name="entityType">实体类型</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>迁移构造器</returns>
    public static MigrationBuilder DropDependencyLogicallyDeletableEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

        return migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(entityType.GetTableName()!, sqlTemplate);
    }

    /// <summary>
    /// 删除依赖项逻辑删除实体查询视图
    /// </summary>
    /// <param name="migrationBuilder">迁移构造器</param>
    /// <param name="tableName">视图对应的表名</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>迁移构造器</returns>
    public static MigrationBuilder DropDependencyLogicallyDeletableEntityQueryView(
        this MigrationBuilder migrationBuilder,
        string tableName,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(sqlTemplate);
        if (string.IsNullOrEmpty(tableName))
        {
            throw new ArgumentException($"“{nameof(tableName)}”不能为 null 或空。", nameof(tableName));
        }

        migrationBuilder.Sql(sqlTemplate.DropSqlTemplate.Replace("{tableName}", tableName));

        return migrationBuilder;
    }

    /// <summary>
    /// 创建依赖项逻辑删除视图
    /// </summary>
    /// <param name="entityType">实体类型</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>视图创建Sql、视图名称、依赖数据源名称以及依赖类型的集合，为视图创建排序提供线索</returns>
    private static (string sql, string viewName, IReadOnlyList<(string principalDataSourceName, bool isViewDependency)> principalDataSourceNames) CreateDependencyLogicallyDeletableEntityQueryViewSql(
        IEntityType entityType,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

        var isDependencyLogicallyDeletableEntity = (entityType
            .FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
            ?.Any(static x => x == typeof(IDependencyLogicallyDeletable)) is true;

        if (!isDependencyLogicallyDeletableEntity) throw new InvalidOperationException($"{entityType.Name}不是依赖项逻辑删除实体或未配置视图生成。");

        var tableName = entityType.GetTableName()!;
        var formatTableName = sqlTemplate.FormatTableOrColumnName(tableName)!;

        var tableIdentifier = StoreObjectIdentifier.Table(tableName)!;
        var columnEnumerable = entityType.GetProperties()
            .Where(static prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is not true)
            .Select(prop => $"{formatTableName}.{sqlTemplate.FormatTableOrColumnName(prop.GetColumnName(tableIdentifier))}");
        var columns = string.Join(", ", columnEnumerable);

        var foreignKeys = entityType.GetForeignKeys()
            .Where(static fk => fk.DeleteBehavior is DeleteBehavior.Cascade or DeleteBehavior.SetNull)
            .Where(static fk =>
            {
                var annotationValue = fk.PrincipalEntityType
                    .FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>;
                return annotationValue
                    ?.Any(static t =>
                        t == typeof(ILogicallyDeletable) || t == typeof(IDependencyLogicallyDeletable)) is true;
            })
            .Select(fk => BuildJoinInfo(entityType, fk))
            .ToList();

        if (foreignKeys.Count == 0) throw new InvalidOperationException($"实体{entityType.Name}没有支持逻辑删除或依赖项逻辑删除的级联外键，无需实现{nameof(IDependencyLogicallyDeletable)}接口。");

        var principalLogicallyDeleteColumns = string.Join(
            ", ",
            foreignKeys
                .SelectMany(p => BuildPrincipalDataSources(p.Key))
                .Select(x => sqlTemplate.PrincipalLogicallyDeleteColumnTemplate.Replace("{principalLogicallyDeleteColumn}", x))
        );

        var joinTargetEnumerable = foreignKeys.Select(p =>
        {
            var formatPrincipalDataSourceName = sqlTemplate.FormatTableOrColumnName(p.Key.principalDataSourceName)!;
            var conditions = p.Value.Select(x => $"{formatTableName}.{sqlTemplate.FormatTableOrColumnName(x.foreignKeyName)} = {formatPrincipalDataSourceName}.{sqlTemplate.FormatTableOrColumnName(x.principalKeyName)}");
            return sqlTemplate.JoinTargetTemplate
                .Replace("{principalDataSource}", formatPrincipalDataSourceName)
                .Replace("{joinCondition}", string.Join(" AND ", conditions));
        });

        var joinTargets = string.Join("\r\n", joinTargetEnumerable);

        var dependencyDeletedAtProperty = entityType.GetProperties()
            .Single(static prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is true);

        var sql = sqlTemplate.CreateSqlTemplate
            .Replace("{tableName}", tableName)
            .Replace("{columns}", columns)
            .Replace("{dependencyDeletedAtColumn}", sqlTemplate.FormatTableOrColumnName(dependencyDeletedAtProperty.GetColumnName(tableIdentifier)))
            .Replace("{principalLogicallyDeleteColumns}", principalLogicallyDeleteColumns)
            .Replace("{joinTargets}", joinTargets);

        return (sql, entityType.GetViewName()!, foreignKeys.Select(x => (x.Key.principalDataSourceName, x.Key.isViewDependency)).ToImmutableList());

        static KeyValuePair<(string principalDataSourceName, string? principalLogicallyDeletableColumnName, string? principalDependencyLogicallyDeletableColumnName, bool isViewDependency), IReadOnlyList<(string foreignKeyName, string principalKeyName)>> BuildJoinInfo(IEntityType entityType, IForeignKey foreignKey)
        {
            if (foreignKey.Properties.Count != foreignKey.PrincipalKey.Properties.Count) throw new InvalidOperationException($"外键和主键字段数量不一致。外键实体：{entityType.Name}；主实体：{foreignKey.PrincipalEntityType.Name}");

            var principalEntityType = foreignKey.PrincipalEntityType;

            var principalIsDependencyLogicallyDeletableEntity = (principalEntityType
                .FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                ?.Any(static t => t == typeof(IDependencyLogicallyDeletable)) is true;

            var principalDataSourceName = principalIsDependencyLogicallyDeletableEntity
                ? principalEntityType.GetViewName()!
                : principalEntityType.GetTableName()!;
            var principalDataSourceIdentifier = principalIsDependencyLogicallyDeletableEntity
                ? StoreObjectIdentifier.View(principalDataSourceName)
                : StoreObjectIdentifier.Table(principalDataSourceName);

            var foreignTableName = entityType.GetTableName()!;
            var foreignTableIdentifier = StoreObjectIdentifier.Table(foreignTableName);
            var foreignKeyNames = foreignKey.Properties.Select(prop => prop.GetColumnName(foreignTableIdentifier)!);
            var principalKeyNames = foreignKey.PrincipalKey.Properties.Select(prop => prop.GetColumnName(principalDataSourceIdentifier)!);

            var joinConditionPairs = foreignKeyNames.Zip(principalKeyNames, (fk, pk) => (foreignKeyName: fk, principalKeyName: pk));

            return KeyValuePair.Create(
                (
                    principalDataSourceName,
                    principalEntityType.GetProperties()
                        .SingleOrDefault(prop => prop.FindAnnotation(EntityModelBuilderExtensions._logicallyDeleteableAnnotationName)?.Value is true)
                        ?.GetColumnName(principalDataSourceIdentifier),
                    principalEntityType.GetProperties()
                        .SingleOrDefault(prop => prop.FindAnnotation(EntityModelBuilderExtensions._dependencyLogicallyDeleteableAnnotationName)?.Value is true)
                        ?.GetColumnName(principalDataSourceIdentifier),
                    principalIsDependencyLogicallyDeletableEntity
                ),
                joinConditionPairs.ToImmutableList() as IReadOnlyList<(string, string)>
            );
        }

        IEnumerable<string> BuildPrincipalDataSources((string principalDataSourceName, string? principalLogicallyDeletableColumnName, string? principalDependencyLogicallyDeletableColumnName, bool _) val)
        {
            if (val.principalLogicallyDeletableColumnName is not null)
                yield return $"{sqlTemplate.FormatTableOrColumnName(val.principalDataSourceName)}.{sqlTemplate.FormatTableOrColumnName(val.principalLogicallyDeletableColumnName)}";
            if (val.principalDependencyLogicallyDeletableColumnName is not null)
                yield return $"{sqlTemplate.FormatTableOrColumnName(val.principalDataSourceName)}.{sqlTemplate.FormatTableOrColumnName(val.principalDependencyLogicallyDeletableColumnName)}";
        }
    }
}

迁移扩展中对视图操作的排序用到了树形结构，感兴趣的朋友可以查看笔者的早期博客C# 通用树形数据结构了解详细信息。

数据库上下文

public class ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : ApplicationIdentityDbContext<
        ApplicationUser,
        ApplicationRole,
        IdentityKey,
        ApplicationUserClaim,
        ApplicationUserRole,
        ApplicationUserLogin,
        ApplicationRoleClaim,
        ApplicationUserToken>(options)
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 其他无关代码

        // 自动根据数据库类型进行数据库相关的模型配置
        switch (Database.ProviderName)
        {
            case _msSqlServerProvider:
                modelBuilder.ApplyConfigurationsFromAssembly(
                    typeof(LogRecordEntityTypeConfiguration).Assembly,
                    type => type.GetCustomAttributes<DatabaseProviderAttribute>().Any(a => a.ProviderName is _msSqlServerProvider));

                modelBuilder.ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(DefaultSqlServerDependencyLogicallyDeletableNullValueSql.Instance);
                break;
            case _pomeloMySqlProvider:
                modelBuilder.ApplyConfigurationsFromAssembly(
                    typeof(LogRecordEntityTypeConfiguration).Assembly,
                    type => type.GetCustomAttributes<DatabaseProviderAttribute>().Any(a => a.ProviderName is _pomeloMySqlProvider));

                modelBuilder.ConfigureForTimeAuditable(DefaultMySqlTimeAuditableDefaultValueSql.Instance);
                modelBuilder.ConfigureQueryFilterForILogicallyDeleteOrIDependencyLogicallyDeletable(DefaultMySqlDependencyLogicallyDeletableNullValueSql.Instance);
                break;
            case _msSqliteProvider:
                goto default;
            default:
                throw new NotSupportedException(Database.ProviderName);
        }

        modelBuilder.ConfigEntityQueryView();
    }
}

拦截器

/// <summary>
/// 把逻辑删除实体的删除变更为编辑，设置删除时间，然后使用<see cref="LogicallyDeletedRuntimeAnnotation"/>标记运行时注释便于区分普通的已编辑实体
/// </summary>
public class LogicallyDeletableSaveChangesInterceptor : SaveChangesInterceptor
{
    /// <summary>
    /// 逻辑删除实体的运行时注释名，注释的值为<see langword="true"/>
    /// </summary>
    public const string LogicallyDeletedRuntimeAnnotation = "Runtime:LogicallyDeleted";

    protected IServiceScopeFactory ScopeFactory { get; }

    public LogicallyDeletableSaveChangesInterceptor(IServiceScopeFactory scopeFactory)
    {
        ArgumentNullException.ThrowIfNull(scopeFactory);

        ScopeFactory = scopeFactory;
    }

    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        OnSavingChanges(eventData);
        return base.SavingChanges(eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        OnSavingChanges(eventData);
        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }

    public override int SavedChanges(SaveChangesCompletedEventData eventData, int result)
    {
        OnSavedChanges(eventData);
        return base.SavedChanges(eventData, result);
    }

    public override ValueTask<int> SavedChangesAsync(
        SaveChangesCompletedEventData eventData,
        int result,
        CancellationToken cancellationToken = default)
    {
        OnSavedChanges(eventData);
        return base.SavedChangesAsync(eventData, result, cancellationToken);
    }

    public override void SaveChangesCanceled(DbContextEventData eventData)
    {
        OnSaveChangesCanceled(eventData);
        base.SaveChangesCanceled(eventData);
    }

    public override Task SaveChangesCanceledAsync(
        DbContextEventData eventData,
        CancellationToken cancellationToken = default)
    {
        OnSaveChangesCanceled(eventData);
        return base.SaveChangesCanceledAsync(eventData, cancellationToken);
    }

    public override void SaveChangesFailed(DbContextErrorEventData eventData)
    {
        OnSaveChangesFailed(eventData);
        base.SaveChangesFailed(eventData);
    }

    public override Task SaveChangesFailedAsync(
        DbContextErrorEventData eventData,
        CancellationToken cancellationToken = default)
    {
        OnSaveChangesFailed(eventData);
        return base.SaveChangesFailedAsync(eventData, cancellationToken);
    }

    protected virtual void OnSavingChanges(DbContextEventData eventData)
    {
        ArgumentNullException.ThrowIfNull(eventData.Context);

        using var scope = ScopeFactory.CreateScope();
        var timeProvider = scope.ServiceProvider.GetRequiredService<TimeProvider>();

        eventData.Context.ChangeTracker.DetectChanges();

        // 按实体元数据分组
        var typedEntries = eventData.Context.ChangeTracker.Entries()
            .Where(static entry => entry.State is EntityState.Deleted)
            .GroupBy(static entry => entry.Metadata);

        foreach (var entries in typedEntries)
        {
            // 相同元数据的不同上下文对象使用不同的逻辑删除缓存
            // 实体模型的运行时元数据会通过逻辑删除缓存持有上下文对象的引用，需要在所有保存拦截方法中处理缓存并删除引用
            var logicalDeletedCacheDictionary = entries.Key
                .GetOrAddRuntimeAnnotationValue<ConcurrentDictionary<DbContext, HashSet<EntityEntry>>, object?>(
                    LogicallyDeletedRuntimeAnnotation,
                    static dbContext => [],
                    null);
            var logicalDeletedCache = logicalDeletedCacheDictionary.GetOrAdd(eventData.Context, []);

            foreach (var entry in entries)
            {
                if (entry.Entity is ILogicallyDeletable logicallyDeletable)
                {
                    entry.State = EntityState.Modified;
                    logicallyDeletable.DeletedAt = timeProvider.GetLocalNow();

                    // 使用运行时注释缓存逻辑删除的实体
                    logicalDeletedCache.Add(entry);
                }
            }
        }
    }

    protected virtual void OnSavedChanges(SaveChangesCompletedEventData eventData)
    {
        // 保存成功时需要解除对实体的跟踪，其他情况无需处理
        PostProcessEntriesAndCleanLogicalDeletedCache(eventData, static entry => entry.State = EntityState.Detached);
    }

    protected virtual void OnSaveChangesCanceled(DbContextEventData eventData)
    {
        PostProcessEntriesAndCleanLogicalDeletedCache(eventData);
    }

    protected virtual void OnSaveChangesFailed(DbContextEventData eventData)
    {
        PostProcessEntriesAndCleanLogicalDeletedCache(eventData);
    }

    /// <summary>
    /// 获取已逻辑删除的实体
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="eventData"></param>
    /// <returns>已逻辑删除的实体集合</returns>
    protected static IReadOnlyList<EntityEntry> GetLogicallyDeletedEntries<TEntity>(DbContextEventData eventData)
        where TEntity : class
    {
        var dict = eventData.Context?.Model
            .FindEntityType(typeof(TEntity))
            ?.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>;

        var entities = dict
            ?.GetValueOrDefault(eventData.Context!)
            ?.Where(static e => e.Entity is TEntity)
            ?.ToImmutableArray() ?? [];

        return entities;
    }

    /// <summary>
    /// 获取已逻辑删除的实体
    /// </summary>
    /// <param name="eventData"></param>
    /// <returns>已逻辑删除的实体集合</returns>
    protected static IReadOnlyList<EntityEntry> GetLogicallyDeletedEntries(DbContextEventData eventData)
    {
        var entities = eventData.Context?.Model
            .GetEntityTypes()
            .Select(static et => et.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>)
            .Where(static dict => dict is not null)
            .Select(dict => dict!.GetValueOrDefault(eventData.Context!))
            .Where(static hs => hs is { Count: > 0 })
            .SelectMany(static hs => hs!)
            .ToImmutableArray() ?? [];

        return entities;
    }

    /// <summary>
    /// 在保存后事件调用，执行自定义实体处理，然后清除运行时元数据注释的缓存，避免内存泄漏
    /// </summary>
    /// <param name="eventData">事件数据</param>
    /// <param name="action">自定义处理委托</param>
    protected static void PostProcessEntriesAndCleanLogicalDeletedCache(DbContextEventData eventData, Action<EntityEntry>? action = null)
    {
        ArgumentNullException.ThrowIfNull(eventData.Context);

        var entrySetDict = eventData.Context.ChangeTracker.Entries()
            .GroupBy(static e => e.Metadata)
            .Select(static group => group.Key.FindRuntimeAnnotationValue(LogicallyDeletedRuntimeAnnotation) as ConcurrentDictionary<DbContext, HashSet<EntityEntry>>)
            .Where(static dict => dict is not null)
            .ToList();

        var entrySets = entrySetDict
            .Select(dict => dict!.GetValueOrDefault(eventData.Context))
            .Where(static set => set is not null);

        foreach (var set in entrySets)
        {
            foreach (var entry in set!)
            {
                action?.Invoke(entry);
            }

            set.Clear();
        }

        // 清空当前上下文的逻辑删除缓存避免内存泄漏
        foreach (var dict in entrySetDict)
        {
            dict!.TryRemove(eventData.Context, out var _);
        }
    }
}

/// <summary><inheritdoc cref="LogicallyDeletableSaveChangesInterceptor"/></summary>
/// <typeparam name="TUser">用户实体类型</typeparam>
/// <typeparam name="TKey">用户实体主键类型</typeparam>
/// <param name="scopeFactory"></param>
/// <remarks>为 Identity 实体的唯一索引属性设置特别删除标记</remarks>
public class IdentityLogicallyDeletableSaveChangesInterceptor<TUser, TKey>(IServiceScopeFactory scopeFactory)
    : LogicallyDeletableSaveChangesInterceptor(scopeFactory)
    where TUser : IdentityUser<TKey>, ILogicallyDeletable
    where TKey : IEquatable<TKey>
{
    private const string _delMark = "!del";

    /// <summary>
    /// 把已删除的用户的特殊属性增加标记，避免无法创建同名用户和查询出已删除的同名用户
    /// </summary>
    /// <param name="eventData"></param>
    protected override void OnSavingChanges(DbContextEventData eventData)
    {
        // 此处会把应该逻辑删除的已删除实体调整为已修改，不能通过ChangeTracker找到已逻辑删除的实体
        base.OnSavingChanges(eventData);

        var entityEntries = GetLogicallyDeletedEntries<TUser>(eventData);

        foreach (var entry in entityEntries)
        {
            var entity = entry.Entity as TUser;

            entity!.Email += _delMark + entity.DeletedAt.Ticks;
            entity.NormalizedEmail += _delMark.ToUpperInvariant() + entity.DeletedAt.Ticks;
            entity.UserName += _delMark + entity.DeletedAt.Ticks;
            entity.NormalizedUserName += _delMark.ToUpperInvariant() + entity.DeletedAt.Ticks;
        }
    }
}

服务配置

services.AddPooledDbContextFactory<ApplicationDbContext>((sp, options) =>
{
    if (sp.GetRequiredService<IWebHostEnvironment>().IsDevelopment())
    {
        options.EnableSensitiveDataLogging();
        options.EnableDetailedErrors();
    }

    // 注册拦截器
    var scopeFactory = sp.GetRequiredService<IServiceScopeFactory>();
    options.AddInterceptors(new IdentityLogicallyDeletableSaveChangesInterceptor<ApplicationUser, IdentityKey>(scopeFactory));

    ConfigureDbConnection(options, sp);
});

迁移

/// <inheritdoc />
public partial class V0002 : Migration
{
    /// <inheritdoc />
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // 其他迁移工具生成的代码

        migrationBuilder.ApplyEntityQueryView(
            this, // 当前迁移
            new V0001() { ActiveProvider = this.ActiveProvider }, // 上一个迁移
            true, // 是升级迁移
            DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate.Instance,
            DefaultSqlServerTreeEntityViewSqlTemplate.Instance); // 下篇会详细说明
    }

    /// <inheritdoc />
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // 其他迁移工具生成的代码

        migrationBuilder.ApplyEntityQueryView(
            this,
            new V0001() { ActiveProvider = this.ActiveProvider },
            false, // 是回滚迁移
            DefaultSqlServerDependencyLogicallyDeletableEntityViewSqlTemplate.Instance,
            DefaultSqlServerTreeEntityViewSqlTemplate.Instance);
    }
}

此处以第二版迁移为例，方便演示回滚迁移所需的参数。只需要调用这一个扩展方法就可以完成视图的自动迁移，生成的迁移脚本也是完整的，可以直接使用。

唯一的遗憾是模型差异比较需要在迁移处现场完成，为此需要手动提供上一个迁移模型。如果要完全在框架中完成比较和代码生成，需要改动的东西太多也很麻烦，权衡之后决定自行在迁移中实现。如果哪天EF Core把这方面的功能做成类似拦截器那种不需要继承现有类型就能插入迁移代码生成流程的话能方便不少。

由于EF Core支持很多复杂的模型映射，迁移扩展可能还有没覆盖到的情况，目前只能说常规映射基本正常。

这个实现套路也是参考了笔者之前的一篇旧文EntityFramework Core 2.x/3.x （ef core） 在迁移中自动生成数据库表和列说明。不过现在注释已经是内置迁移功能的一部分，因此无需手动实现。参考项目中保留了相关代码，只不过把实现方案换成了挂接到内置实现，到模型构造的部分依然不变，但不再需要手动调整迁移代码，变成真正的一次编写永久使用。

迁移脚本预览（节选）

-- 同时依赖多个主实体的情况
CREATE VIEW QueryView_Entity2_1s
AS
SELECT [Entity2_1s].[Id], [Entity2_1s].[DeletedAt], [Entity2_1s].[Depth], [Entity2_1s].[Entity1_1_1Id], [Entity2_1s].[Entity2Id], [Entity2_1s].[Entity2_0Id], [Entity2_1s].[HasChildren], [Entity2_1s].[IsLeaf], [Entity2_1s].[IsRoot], [Entity2_1s].[ParentId], [Entity2_1s].[Path], [Entity2_1s].[Text2_1],
    (SELECT MAX([DeleteTimeTable].[DeletedAt])
        FROM (VALUES ([Entity1_1_1s].[DeletedAt]), ([Entity2_0s].[DeletedAt])) AS DeleteTimeTable([DeletedAt])) AS [DependencyDeletedAt]
FROM [Entity2_1s]
LEFT JOIN [Entity1_1_1s]
ON [Entity2_1s].[Entity1_1_1Id] = [Entity1_1_1s].[Id]
LEFT JOIN [Entity2_0s]
ON [Entity2_1s].[Entity2_0Id] = [Entity2_0s].[Id];
GO

IF EXISTS(SELECT * FROM [sysobjects] WHERE [id] = OBJECT_ID(N'QueryView_Entity3s') AND objectproperty(id, N'IsView') = 1)
BEGIN
    DROP VIEW [QueryView_Entity3s]
END
GO

-- 依赖的主实体也有视图的情况
CREATE VIEW QueryView_Entity3s
AS
SELECT [Entity3s].[Id], [Entity3s].[Entity1_1Id], [Entity3s].[Text2],
    (SELECT MAX([DeleteTimeTable].[DeletedAt])
        FROM (VALUES ([QueryView_Entity1_1s].[DeletedAt]), ([QueryView_Entity1_1s].[MyDependencyDeletedAt])) AS DeleteTimeTable([DeletedAt])) AS [DependencyDeletedAt]
FROM [Entity3s]
LEFT JOIN [QueryView_Entity1_1s]
ON [Entity3s].[Entity1_1Id] = [QueryView_Entity1_1s].[Id];
GO

外键和主实体是复合列的情况也是支持的，只是在示例中没有用上，之前单独的开发测试时验证过，如果不出意外应该是不会坏掉。

结语

经过这一系列的操作，最终实现了软删除和级联软删除的自动化且能有效模拟级联删除的行为，在数据库端也能尽可能简化查询的编写。利用EF Core的各种高级功能最终实现了对业务代码的0侵入。

示例代码：SoftDeleteDemo.rar。主页显示异常请在libman.json上右键恢复前端包。

QQ群

读者交流QQ群：540719365

欢迎读者和广大朋友一起交流，如发现本书错误也欢迎通过博客园、QQ群等方式告知笔者。

本文地址：https://www.cnblogs.com/coredx/p/18305274.html