SQL Server EF Core プロバイダーでの階層データ

Note

この機能は、EF Core 8.0 で追加されました。

Azure SQL と SQL Server には、 hierarchyidを格納するために使われるという特殊なデータ型があります。この場合、"階層データ" とは、基本的に、各項目が親や子を持つことができるツリー構造を形成するデータのことです。このようなデータの例を次に示します。

組織構造
ファイルシステム
プロジェクト内のタスクのセット
言語の用語の分類
Web ページ間のリンクのグラフ

この場合、データベースは、このデータに対してその階層構造を使ってクエリを実行できます。たとえば、クエリを使って特定の項目の先祖と依存関係を見つけることや、階層の特定の深さにあるすべての項目を見つけることができます。

.NET と EF Core での HierarchyId の使用

最下位レベルでは、 Microsoft.SqlServer.Types NuGet パッケージには、 SqlHierarchyId という型が含まれています。この型は動作する hierarchyid 値をサポートしていますが、LINQ で使用するには少し面倒です。

次のレベルでは、新しい Microsoft.EntityFrameworkCore.SqlServer.Abstractions パッケージが導入されました。これには、エンティティ型での使用を目的とした上位レベルの HierarchyId 型が含まれています。

Tip

.NET Framework 型が SQL Server データベースエンジン内でホストされる方法に基づいてモデル化された HierarchyIdよりも、 SqlHierarchyId 型は .NET の規範に沿っています。 HierarchyId は EF Core で機能するように設計されていますが、EF Core の外部でも、他のアプリケーションで使うことができます。 Microsoft.EntityFrameworkCore.SqlServer.Abstractions パッケージは他のパッケージを参照しないため、展開されたアプリケーションのサイズと依存関係への影響は最小限に抑えられます。

クエリや更新などの EF Core 機能に HierarchyId を使うには Microsoft.EntityFrameworkCore.SqlServer.HierarchyId パッケージが必要です。このパッケージは推移的な依存関係として Microsoft.EntityFrameworkCore.SqlServer.Abstractions と Microsoft.SqlServer.Types を取り込むので、多くの場合、必要なパッケージはこれだけです。

.NET 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; }
}

Tip

ここと次の例のコードは HierarchyIdSample.cs から引用したものです。

Tip

HierarchyId は、必要に応じてキープロパティ型として使う場合に適しています。

この場合、家系図には一族の家長というルートがあります。各ハーフリングは、その PathFromPatriarch プロパティを使い、家長からツリーをたどって見つけることができます。 SQL Server は、これらのパスにコンパクトなバイナリ形式を使用しますが、コードを操作するときに人間が判読できる文字列表現との間で解析するのが一般的です。この表現では、各レベルの位置は / 文字で区切られます。たとえば、次の図の家系図を考えてみましょう。

ハーフリングの家系図

このツリーの内容:

ツリーのルートには Balbo がおり、 / で表されます。
Balbo には 5 人の子供がおり、 /1/、 /2/、 /3/、 /4/、 /5/ で表されます。
Balbo の最初の子供である Mungo にも、 /1/1/、 /1/2/、 /1/3/、 /1/4/、 /1/5/ で表される 5 人の子供がいます。 HierarchyId for Mungo (/1/) は、そのすべての子の接頭辞である点に注意してください。
同様に、Balbo の 3 人目の子供である Ponto には /3/1/ と /3/2/ で表される 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();

Tip

必要に応じて、10 進数値を使って 2 つの既存のノード間に新しいノードを作成できます。たとえば、 /3/2.5/2/ と /3/2/2/ の間に /3/3/2/ を挿入します。

階層のクエリ

HierarchyId は、LINQ クエリで使用できるいくつかのメソッドを公開しています。

Method	Description
`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 は、1 レベル、または指定した数のレベル分、上または下に検索する場合に便利です。一方、 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());

Important

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" が返されます。

共通の祖先を見つける

この特定の家系図についてよく聞かれる質問の 1 つは、"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") のスキャンダルは皆さん覚えていますよね。Longo が実は Mungo の息子ではなく、Ponto の息子であることが DNA 鑑定で明らかになったというものです。このスキャンダルによって、家系図を書き直す必要が生じました。特に、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)

Note

HierarchyId プロパティのパラメーター値は、コンパクトなバイナリ形式でデータベースに送信されます。

更新後、"Mungo" の子孫を照会すると、"Bungo"、"Belba"、"Linda"、"Bingo"、"Bilbo"、"Falco"、"Poppy" が返されます。一方、"Ponto" の子孫を照会すると、"Longo"、"Rosa"、"Polo"、"Otho"、"Posco"、"Prisca"、"Lotho"、"Ponto"、"Porto"、"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::Parse(@input)
hierarchyId.ToString()	@hierarchyId.ToString()

その他のリソース

SQL Server の階層データ

フィードバック

このページはお役に立ちましたか?

Last updated on 2025-01-15