論如何直接用EF Core實現建立更新時間、使用者審計，自動化樂觀併發、軟刪除和樹形查詢（下）

coredx發表於2024-07-19

前言

資料庫併發，資料審計和軟刪除一直是資料持久化方面的經典問題。早些時候，這些工作需要手寫複雜的SQL或者透過儲存過程和觸發器實現。手寫複雜SQL對軟體可維護性構成了相當大的挑戰，隨著SQL字數的變多，用到的巢狀和複雜語法增加，可讀性和可維護性的難度是幾何級暴漲。因此如何在實現功能的同時控制這些SQL的複雜度是一個很有價值的問題。而且這個問題同時涉及應用軟體和資料庫兩個相對獨立的體系，平行共管也是產生混亂的一大因素。

EF Core作為 .NET平臺的高階ORM框架，可以託管和資料庫的互動，同時提供了大量擴充套件點方便自定義。以此為基點把對資料庫的操作託管後便可以解決平行共管所產生的混亂，利用LINQ則可以最大程度上降低軟體程式碼的維護難度。

由於專案需要，筆者先後開發併發布了通用的基於EF Core儲存的國際化服務和基於EF Core儲存的Serilog持久化服務，不過這兩個功能包並沒有深度利用EF Core，雖然主要是因為沒什麼必要。但是專案還需要提供常用的資料審計和軟刪除功能，因此對EF Core進行了一些更深入的研究。

起初有考慮過是否使用現成的ABP框架來處理這些功能，但是在其他專案的使用體驗來說並不算好，其中充斥著大量上下文依賴的功能，而且這些依賴資訊能輕易藏到和最終業務程式碼相距十萬八千里的地方（特別是程式碼還是別人寫的時候），然後在不經意間給你一個大驚喜。對於以程式碼正交性、非誤導性，純函式化為追求的一介碼農（看過我釋出的那兩個功能包的朋友應該有感覺，一個功能筆者也要根據用途劃分為不同的包，確保解決方案中的各個專案都能按需引用，不會殘留無用的程式碼），實在是喜歡不起來ABP這種全家桶。

鑑於專案規模不大，筆者決定針對這些需求做一個專用功能，目標是儘可能減少依賴，方便將來複用到其他專案，降低和其他功能功能衝突的風險。現在筆者將用一系列部落格做成果展示。由於這些功能沒有經過大範圍測試，不確定是否存在未知缺陷，因此暫不打包釋出。

新書宣傳

有關新書的更多介紹歡迎檢視《C#與.NET6 開發從入門到實踐》上市，作者親自來打廣告了！

正文

由於這些功能設計的程式碼量和知識點較多，為控制篇幅，本文介紹樹形查詢功能。

SqlServer原生支援分層資料，EF Core也提供了相應的支援，但是很遺憾，這又是一個獨佔功能。為了相容其他資料庫只能單獨處理。由於EF Core的導航修復功能，使用ParentId的自關聯結構能得到原生支援。這也是描述一棵樹最簡單且不會破壞資料完整性的方式（即這種描述方式永遠滿足樹結構的所有判定約束）。但是在查詢方面，這種結構確並不方便，因此為了簡化查詢，出現了其他儲存樹的設計方式，常見的有左右值編碼、路徑描述和額外的關係描述表等。這些描述方式能在一定程度上簡化查詢，但是確無法在物理上確保資料完整性，這就對資料維護提出了嚴峻的挑戰。

在中篇我們用檢視實現了全自動的級聯軟刪除模擬，那麼是否同樣可以用檢視來解決樹形結構的查詢問題呢？答案是肯定的，而這隻有一個小小的前提條件——支援公用表表示式（SQL中的遞迴）。這樣就能實現物理表中使用ParentId的自關聯確保資料完整性，同時自動相容EF Core的導航修復。而用於簡化查詢的其他資訊則由檢視自動計算生成。

