Hierarchische Daten im SQL Server EF Core-Anbieter

Azure SQL und SQL Server verfügen über einen speziellen Datentyp namenshierarchyid, der zum Speichern hierarchischer Daten verwendet wird. In diesem Fall sind „hierarchische Daten“ im Wesentlichen Daten, die eine Struktur bilden, in der jedes Element ein übergeordnetes Element und/oder untergeordnete Elemente aufweisen kann. Beispiele für solche Daten sind:

• Eine Organisationsstruktur
• Ein Dateisystem
• Eine Gruppe von Aufgaben in einem Projekt.
• Eine Taxonomie sprachlicher Termini
• Ein Diagramm der Links zwischen Webseiten

Die Datenbank kann dann Abfragen für diese Daten mithilfe ihrer hierarchischen Struktur ausführen. Beispielsweise kann eine Abfrage über- und untergeordnete Elemente von bestimmten Elementen oder alle Elemente in einer bestimmten Tiefe in der Hierarchie finden.

Verwenden von HierarchyId in .NET und EF Core

Auf der niedrigsten Ebene enthält das Microsoft.SqlServer.Types NuGet-Paket einen Typ namens SqlHierarchyId. Während dieser Typ funktionierende HierarchyId-Werte unterstützt, ist es etwas umständlich, mit ihnen in LINQ zu arbeiten.

Auf der nächsten Ebene wurde ein neues Microsoft.EntityFrameworkCore.SqlServer.Abstractions-Paket eingeführt, das einen HierarchyId-Typ höherer Ebene enthält, der für die Verwendung in Entitätstypen vorgesehen ist.

Für die Verwendung von HierarchyId für EF Core-Funktionen wie Abfragen und Updates ist das Paket Microsoft.EntityFrameworkCore.SqlServer.HierarchyId-Paket erforderlich. Dieses Paket enthält Microsoft.EntityFrameworkCore.SqlServer.Abstractions und Microsoft.SqlServer.Types als transitive Abhängigkeiten und ist daher oft das einzige Paket, das benötigt wird.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Nachdem das Paket installiert wurde, wird die Verwendung von HierarchyId aktiviert, indem UseHierarchyId als Teil des Aufrufs der Anwendung von UseSqlServer aufgerufen wird. Beispiel:

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

Modellieren von Hierarchien

Der HierarchyId-Typ kann für Eigenschaften eines Entitätstyps verwendet werden. Angenommen, wir möchten den väterlichen Stammbaum einiger fiktiver Halblinge modellieren. Im Entitätstyp für Halflingkann eine HierarchyId-Eigenschaft verwendet werden, um jeden Halbling im Stammbaum zu finden.

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

In diesem Fall ist der Stammbaum mit dem Patriarchen der Familie verwurzelt. Jeder Halbling kann vom Patriarchen über den Stammbaum mithilfe seiner PathFromPatriarch-Eigenschaft nachverfolgt werden. SQL Server verwendet für diese Pfade ein kompaktes Binärformat. Es ist jedoch üblich, beim Arbeiten mit Code in und aus einer für Menschen lesbaren Zeichenfolgendarstellung zu analysieren. In dieser Darstellung wird die Position auf jeder Ebene durch ein /-Zeichen getrennt. Betrachten Sie beispielsweise den Stammbaum im folgenden Diagramm:

In dieser Struktur:

• Balbo befindet sich ganz oben in der Struktur, dargestellt durch /.
• Balbo hat fünf untergeordnete Elemente: /1/, /2/, /3/, /4/und /5/.
• Mungo, Balbos erstes untergeordnetes Element, hat auch fünf untergeordnete Elemente: /1/1/, /1/2/, /1/3/, /1/4/und /1/5/. Beachten Sie, dass das HierarchyId für Mungo (/1/) das Präfix für alle untergeordneten Elemente ist.
• Ebenso hat Ponto, Balbos drittes untergeordnetes Element, zwei untergeordnete Elemente: /3/1/ und /3/2/. Auch hier wird jedem dieser untergeordneten Elemente die HierarchyId für Ponto vorangestellt, dargestellt als /3/.
• Und immer so weiter in der Struktur...

Der folgende Code fügt diesen Stammbaum mithilfe von EF Core in eine Datenbank ein:

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();

Abfragen von Hierarchien

HierarchyId stellt mehrere Methoden zur Verfügung, die in LINQ-Abfragen verwendet werden können.

Darüber hinaus können die Operatoren ==, !=, <, <=, > und >= verwendet werden.

Im Folgenden sind Beispiele für die Verwendung dieser Methoden in LINQ-Abfragen aufgeführt.

Abrufen von Entitäten auf einer bestimmten Ebene in der Struktur

