C# 實現無限層級分類：遞迴查詢 + CTE 優化 + 快取策略完整方案

資料模型

CREATE TABLE Category (
    Id          INT IDENTITY PRIMARY KEY,
    Name        NVARCHAR(100) NOT NULL,
    ParentId    INT NULL REFERENCES Category(Id), -- 自關聯
    Level       INT NOT NULL DEFAULT 0,           // 冗餘欄位：根=0，子=父+1
    SortOrder   INT NOT NULL DEFAULT 0,
    Description NVARCHAR(500),
    CreatedAt   DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

CREATE INDEX IX_Category_ParentId ON Category(ParentId);
CREATE INDEX IX_Category_Level ON Category(Level);

實體類別

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; }
    public int Level { get; set; }
    public int SortOrder { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }

    // 導航屬性 (EF Core / Dapper 手動填充)
    public List Children { get; set; } = new();
}

方案一：遞迴 CTE (Common Table Expression) — 單次查詢取全樹 (推薦)

SQL Server / PostgreSQL / SQLite / Oracle 11g+ 支援。
**優點**：單次往返、資料庫端完成遞迴、效能最佳。
**缺點**：MySQL 8.0 前不支援；極深層級 (＞1000) 可能觸及 MAXRECURSION 限制。

public class CategoryRepository
{
    private readonly string _connStr;

    public CategoryRepository(string connStr) => _connStr = connStr;

    /// 

    /// 取得完整分類樹 (根節點 Level=0)
    /// 
    public List GetFullTree()
    {
        const string sql = @"
WITH CategoryTree AS (
    -- Anchor: 根節點
    SELECT Id, Name, ParentId, Level, SortOrder, Description, CreatedAt,
           CAST(Name AS NVARCHAR(MAX)) AS Path -- 可選：完整路徑
    FROM Category
    WHERE ParentId IS NULL

    UNION ALL

    -- Recursive: 子節點
    SELECT c.Id, c.Name, c.ParentId, c.Level, c.SortOrder, c.Description, c.CreatedAt,
           CAST(ct.Path + ' > ' + c.Name AS NVARCHAR(MAX))
    FROM Category c
    INNER JOIN CategoryTree ct ON c.ParentId = ct.Id
)
SELECT Id, Name, ParentId, Level, SortOrder, Description, CreatedAt
FROM CategoryTree
ORDER BY Path; -- 依路徑排序，保證父先於子";

        var dict = new Dictionary();
        var roots = new List();

        using (var conn = new SqlConnection(_connStr))
        using (var cmd = new SqlCommand(sql, conn))
        {
            conn.Open();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var cat = new Category
                    {
                        Id = reader.GetInt32(0),
                        Name = reader.GetString(1),
                        ParentId = reader.IsDBNull(2) ? null : reader.GetInt32(2),
                        Level = reader.GetInt32(3),
                        SortOrder = reader.GetInt32(4),
                        Description = reader.IsDBNull(5) ? "" : reader.GetString(5),
                        CreatedAt = reader.GetDateTime(6)
                    };
                    dict[cat.Id] = cat;

                    if (cat.ParentId == null)
                        roots.Add(cat);
                    else if (dict.TryGetValue(cat.ParentId.Value, out var parent))
                        parent.Children.Add(cat);
                }
            }
        }
        return roots;
    }
}

方案二：遞迴載入 (N+1 查詢) — 簡單但效能差，僅適合極小資料量

// 原文模式改良：單次查詢所有節點，記憶體中組樹 (避免 N+1)
public List GetTreeInMemory()
{
    const string sql = "SELECT Id, Name, ParentId, Level, SortOrder, Description, CreatedAt FROM Category ORDER BY Level, SortOrder";

    var all = new List();
    using (var conn = new SqlConnection(_connStr))
    using (var cmd = new SqlCommand(sql, conn))
    {
        conn.Open();
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                all.Add(new Category { /* 映射欄位 */ });
            }
        }
    }

    // 記憶體組樹 O(N)
    var lookup = all.ToDictionary(c => c.Id);
    var roots = new List();

    foreach (var cat in all)
    {
        if (cat.ParentId == null)
            roots.Add(cat);
        else if (lookup.TryGetValue(cat.ParentId.Value, out var parent))
            parent.Children.Add(cat);
    }
    return roots;
}

方案三：Closure Table (關係閉包表) — 適合高頻「查詢所有子孫/祖先」

CREATE TABLE CategoryClosure (
    AncestorId   INT NOT NULL REFERENCES Category(Id),
    DescendantId INT NOT NULL REFERENCES Category(Id),
    Depth        INT NOT NULL, -- 0=自己, 1=直接子, 2=孫...
    PRIMARY KEY (AncestorId, DescendantId)
);

-- 觸發器/應用層維護：新增節點時插入 (自己,自己,0) + (父祖先, 新節點, 父深度+1)
-- 查詢節點 5 的所有子孫：
SELECT c.* FROM Category c
JOIN CategoryClosure cc ON c.Id = cc.DescendantId
WHERE cc.AncestorId = 5 AND cc.Depth > 0;

快取策略：分類樹讀多寫少，極適合快取

public class CachedCategoryService
{
    private readonly CategoryRepository _repo;
    private readonly IMemoryCache _cache;
    private const string CacheKey = "CategoryTree";
    private static readonly TimeSpan CacheExpiry = TimeSpan.FromHours(1);

    public CachedCategoryService(CategoryRepository repo, IMemoryCache cache)
    {
        _repo = repo;
        _cache = cache;
    }

    public List GetTree()
    {
        return _cache.GetOrCreate(CacheKey, entry =>
        {
            entry.AbsoluteExpirationRelativeToNow = CacheExpiry;
            entry.SetPriority(CacheItemPriority.NeverRemove); // 重要資料
            return _repo.GetFullTree();
        });
    }

    // 寫入操作後主動失效
    public void InvalidateCache() => _cache.Remove(CacheKey);

    public void AddCategory(Category cat) { /* DB Insert */ InvalidateCache(); }
    public void UpdateCategory(Category cat) { /* DB Update */ InvalidateCache(); }
    public void DeleteCategory(int id) { /* DB Delete (需處理子節點) */ InvalidateCache(); }
}

Web API 回傳 JSON (遞迴序列化)

[HttpGet("tree")]
public ActionResult> GetTree()
{
    var tree = _service.GetTree();
    return Ok(tree.Select(MapToDto).ToList());
}

private CategoryDto MapToDto(Category c) => new CategoryDto
{
    Id = c.Id,
    Name = c.Name,
    Level = c.Level,
    Children = c.Children.Select(MapToDto).ToList() // 遞迴投影
};

public class CategoryDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Level { get; set; }
    public List Children { get; set; } = new();
}

效能對比總結

最佳實踐建議

1. **首選 CTE** (支援資料庫) + 記憶體組樹 → 簡潔、高效。
2. **冗餘 Level、Path (Materialized Path)** 欄位 → 加速排序、篩選、顯示層級縮排。
3. **快取整棵樹** (記憶體/Redis) + 寫入失效 → 讀取近乎零延遲。
4. **軟刪除** (IsDeleted) 而非物理刪除，避免破壞子樹結構。
5. **驗證循環引用** (新增/修改 ParentId 時檢測是否會形成環)。
6. 大型系統考慮 **Closure Table** 或圖資料庫 (Neo4j) 支援複雜關係查詢。

分類樹是經典遞迴結構，掌握 CTE、Closure Table、快取三板斧，能從容應對從 CMS 選單到電商類目、組織架構的各種場景。