生成樹的檢視功能其實已經在本文宣傳的書中實現了，不過這次新增自動軟刪除後，樹檢視也需要考慮如何相容軟刪除。一開始筆者想過在一個檢視定義中實現，後來發現這種方式開發難度比較大，而且不利於複用已有的研究成果。最終決定使用獨立的檢視，這就涉及到檢視資料來源的選擇，因為EF Core只能對映一個檢視。經過一番思考發現樹形檢視永遠只依賴其自身的表或檢視，因此EF Core對映到樹形檢視，屬性檢視依賴軟刪除檢視是最簡單方便的。在之前介紹軟刪除的文章中已經出現了和樹有關的程式碼，這些程式碼的一部分用處就是選擇對映目標。

程式碼實現

基礎介面

/// <summary>
/// 樹形資料介面
/// </summary>
/// <typeparam name="T">節點資料型別</typeparam>
public interface ITree<T>
{
    /// <summary>
    /// 父節點
    /// </summary>
    T? Parent { get; set; }

    /// <summary>
    /// 子節點集合
    /// </summary>
    IList<T> Children { get; set; }

    /// <summary>
    /// 節點深度，根的深度為0
    /// </summary>
    int Depth { get; }

    /// <summary>
    /// 是否是根節點
    /// </summary>
    bool IsRoot { get; }

    /// <summary>
    /// 是否是葉節點
    /// </summary>
    bool IsLeaf { get; }

    /// <summary>
    /// 是否有子節點
    /// </summary>
    bool HasChildren { get; }

    /// <summary>
    /// 節點路徑（UNIX路徑格式，以“/”分隔）
    /// </summary>
    string? Path { get; }
}

/// <summary>
/// 樹形實體介面
/// </summary>
/// <typeparam name="T">實體型別</typeparam>
public interface ITreeEntity<T> : IEntity, ITree<T>
{
}

/// <summary>
/// 樹形實體介面
/// </summary>
/// <typeparam name="TKey">主鍵型別</typeparam>
/// <typeparam name="TEntity">實體型別</typeparam>
public interface ITreeEntity<TKey, TEntity> : ITreeEntity<TEntity>, IEntity<TKey>
    where TKey : struct, IEquatable<TKey>
    where TEntity : ITreeEntity<TKey, TEntity>
{
    /// <summary>
    /// 父節點Id
    /// </summary>
    TKey? ParentId { get; set; }
}

/// <summary>
/// 實體介面
/// </summary>
public interface IEntity;

/// <summary>
/// 實體介面
/// </summary>
/// <typeparam name="TKey">唯一標識的型別</typeparam>
public interface IEntity<TKey> : IEntity
    where TKey : struct, IEquatable<TKey>
{
    /// <summary>
    /// 實體的唯一標識
    /// </summary>
    TKey Id { get; set; }
}

本文的ITree<T>介面就是從前文軟刪除檢視操作排序用的介面簡化而來。

模型配置擴充套件

/// <summary>
/// 樹形實體模型配置擴充套件
/// </summary>
public static class TreeEntityModelBuilderExtensions
{
    private const string _queryViewAnnotationName = EntityModelBuilderExtensions._queryViewAnnotationName;

    /// <summary>
    /// 配置樹形實體介面
    /// </summary>
    /// <typeparam name="TKey">主鍵型別</typeparam>
    /// <typeparam name="TEntity">樹形實體型別</typeparam>
    /// <param name="builder">實體型別構造器</param>
    /// <param name="dummyValueSql">表用計算列的虛假值生成Sql</param>
    /// <returns>實體型別構造器</returns>
    public static EntityTypeBuilder<TEntity> ConfigureForITreeEntity<TKey, TEntity>(
        this EntityTypeBuilder<TEntity> builder,
        ITreeEntityDummyValueSql dummyValueSql
    )
        where TKey : struct, IEquatable<TKey>
        where TEntity : class, ITreeEntity<TKey, TEntity>
    {
        ArgumentNullException.ThrowIfNull(builder);

        builder.HasOne(e => e.Parent)
            .WithMany(pe => pe.Children)
            .HasForeignKey(e => e.ParentId);

        builder.Property(e => e.Depth)
            .HasComputedColumnSql(dummyValueSql.DepthSql);

        builder.Property(e => e.HasChildren)
            .HasComputedColumnSql(dummyValueSql.HasChildrenSql);

        builder.Property(e => e.Path)
            .HasComputedColumnSql(dummyValueSql.PathSql);

        ConfigQueryViewAnnotationForTreeEntity<TKey, TEntity>(builder);

        return builder;
    }

