SQL Server EF Core 提供者中的階層式資料

發行項
08/25/2023

注意

這項功能已在 EF Core 8.0 中新增。

Azure SQL 和 SQL Server 具有稱為 hierarchyid 的特殊資料類型，可用來儲存階層式資料。在此情況下，「階層式資料」基本上表示形成樹狀結構的資料，其中每個專案都可以有父系和/或子系。這類資料的範例包括：

組織結構
檔案系統
專案中的一組工作
語言詞彙的分類表
網頁之間的連結圖形

資料庫接著可以使用其階層式結構，針對此資料執行查詢。例如，查詢可以尋找給定專案的上階和相依專案，或尋找階層中特定深度的所有專案。

在 .NET 和 EF Core 中使用 HierarchyId

在最低層級， Microsoft.SqlServer.Types NuGet 套件包含稱為 SqlHierarchyId 的類型。雖然此類型支援使用 hierarchyid 值，但在 LINQ 中使用會有點麻煩。

在下一個層級中，已引進新的 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 套件，其中包含用於實體類型的高階 HierarchyId 類型。

提示

此 HierarchyId 類型比 .NET 的規範更慣用，而是在 SQL Server 資料庫引擎內裝載 .NET SqlHierarchyId Framework 類型的方式之後進行模型化。 HierarchyId 其設計目的是要與 EF Core 搭配使用，但也可用於其他應用程式中的 EF Core 外部。套件 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 不會參考任何其他套件，因此對已部署的應用程式大小和相依性的影響最小。

HierarchyId針對 EF Core 功能使用，例如查詢和更新需要 Microsoft.EntityFrameworkCore.SqlServer.HierarchyId 套件。此套件引進 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 和 Microsoft.SqlServer.Types 作為可轉移的相依性，因此通常是唯一所需的套件。

.NET Core CLI
Visual Studio

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

工具 > NuGet 封裝管理員封裝管理員 > 主控台

執行下列命令：

Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

提示：您也可以以滑鼠右鍵按一下專案並選取 [管理 NuGet 套件] 來安裝套件

安裝套件之後，藉由呼叫 UseHierarchyId 作為應用程式呼叫 UseSqlServer 的一部分，啟用的用法 HierarchyId 。例如：

options.UseSqlServer(
    connectionString,
    x => x.UseHierarchyId());

模型化階層

此 HierarchyId 類型可用於實體類型的屬性。例如，假設我們想要模型化一些虛構半身子的父系家族樹。在的 Halfling 實體類型中， HierarchyId 屬性可用來找出家族樹狀結構中的每個半形。

public class Halfling
{
    public Halfling(HierarchyId pathFromPatriarch, string name, int? yearOfBirth = null)
    {
        PathFromPatriarch = pathFromPatriarch;
        Name = name;
        YearOfBirth = yearOfBirth;
    }

    public int Id { get; private set; }
    public HierarchyId PathFromPatriarch { get; set; }
    public string Name { get; set; }
    public int? YearOfBirth { get; set; }
}

提示

這裡和下列範例中顯示的程式碼來自 HierarchyIdSample.cs 。

提示

如有需要， HierarchyId 適合做為索引鍵屬性類型使用。

在此情況下，家族樹是以家族的父權階層為根。每半項都可以使用其 PathFromPatriarch 屬性從樹狀結構中的父階層追蹤。 SQL Server 會針對這些路徑使用精簡的二進位格式，但在使用程式碼時，通常會剖析人類可讀取的字串標記法。在此標記法中，每個層級的位置會以 / 字元分隔。例如，請考慮下圖中的家族樹狀結構：

Halfling family tree

在此樹狀結構中：

Balbo 位於樹狀結構的根目錄，以 / 表示。
Balbo 有五個子系，由 /1/ 、 /2/ 、 /3/ 、 /4/ 和 /5/ 表示。
巴爾博的第一個孩子蒙戈也有五個孩子，由 /1/1/ 、 /1/2/ 、 /1/3/ 、 /1/4/ 和 /1/5/ 代表。請注意， HierarchyId 巴爾博（ /1/ ）的是他所有孩子的前置詞。
同樣地，巴爾博的第三個孩子龐托有兩個孩子，由 /3/1/ 和 /3/2/ 代表。同樣地，這些子系前面會加上 HierarchyId Ponto 的，其表示為 /3/ 。
樹下等等...