Die folgende Abfrage verwendet GetLevel, um alle Halblinge auf einer bestimmten Ebene in der Struktur zurückzugeben:

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

Dies entspricht der folgenden SQL-Syntax:

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

Wenn Sie dies in einer Schleife ausführen, können Sie die Halblinge für jede Generation abrufen:

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

Abrufen des direkten Vorgängers einer Entität

Die folgende Abfrage verwendet GetAncestor, um den direkten Vorgänger eines Halblings unter Berücksichtigung des Namens dieses Halblings zu finden:

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

Dies entspricht der folgenden SQL-Syntax:

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)

Die Ausführung dieser Abfrage für den Halbling „Bilbo“ gibt „Bungo“ zurück.

Abrufen der direkten Nachfolger einer Entität

Die folgende Abfrage verwendet auch GetAncestor, aber dieses Mal, um die direkten Nachfolger eines Halblings zu finden, wenn der Name dieses Halblings gegeben ist:

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

Dies entspricht der folgenden SQL-Syntax:

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)

Die Ausführung dieser Abfrage für den Halbling „Mungo“ gibt „Bungo“, „Belba“, „Longo“ und „Linda“ zurück.

Abrufen aller Vorgänger einer Entität

GetAncestor ist nützlich, um eine einzelne Ebene oder eine bestimmte Anzahl von Ebenen nach oben oder unten zu durchsuchen. Auf der anderen Seite ist IsDescendantOf nützlich, um alle Vorfahren oder Nachfolger zu finden. Die folgende Abfrage verwendet zum Beispiel IsDescendantOf, um alle Vorfahren eines Halblings zu finden, wobei der Name des Halblings angegeben wird:

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());

Dies entspricht der folgenden SQL-Syntax:

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

Die Ausführung dieser Abfrage für den Halbling „Bilbo“ gibt „Bungo“, „Mungo“ und „Balbo“ zurück.

Abrufen aller Nachfolger einer Entität

Die folgende Abfrage verwendet auch IsDescendantOf, aber dieses Mal, um alle Nachfolger eines Halblings zu finden, wobei der Name des Halblings angegeben wird:

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());

Dies entspricht der folgenden SQL-Syntax:

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()

Die Ausführung dieser Abfrage für den Halbling „Mungo“ gibt „Bungo“, „Belba“, „Longo“, „Linda“, „Bingo“, „Bilbo“, „Otho“, „Falco“, „Lotho“ und „Poppy“ zurück.

Suchen nach einem gemeinsamen Vorfahren

Eine der häufigsten Fragen zu diesem speziellen Stammbaum ist: „Wer ist der gemeinsame Vorfahre von Frodo und Bilbo?“ Wir können IsDescendantOf verwenden, um eine solche Abfrage zu erstellen:

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();

Dies entspricht der folgenden SQL-Syntax:

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

Die Ausführung dieser Abfrage mit „Bilbo“ und „Frodo“ teilt uns mit, dass deren gemeinsamer Vorfahre „Balbo“ ist.

Aktualisieren von Hierarchien

Die normalen Änderungsnachverfolgungs- und SaveChanges-Mechanismen können verwendet werden, um hierarchyid-Spalten zu aktualisieren.

Neueinordnung einer Unterhierarchie

Zum Beispiel erinnern wir uns alle an den Skandal von SR 1752 (auch als „LongoGate“ bekannt), als DNA-Tests ergaben, dass Longo nicht der Sohn von Mungo, sondern tatsächlich der Sohn von Ponto ist! Eine Folge dieses Skandals war, dass der Stammbaum neu geschrieben werden musste. Insbesondere mussten Longo und alle seine Nachkommen von Mungo nach Ponto neu eingeordnet werden. GetReparentedValue kann dazu verwendet werden. Beispielsweise werden zuerst „Longo“ und alle seine Nachfolger abgefragt:

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

Anschließend wird GetReparentedValue verwendet, um die HierarchyId für Longo und jeden Nachfolger zu aktualisieren, gefolgt von einem Aufruf von SaveChangesAsync:

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

await context.SaveChangesAsync();

Dies führt zu der folgenden Aktualisierung der Datenbank:

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;

Unter Verwendung dieser Parameter:

 @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)

Nach dem Update gibt die Abfrage nach den Nachfolgern von „Mungo“ die Namen „Bungo“, „Belba“, „Linda“, „Bingo“, „Bilbo“, „Falco“ und „Poppy“ zurück, während die Abfrage nach den Nachfolgern von "Ponto" die Namen „Longo“, „Rosa“, „Polo“, „Otho“, „Posco“, „Prisca“, „Lotho“, „Ponto“, „Porto“, „Peony“ und „Angelica“ zurückgibt.

Funktionszuordnungen

Zusätzliche Ressourcen

• Hierarchische Daten in SQL Server