    /// <summary>
    /// 配置樹形實體介面
    /// </summary>
    /// <param name="modelBuilder">模型構造器</param>
    /// <param name="dummyValueSql">表用計算列的虛假值生成Sql</param>
    /// <returns>模型構造器</returns>
    public static ModelBuilder ConfigureForITreeEntity(this ModelBuilder modelBuilder, ITreeEntityDummyValueSql dummyValueSql)
    {
        ArgumentNullException.ThrowIfNull(modelBuilder);
        ArgumentNullException.ThrowIfNull(dummyValueSql);

        foreach (var entity
            in modelBuilder.Model.GetEntityTypes()
                .Where(static e => e.ClrType.IsDerivedFrom(typeof(ITreeEntity<,>))))
        {
            var entityTypeBuilderMethod = GetEntityTypeBuilderMethod(entity);
            var treeEntityMethod = GetEntityTypeConfigurationMethod(
                nameof(ConfigureForITreeEntity),
                2,
                entity.FindProperty(nameof(TreeType.Id))!.ClrType,
                entity.ClrType);

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

        return modelBuilder;
    }

    /// <summary>
    /// 配置樹形實體的查詢檢視註解
    /// </summary>
    /// <typeparam name="TKey">實體主鍵型別</typeparam>
    /// <typeparam name="TEntity">實體型別</typeparam>
    /// <param name="builder">實體型別構造器</param>
    private static void ConfigQueryViewAnnotationForTreeEntity<TKey, TEntity>(EntityTypeBuilder<TEntity> builder)
        where TKey : struct, IEquatable<TKey>
        where TEntity : class, ITreeEntity<TKey, TEntity>
    {
        var annotationValue = builder.Metadata.FindAnnotation(_queryViewAnnotationName)?.Value;
        if (annotationValue is null)
        {
            builder.HasAnnotation(_queryViewAnnotationName, new List<Type>() { typeof(ITreeEntity<,>) });
        }
        else
        {
            var stringListAnnotationValue = annotationValue as List<Type>;
            if (stringListAnnotationValue is not null && stringListAnnotationValue.Find(static x => x == typeof(ITreeEntity<,>)) is null)
            {
                stringListAnnotationValue.Add(typeof(ITreeEntity<,>));
            }
        }
    }
}

/// <summary>
/// 僅用於內部輔助，無實際作用
/// </summary>
file sealed class TreeType : ITreeEntity<int, TreeType>
{
    public TreeType()
    {
        throw new NotImplementedException();
    }

    public int? ParentId { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
    public TreeType? Parent { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
    public IList<TreeType> Children { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }

    public int Depth => throw new NotImplementedException();

    public bool IsRoot => throw new NotImplementedException();

    public bool IsLeaf => throw new NotImplementedException();

    public bool HasChildren => throw new NotImplementedException();

    public string? Path => throw new NotImplementedException();

    public int Id { get => throw new NotImplementedException(); set => throw new NotImplementedException(); }
}

Sql模版（以SqlServer為例）

/// <summary>
/// 樹形實體的檢視列在表中的臨時值對映
/// <para>EF Core目前還不支援多重對映時分別配置表和檢視的對映，因此需要在表中對映一個同名計算列</para>
/// </summary>
public interface ITreeEntityDummyValueSql
{
    /// <summary>
    /// 節點深度的SQL
    /// </summary>
    string DepthSql { get; }

    /// <summary>
    /// 節點是否有子樹的SQL
    /// </summary>
    string HasChildrenSql { get; }

    /// <summary>
    /// 節點路徑的SQL
    /// </summary>
    string PathSql { get; }
}

public class DefaultSqlServerTreeEntityDummyValueSql : ITreeEntityDummyValueSql
{
    public static DefaultSqlServerTreeEntityDummyValueSql Instance => new();

    private const string _depthSql = "-1";
    private const string _hasChildrenSql = "cast(0 as bit)";
    private const string _pathSql = "''";

    public string DepthSql => _depthSql;

    public string HasChildrenSql => _hasChildrenSql;

    public string PathSql => _pathSql;

    private DefaultSqlServerTreeEntityDummyValueSql() { }
}

/// <summary>
/// 樹形實體的檢視SQL模板
/// </summary>
public interface ITreeEntityDatabaseViewSqlTemplate : ITableOrColumnNameFormattable
{
    /// <summary>
    /// 建立檢視的模板
    /// </summary>
    string CreateSqlTemplate { get; }

    /// <summary>
    /// 刪除檢視的模板
    /// </summary>
    string DropSqlTemplate { get; }
}

public class DefaultSqlServerTreeEntityViewSqlTemplate : ITreeEntityDatabaseViewSqlTemplate
{
    public static DefaultSqlServerTreeEntityViewSqlTemplate Instance => new();

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

    private const string _createSqlTemplate =
        $$"""
        --建立或重建樹形實體查詢檢視
        {{_dropSqlTemplate}}
        CREATE VIEW {{_viewNameTemplate}}    --建立檢視
        AS
        WITH [temp]({columns}, [Depth], [Path], [HasChildren]) AS
        (
            --初始查詢（這裡的 [ParentId] IS NULL 在資料中是最底層的根節點）
            SELECT {columns},
                0 AS [Depth],
                '/' + CAST([Id] AS nvarchar(max)) + '/' AS [Path], --如果Id使用Guid型別，可能會導致層數太深時出問題（大概100層左右，超過4000字之後的字串會被砍掉,sqlserver 2005以後用 nvarchar(max)可以突破限制），Guid的字數太多了
                (CASE WHEN EXISTS(SELECT 1 FROM [{dataSourceName}] WHERE [{dataSourceName}].[ParentId] = [Root].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
            FROM [{dataSourceName}] AS [Root]
            WHERE [Root].[ParentId] IS NULL

            UNION ALL
            --遞迴條件
            SELECT {child.columns},
                [Parent].[Depth] + 1,
                [Parent].[Path] + CAST([Child].[Id] AS nvarchar(max)) + '/' AS [Path],
                (CASE WHEN EXISTS(SELECT 1 FROM [{dataSourceName}] WHERE [{dataSourceName}].[ParentId] = [Child].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
            FROM [{dataSourceName}] AS [Child] --3：這裡的臨時表和原始資料表都必須使用別名不然遞迴的時候不知道查詢的是哪個表的列
            INNER JOIN [temp] AS [Parent]
            ON ([Child].[ParentId] = [Parent].[Id]) --這個關聯關係很重要，一定要理解一下誰是誰的父節點
        )
        --4：遞迴完成後 一定不要少了這句查詢語句 否則會報錯
        SELECT *
        FROM [temp];
        GO
        """;

    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? FormatTableOrColumnName(string? name)
    {
        if(name is null) return null;

        return $"[{name}]";
    }

    private DefaultSqlServerTreeEntityViewSqlTemplate() { }
}

遷移擴充套件

/// <summary>
/// 樹形實體檢視遷移擴充套件
/// </summary>
public static class TreeEntityMigrationBuilderExtensions
{
    private static readonly ImmutableArray<string> _properties = ["Depth", "Path", "HasChildren"];

    /// <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 ApplyTreeEntityQueryView(
        this MigrationBuilder migrationBuilder,
        Migration thisVersion,
        Migration? previousVersion,
        bool isUp,
        ITreeEntityDatabaseViewSqlTemplate 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(ITreeEntity<,>)) is true
            );

        var previousVersionEntityTypes = previousVersion?.TargetModel.GetEntityTypes()
            .Where(static et =>
                (et.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                ?.Any(x => x == typeof(ITreeEntity<,>)) 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(en => en.GetTableName() == alterTable.OldTable.Name) is true)
                    {
                        pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
                    }

                    if (thisVersionEntityTypes!.Any(en => en.GetTableName() == alterTable.Name))
                    {
                        var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == alterTable.Name);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
                // 回滾遷移用上一個版本的模型重建檢視
                else
                {
                    // 如果當前版本這個實體是樹形實體，刪除舊檢視
                    if (thisVersionEntityTypes.Any(en => en.GetTableName() == alterTable.OldTable.Name))
                    {
                        pendingViewOperations.Add((null, alterTable.OldTable.Name, false));
                    }

                    EnsureMigrationOfPreviousVersion(previousVersion);
                    if (previousVersionEntityTypes!.Any(en => en.GetTableName() == 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 columnOperation in
            migrationBuilder.Operations.Where(static op =>
            {
                var opType = op.GetType();
                return opType.IsDerivedFrom<ColumnOperation>() || opType.IsDerivedFrom<DropColumnOperation>();
            }))
        {
            if (columnOperation is AddColumnOperation addColumn)
            {
                if (isUp && thisVersionEntityTypes!.Any(en => en.GetTableName() == addColumn.Table))
                {
                    var entity = thisVersionEntityTypes!.Single(en => en.GetTableName() == addColumn.Table);
                    AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                }

                if (!isUp)
                {
                    EnsureMigrationOfPreviousVersion(previousVersion);
                    if (previousVersionEntityTypes!.Any(en => en.GetTableName() == addColumn.Table))
                    {
                        var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == addColumn.Table);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
            }
            else if (columnOperation is AlterColumnOperation alterColumn/* && alterColumn.OldColumn.Name is not null && alterColumn.Name != alterColumn.OldColumn.Name*/)
            {
                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(en => en.GetTableName() == alterColumn.Table))
                    {
                        var entity = previousVersionEntityTypes!.Single(en => en.GetTableName() == alterColumn.Table);
                        AddEntityCreateEntityViewToTempDict(tempViewOperationsDict, entity);
                    }
                }
            }
            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));
            if (isUp)
            {
                // 如果當前版本的實體確實是樹形實體，選擇建立檢視的命令
                if ((thisVersionEntityTypes
                        ?.SingleOrDefault(et => et.GetTableName() == entityViewOperations.Key)
                        ?.FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                        ?.Any(x => x == typeof(ITreeEntity<,>)) is true)
                {
                    pendingViewOperations.Add(entityViewOperations.Value.First(o => o.entity is not null && o.isCreate));
                }
                else
                {
                    pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
                }
            }
            else
            {
                // 當前遷移就是第一版，選擇刪除檢視命令
                if (previousVersion is null)
                {
                    pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
                }
                // 如果上一版本的實體確實是樹形實體，選擇建立檢視的命令
                else if ((previousVersionEntityTypes
                            ?.Single(et => et.GetTableName() == entityViewOperations.Key)
                            .FindAnnotation(EntityModelBuilderExtensions._queryViewAnnotationName)?.Value as List<Type>)
                            ?.Any(x => x == typeof(IDependencyLogicallyDeletable)) is true)
                {
                    pendingViewOperations.Add(entityViewOperations.Value.First(o => o.entity is not null && o.isCreate));
                }
                else
                {
                    pendingViewOperations.Add(entityViewOperations.Value.First(o => !o.isCreate));
                }
            }
        }

        foreach (var (entity, tableName, isCreate) in pendingViewOperations)
        {
            if (isCreate) migrationBuilder.CreateTreeEntityQueryView(entity!, sqlTemplate);
            else if (entity is not null) migrationBuilder.DropTreeEntityQueryView(entity, sqlTemplate);
            else if (tableName is not null) migrationBuilder.DropTreeEntityQueryView(tableName, sqlTemplate);
            else throw new InvalidOperationException("遷移實體型別和遷移表名不能同時為 null。");
        }

        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 CreateTreeEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        ITreeEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

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

        if (!isTreeEntity) throw new InvalidOperationException($"{entityType.Name}不是樹形實體或未配置檢視生成。");

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

        var tableName = entityType.GetTableName()!;
        var dataSourceName = isDependencyLogicallyDeletableEntity
            ? $"{EntityModelBuilderExtensions._queryViewNamePrefixes}{tableName}"
            : tableName;

        var tableIdentifier = StoreObjectIdentifier.Table(tableName);

        var columnNames = entityType.GetProperties()
            .Where(static c => !_properties.Contains(c.Name))
            .Select(pro => sqlTemplate.FormatTableOrColumnName(pro.GetColumnName(tableIdentifier)));
        var childColumnNames = columnNames.Select(c => $@"{sqlTemplate.FormatTableOrColumnName("Child")}.{c}");

        migrationBuilder.Sql(sqlTemplate.CreateSqlTemplate
            .Replace("{tableName}", tableName)
            .Replace("{dataSourceName}", dataSourceName)
            .Replace("{columns}", string.Join(", ", columnNames))
            .Replace("{child.columns}", string.Join(", ", childColumnNames))
        );

        return migrationBuilder;
    }

    /// <summary>
    /// 刪除樹形實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="entityType">實體型別</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder DropTreeEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        ITreeEntityDatabaseViewSqlTemplate sqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(sqlTemplate);

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

    /// <summary>
    /// 刪除樹形實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="tableName">檢視對應的表名</param>
    /// <param name="sqlTemplate">Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder DropTreeEntityQueryView(
        this MigrationBuilder migrationBuilder,
        string tableName,
        ITreeEntityDatabaseViewSqlTemplate 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;
    }
}