下列程式碼會使用 EF Core 將此家族樹插入資料庫中：

await AddRangeAsync(
    new Halfling(HierarchyId.Parse("/"), "Balbo", 1167),
    new Halfling(HierarchyId.Parse("/1/"), "Mungo", 1207),
    new Halfling(HierarchyId.Parse("/2/"), "Pansy", 1212),
    new Halfling(HierarchyId.Parse("/3/"), "Ponto", 1216),
    new Halfling(HierarchyId.Parse("/4/"), "Largo", 1220),
    new Halfling(HierarchyId.Parse("/5/"), "Lily", 1222),
    new Halfling(HierarchyId.Parse("/1/1/"), "Bungo", 1246),
    new Halfling(HierarchyId.Parse("/1/2/"), "Belba", 1256),
    new Halfling(HierarchyId.Parse("/1/3/"), "Longo", 1260),
    new Halfling(HierarchyId.Parse("/1/4/"), "Linda", 1262),
    new Halfling(HierarchyId.Parse("/1/5/"), "Bingo", 1264),
    new Halfling(HierarchyId.Parse("/3/1/"), "Rosa", 1256),
    new Halfling(HierarchyId.Parse("/3/2/"), "Polo"),
    new Halfling(HierarchyId.Parse("/4/1/"), "Fosco", 1264),
    new Halfling(HierarchyId.Parse("/1/1/1/"), "Bilbo", 1290),
    new Halfling(HierarchyId.Parse("/1/3/1/"), "Otho", 1310),
    new Halfling(HierarchyId.Parse("/1/5/1/"), "Falco", 1303),
    new Halfling(HierarchyId.Parse("/3/2/1/"), "Posco", 1302),
    new Halfling(HierarchyId.Parse("/3/2/2/"), "Prisca", 1306),
    new Halfling(HierarchyId.Parse("/4/1/1/"), "Dora", 1302),
    new Halfling(HierarchyId.Parse("/4/1/2/"), "Drogo", 1308),
    new Halfling(HierarchyId.Parse("/4/1/3/"), "Dudo", 1311),
    new Halfling(HierarchyId.Parse("/1/3/1/1/"), "Lotho", 1310),
    new Halfling(HierarchyId.Parse("/1/5/1/1/"), "Poppy", 1344),
    new Halfling(HierarchyId.Parse("/3/2/1/1/"), "Ponto", 1346),
    new Halfling(HierarchyId.Parse("/3/2/1/2/"), "Porto", 1348),
    new Halfling(HierarchyId.Parse("/3/2/1/3/"), "Peony", 1350),
    new Halfling(HierarchyId.Parse("/4/1/2/1/"), "Frodo", 1368),
    new Halfling(HierarchyId.Parse("/4/1/3/1/"), "Daisy", 1350),
    new Halfling(HierarchyId.Parse("/3/2/1/1/1/"), "Angelica", 1381));

await SaveChangesAsync();

提示

如有需要，十進位值可用來在兩個現有節點之間建立新的節點。例如， /3/2.5/2/ 介於和 /3/3/2/ 之間 /3/2/2/ 。

查詢階層

HierarchyId 會公開數個可用於 LINQ 查詢的方法。

方法	描述
`GetAncestor(int n)`	取得階層式樹狀結構的節點 `n` 層級。
`GetDescendant(HierarchyId? child1, HierarchyId? child2)`	取得大於 `child1` 和小於 `child2` 的子代節點值。
`GetLevel()`	取得階層式樹狀結構中這個節點的層級。
`GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)`	取得值，表示新節點的位置，這個節點的路徑從 `newRoot` 等於 `oldRoot` 路徑到這個，有效地將這個移至新位置。
`IsDescendantOf(HierarchyId? parent)`	取得值，指出這個節點是否為的 `parent` 子系。

此外，可以使用運算子 == 、 != 、 < 、 <=> 和 >= 。

以下是在 LINQ 查詢中使用這些方法的範例。

取得樹狀結構中指定層級的實體

下列查詢會使用 GetLevel 傳回家族樹中指定層級的所有半項：

