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

發行項
10/14/2024

注意

此功能是在 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 類型比 SqlHierarchyId 更符合 .NET 的規範，它會在 .NET Framework 類型裝載於 SQL Server 資料庫引擎後進行建模。 HierarchyId 是設計來與 EF Core 搭配使用的，但也可在 EF Core 外部的其他應用程式使用。 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 套件不會參照任何其他套件，因此對已部署的應用程式大小和相依性的影響極小。

對查詢和更新等 EF Core 功能使用 HierarchyId，需要 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 套件] 來安裝套件

安裝套件之後，您可以在應用程式呼叫 HierarchyId 的過程中呼叫 UseHierarchyId，藉此使用 UseSqlServer。例如：

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 會針對這些路徑使用精簡的二進位格式，但在處理程式碼時，通常會解析成人類可讀的字串格式。在這種表示方式中，每個層級的位置會以 / 字元分隔。以下圖的族譜為例：

半身人族譜

在此族譜中：

Balbo 位於族譜的根部，以 /表示。
Balbo 有五個孩子，以 /1/、/2/、/3/、/4/和 /5/ 表示。
Balbo 的長子 Mungo 也有五個孩子，以 /1/1/、/1/2/、/1/3/、/1/4/和 /1/5/ 表示。請注意，Mungo 的 HierarchyId (/1/) 是他所有孩子的前置詞。
同樣地，Balbo 的第三子 Ponto 有兩個孩子，以 /3/1/ 和 /3/2/ 表示。 Ponto 的孩子每個都會前置 HierarchyId，表示為 /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/2/2/ 和 /3/3/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」執行此查詢，會傳回「Bungo」、「Belba」、「Longo」、「Linda」、「Bingo」、「Bilbo」、「Otho」、「Falco」、「Lotho」和「Poppy」。

尋找共有的上階

關於這個特定族譜的最常見問題之一是，「誰是 Frodo 和 Bilbo 的共同祖先？」我們可用 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 (也就是「LongoGate」) 事件：DNA 測試顯示 Longo 其實不是 Mungo 的兒子，而是 Ponto 的兒子！這起事件的一個後果是族譜需要重寫。尤其是 Longo 和他的所有後裔都需要重新建立父系，從 Mungo 改為 Ponto。此動作可使用 GetReparentedValue。舉例來說，系統會查詢第一個「Longo」及其所有後裔：

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

GetReparentedValue 會用來更新 Longo 和每個後裔的 HierarchyId，接著呼叫 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.GetLevel()	@hierarchyId.GetLevel()
hierarchyId.GetReparentedValue(oldRoot, newRoot)	%
HierarchyId.GetRoot()	hierarchyid::GetRoot()
hierarchyId.IsDescendantOf(parent)	@hierarchyId.IsDescendantOf(@parent)
HierarchyId.Parse(input)	hierarchyid::Parse(@input)
hierarchyId.ToString()	@hierarchyId.ToString()

其他資源

SQL Server 的階層式資料

共用方式為

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

在 .NET 和 EF Core 使用 HierarchyId

建模階層

查詢階層

更新階層

函式對應

其他資源

其他資源