public static class EntityMigrationBuilderExtensions
{
    /// <summary>
    /// 自動掃描遷移模型並配置實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="thisVersion">當前版本的遷移</param>
    /// <param name="previousVersion">上一個版本的遷移</param>
    /// <param name="isUp">是否為升級遷移</param>
    /// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體檢視Sql模板</param>
    /// <param name="treeEntityViewSqlTemplate">樹形實體檢視Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder ApplyEntityQueryView(
        this MigrationBuilder migrationBuilder,
        Migration thisVersion,
        Migration? previousVersion,
        bool isUp,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
        ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(thisVersion);
        ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
        ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);

        migrationBuilder.ApplyDependencyLogicallyDeletableEntityQueryView(
            thisVersion,
            previousVersion,
            isUp,
            dependencyLogicallyDeletableEntityViewSqlTemplate);

        migrationBuilder.ApplyTreeEntityQueryView(
            thisVersion,
            previousVersion,
            isUp,
            treeEntityViewSqlTemplate);

        return migrationBuilder;
    }

    /// <summary>
    /// 建立樹形實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="entityType">實體型別</param>
    /// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體檢視Sql模板</param>
    /// <param name="treeEntityViewSqlTemplate">樹形實體檢視Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder CreateEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
        ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
        ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);

        migrationBuilder.CreateDependencyLogicallyDeletableEntityQueryView(entityType, dependencyLogicallyDeletableEntityViewSqlTemplate);
        migrationBuilder.CreateTreeEntityQueryView(entityType, treeEntityViewSqlTemplate);

        return migrationBuilder;
    }

    /// <summary>
    /// 刪除實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="entityType">實體型別</param>
    /// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體檢視Sql模板</param>
    /// <param name="treeEntityViewSqlTemplate">樹形實體檢視Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder DropEntityQueryView(
        this MigrationBuilder migrationBuilder,
        IEntityType entityType,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
        ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(entityType);
        ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
        ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);

        return migrationBuilder.DropEntityQueryView(
            entityType.GetTableName()!,
            dependencyLogicallyDeletableEntityViewSqlTemplate,
            treeEntityViewSqlTemplate);
    }

    /// <summary>
    /// 刪除實體查詢檢視
    /// </summary>
    /// <param name="migrationBuilder">遷移構造器</param>
    /// <param name="tableName">檢視對應的表名</param>
    /// <param name="dependencyLogicallyDeletableEntityViewSqlTemplate">依賴項邏輯刪除實體檢視Sql模板</param>
    /// <param name="treeEntityViewSqlTemplate">樹形實體檢視Sql模板</param>
    /// <returns>遷移構造器</returns>
    public static MigrationBuilder DropEntityQueryView(
        this MigrationBuilder migrationBuilder,
        string tableName,
        IDependencyLogicallyDeletableEntityDatabaseViewSqlTemplate dependencyLogicallyDeletableEntityViewSqlTemplate,
        ITreeEntityDatabaseViewSqlTemplate treeEntityViewSqlTemplate)
    {
        ArgumentNullException.ThrowIfNull(migrationBuilder);
        ArgumentNullException.ThrowIfNull(dependencyLogicallyDeletableEntityViewSqlTemplate);
        ArgumentNullException.ThrowIfNull(treeEntityViewSqlTemplate);
        if (string.IsNullOrEmpty(tableName))
        {
            throw new ArgumentException($"“{nameof(tableName)}”不能為 null 或空。", nameof(tableName));
        }

        migrationBuilder.DropDependencyLogicallyDeletableEntityQueryView(tableName, dependencyLogicallyDeletableEntityViewSqlTemplate);
        migrationBuilder.DropTreeEntityQueryView(tableName, treeEntityViewSqlTemplate);

        return migrationBuilder;
    }
}