var generation = await context.Halflings.Where(halfling => halfling.PathFromPatriarch.GetLevel() == level).ToListAsync();

這會轉譯為下列 SQL：

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].GetLevel() = @__level_0

在迴圈中執行此作業，我們可以取得每一代的半形：

Generation 0: Balbo
Generation 1: Mungo, Pansy, Ponto, Largo, Lily
Generation 2: Bungo, Belba, Longo, Linda, Bingo, Rosa, Polo, Fosco
Generation 3: Bilbo, Otho, Falco, Posco, Prisca, Dora, Drogo, Dudo
Generation 4: Lotho, Poppy, Ponto, Porto, Peony, Frodo, Daisy
Generation 5: Angelica

取得實體的直接上階

下列查詢會使用 GetAncestor 來尋找半身分的直接上階，因為有一半的名稱：

async Task<Halfling?> FindDirectAncestor(string name)
    => await context.Halflings
        .SingleOrDefaultAsync(
            ancestor => ancestor.PathFromPatriarch == context.Halflings
                .Single(descendent => descendent.Name == name).PathFromPatriarch
                .GetAncestor(1));

這會轉譯為下列 SQL：

SELECT TOP(2) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch] = (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0).GetAncestor(1)

執行此查詢以取得半形「Bilbo」會傳回「Bungo」。

取得實體的直接子系

下列查詢也會使用 GetAncestor ，但這次會尋找半元的直接子系，因為該半個子系的名稱如下：

IQueryable<Halfling> FindDirectDescendents(string name)
    => context.Halflings.Where(
        descendent => descendent.PathFromPatriarch.GetAncestor(1) == context.Halflings
            .Single(ancestor => ancestor.Name == name).PathFromPatriarch);

這會轉譯為下列 SQL：

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].GetAncestor(1) = (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0)

執行此查詢以取得半形「Mungo」會傳回「Bungo」、「Belba」、「Longo」和「Linda」。

取得實體的所有上階

GetAncestor 對於搜尋單一層級或向下搜尋，或確實是指定的層級數目很有用。另一方面， IsDescendantOf 對於尋找所有上階或相依專案很有用。例如，下列查詢會使用 IsDescendantOf 來尋找半階的所有上階，因為該半元的名稱如下：

IQueryable<Halfling> FindAllAncestors(string name)
    => context.Halflings.Where(
            ancestor => context.Halflings
                .Single(
                    descendent =>
                        descendent.Name == name
                        && ancestor.Id != descendent.Id)
                .PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))
        .OrderByDescending(ancestor => ancestor.PathFromPatriarch.GetLevel());

重要

IsDescendantOf 會針對本身傳回 true，這就是為什麼在上述查詢中篩選掉的原因。

這會轉譯為下列 SQL：

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0 AND [h].[Id] <> [h0].[Id]).IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel() DESC

執行此查詢來尋找半形「Bilbo」會傳回「Bungo」、「Mungo」和「Balbo」。

取得實體的所有子系

下列查詢也會使用，但這次會使用 IsDescendantOf 半元的所有子系，因為有一半的名稱：

IQueryable<Halfling> FindAllDescendents(string name)
    => context.Halflings.Where(
            descendent => descendent.PathFromPatriarch.IsDescendantOf(
                context.Halflings
                    .Single(
                        ancestor =>
                            ancestor.Name == name
                            && descendent.Id != ancestor.Id)
                    .PathFromPatriarch))
        .OrderBy(descendent => descendent.PathFromPatriarch.GetLevel());

這會轉譯為下列 SQL：

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].IsDescendantOf((
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0 AND [h].[Id] <> [h0].[Id])) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel()

執行此查詢時，Mungo 的半形「Mungo」會傳回「Bungo」、「Belba」、「Longo」、「Linda」、「Bingo」、「Bilbo」、「Otho」、「Falco」、「Lotho」和「Popy」。

尋找常見的上階

關於這個特定家族樹的最常見問題之一是，「誰是弗羅多和比爾博的共同祖先？我們可用來 IsDescendantOf 撰寫這類查詢：