遷移指令碼預覽（節選）

CREATE VIEW QueryView_Tree_Entity2_1s    --建立檢視
AS
WITH [temp]([Id], [DeletedAt], [DependencyDeletedAt], [Entity1_1_1Id], [Entity2Id], [Entity2_0Id], [IsLeaf], [IsRoot], [ParentId], [Text2_1], [Depth], [Path], [HasChildren]) AS
(
    --初始查詢（這裡的 [ParentId] IS NULL 在資料中是最底層的根節點）
    SELECT [Id], [DeletedAt], [DependencyDeletedAt], [Entity1_1_1Id], [Entity2Id], [Entity2_0Id], [IsLeaf], [IsRoot], [ParentId], [Text2_1],
        0 AS [Depth],
        '/' + CAST([Id] AS nvarchar(max)) + '/' AS [Path], --如果Id使用Guid型別，可能會導致層數太深時出問題（大概100層左右，超過4000字之後的字串會被砍掉,sqlserver 2005以後用 nvarchar(max)可以突破限制），Guid的字數太多了
        (CASE WHEN EXISTS(SELECT 1 FROM [QueryView_Entity2_1s] WHERE [QueryView_Entity2_1s].[ParentId] = [Root].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
    FROM [QueryView_Entity2_1s] AS [Root]
    WHERE [Root].[ParentId] IS NULL

    UNION ALL
    --遞迴條件
    SELECT [Child].[Id], [Child].[DeletedAt], [Child].[DependencyDeletedAt], [Child].[Entity1_1_1Id], [Child].[Entity2Id], [Child].[Entity2_0Id], [Child].[IsLeaf], [Child].[IsRoot], [Child].[ParentId], [Child].[Text2_1],
        [Parent].[Depth] + 1,
        [Parent].[Path] + CAST([Child].[Id] AS nvarchar(max)) + '/' AS [Path],
        (CASE WHEN EXISTS(SELECT 1 FROM [QueryView_Entity2_1s] WHERE [QueryView_Entity2_1s].[ParentId] = [Child].[Id]) THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END) AS [HasChildren]
    FROM [QueryView_Entity2_1s] AS [Child] --3：這裡的臨時表和原始資料表都必須使用別名不然遞迴的時候不知道查詢的是哪個表的列
    INNER JOIN [temp] AS [Parent]
    ON ([Child].[ParentId] = [Parent].[Id]) --這個關聯關係很重要，一定要理解一下誰是誰的父節點
)
--4：遞迴完成後 一定不要少了這句查詢語句 否則會報錯
SELECT *
FROM [temp];

MySql 8.0和Sqlite 3支援查詢所需功能，其他資料庫請自行驗證。

Tips

開發測試時發現，如果用命令列工具會導致無法下斷點單步除錯遷移擴充套件，這一度讓筆者很難受。經過一番折騰，發現可以使用以下程式碼在程式中呼叫遷移生成。

var modelInitializer = appDbContext.GetService<IModelRuntimeInitializer>();
var migrationsAssembly = appDbContext.GetService<IMigrationsAssembly>();
var modelDiffer = appDbContext.GetService<IMigrationsModelDiffer>();
var migrator = appDbContext.GetService<IMigrator>();

var firstModel = modelInitializer.Initialize(migrationsAssembly.CreateMigration(migrationsAssembly.Migrations.First().Value, appDbContext.Database.ProviderName!).TargetModel);
var snapshotModel = modelInitializer.Initialize(migrationsAssembly.ModelSnapshot!.Model);

var differences = modelDiffer.GetDifferences(
    migrationsAssembly.ModelSnapshot!.Model.GetRelationalModel(),
    firstModel.GetRelationalModel());

var script = migrator.GenerateScript(migrationsAssembly.Migrations.LastOrDefault().Key, "0"/*, migrationsAssembly.Migrations.FirstOrDefault().Key*/);

結語

經過3篇系列文，一個僅依賴EF Core，對業務程式碼0入侵，完全確保資料完整性的全自動審計、軟刪除和樹形查詢表就大功告成了！

本系列文的所需程式碼從構思到測試基本可用前後過了將近一個月，基本上可以說已經成為了專案這碟醋包了這個系列的一盤餃子了。包括之前的基於EF Core儲存的國際化服務和基於EF Core儲存的Serilog持久化服務其實也是專案的一部分。不過經過這一系列折騰，以後可以直接拿來用了，也不虧。

示例程式碼：SoftDeleteDemo.rar。主頁顯示異常請在libman.json上右鍵恢復前端包。

QQ群

讀者交流QQ群：540719365

歡迎讀者和廣大朋友一起交流，如發現本書錯誤也歡迎透過部落格園、QQ群等方式告知筆者。

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

論如何直接用EF Core實現建立更新時間、使用者審計，自動化樂觀併發、軟刪除和樹形查詢（上）
2024-07-18
論如何直接用EF Core實現建立更新時間、使用者審計，自動化樂觀併發、軟刪除和樹形查詢（中）
2024-07-19
深入理解 EF Core：使用查詢過濾器實現資料軟刪除
2020-07-22
過濾器
如何在EF Core中實現悲觀鎖
2024-04-20
MyBatis自動設定建立時間和更新時間
2024-06-04
MyBatis
EF Core高效查詢
2024-04-03
基於Mybatis-Plus實現自動化操作建立時間和修改時間
2021-02-04
MyBatis
如何實現 Logstash/Elasticsearch 與MySQL自動同步更新操作和刪除操作 ?
2019-02-17
ElasticsearchMySql
實現二叉搜尋樹的新增，查詢和刪除(JAVA)
2020-11-23
Java
如何使用 EF Core 7 批次刪除資料
2022-12-22
sqlalchemy實現時間列自動更新
2019-01-03
SQL
如何實現模糊查詢時間段
2020-12-03
隨機記錄併發查詢與更新(轉移、刪除)的”無恥”優化方法
2017-03-03
隨機優化
python自動化審計及實現
2020-08-19
Python
二叉查詢樹的插入刪除查詢
2019-03-03
Linux下建立和刪除使用者
2016-01-21
Linux
樹形查詢
2009-02-08
mysql 跨表查詢、更新、刪除示例
2021-09-09
MySql
短影片app程式碼，如何實現聯表資料查詢和刪除？
2024-11-02
APP
二叉查詢樹（查詢、插入、刪除）——C語言
2019-08-07
C語言
oracle樹形查詢
2024-09-04
Oracle
AVL樹（查詢、插入、刪除）——C語言
2019-08-13
C語言
二叉排序樹查詢，插入，刪除
2014-04-11
排序
EF Core 高效更新
2024-04-03
ubuntu 建立和刪除使用者
2019-11-25
Ubuntu
Hibernate查詢自動更新
2019-01-19
ElasticSearch分片互動過程(建立索引、刪除索引、查詢索引)
2020-11-14
Elasticsearch索引
怎樣新增、刪除、移動、複製、建立、查詢節點
2024-08-30
【樹狀陣列區間更新區間查詢】code
2017-09-06
陣列
Tree樹狀圖的動態增刪查改（下）修改與刪除
2019-07-16
利用樹形查詢實現部分分析函式功能
2009-08-24
函式
樹形查詢也瘋狂&優化措施
2009-06-19
優化
JavaScript 自動更新時間
2018-09-21
JavaScript
PHP自動化白盒審計技術與實現
2020-08-19
PHP
智慧指標和二叉樹(3):圖解查詢和刪除
2019-05-11
指標二叉樹圖解
自研ORM框架實現類似EF Core Include 拆分查詢支援自定義條件、排序、選擇
2022-12-16
ORM框架排序
Java併發程式設計(05)：悲觀鎖和樂觀鎖機制
2020-06-18
Java程式設計
在蘋果Mac上如何查詢和刪除檔案？
2021-11-21
蘋果Mac