async Task<Halfling?> FindCommonAncestor(Halfling first, Halfling second)
    => await context.Halflings
        .Where(
            ancestor => first.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch)
                        && second.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))
        .OrderByDescending(ancestor => ancestor.PathFromPatriarch.GetLevel())
        .FirstOrDefaultAsync();

這會轉譯為下列 SQL：

SELECT TOP(1) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE @__first_PathFromPatriarch_0.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
  AND @__second_PathFromPatriarch_1.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel() DESC

使用「Bilbo」和「Frodo」執行此查詢會告訴我們，其共同祖先是「Balbo」。

更新階層

一般變更追蹤和 SaveChanges 機制可用來更新資料 hierarchyid 行。

重新上層子階層

例如，我相信我們都記得SR 1752（也就是「朗哥門」）的醜聞，當DNA測試顯示朗戈不是蒙戈的兒子，但實際上龐托的兒子！這起醜聞的一個後因是家庭樹需要重新寫。特別是，朗戈和他的所有後裔都需要從蒙戈重新養育到龐托。 GetReparentedValue 可以用來執行這項操作。例如，會查詢第一個「Longo」及其所有子系：

var longoAndDescendents = await context.Halflings.Where(
        descendent => descendent.PathFromPatriarch.IsDescendantOf(
            context.Halflings.Single(ancestor => ancestor.Name == "Longo").PathFromPatriarch))
    .ToListAsync();

接著 GetReparentedValue 會用來更新 HierarchyId Longo 和每個子系的，後面接著呼叫 SaveChangesAsync ：

foreach (var descendent in longoAndDescendents)
{
    descendent.PathFromPatriarch
        = descendent.PathFromPatriarch.GetReparentedValue(
            mungo.PathFromPatriarch, ponto.PathFromPatriarch)!;
}

await context.SaveChangesAsync();

這會導致下列資料庫更新：

SET NOCOUNT ON;
UPDATE [Halflings] SET [PathFromPatriarch] = @p0
OUTPUT 1
WHERE [Id] = @p1;
UPDATE [Halflings] SET [PathFromPatriarch] = @p2
OUTPUT 1
WHERE [Id] = @p3;
UPDATE [Halflings] SET [PathFromPatriarch] = @p4
OUTPUT 1
WHERE [Id] = @p5;

使用這些參數：

 @p1='9',
 @p0='0x7BC0' (Nullable = false) (Size = 2) (DbType = Object),
 @p3='16',
 @p2='0x7BD6' (Nullable = false) (Size = 2) (DbType = Object),
 @p5='23',
 @p4='0x7BD6B0' (Nullable = false) (Size = 3) (DbType = Object)

注意

屬性的參數 HierarchyId 值會以精簡的二進位格式傳送至資料庫。

更新之後，查詢「Mungo」的子系會傳回「Bungo」，「Belba」、「Linda」、「Bingo」、「Bilbo」、「Falco」和「Popy」，同時查詢「Ponto」的子系會傳回「Longo」、「Rosa」、「Polo」、「Otho」、「Posco」、「Prisca」、「Lotho」、「Ponto」、「Port」、「Peony」和「Angelica」。

函式對應

.NET	SQL
hierarchyId.GetAncestor（n）	@hierarchyId.GetAncestor(@n)
hierarchyId.GetDescendant（child）	@hierarchyId.GetDescendant(@child、Null）
hierarchyId.GetDescendant（child1， child2）	@hierarchyId.GetDescendant(@child1, @child2)
hierarchyId.GetLevel（）	@hierarchyId.GetLevel()
hierarchyId.GetReparentedValue（oldRoot，newRoot）	@hierarchyId.GetReparentedValue(@oldRoot, @newRoot)
HierarchyId.GetRoot（）	hierarchyid：：GetRoot（）
hierarchyId.IsDescendantOf（parent）	@hierarchyId.IsDescendantOf(@parent)
HierarchyId.Parse（input）	hierarchyid：:P arse（@input）
hierarchyId.ToString（）	@hierarchyId.ToString()

其他資源

SQL Server 中的階層式資料

共用方式為

SQL Server EF Core 提供者中的階層式資料

在 .NET 和 EF Core 中使用 HierarchyId

模型化階層

查詢階層

更新階層

函式對應

其他資源

意見反應

其他資源