Delen via

Hiërarchische gegevens in de SQL Server EF Core-provider

  • Artikel

Notitie

Deze functie is toegevoegd in EF Core 8.0.

Azure SQL en SQL Server hebben een speciaal gegevenstype met de naam hierarchyid dat wordt gebruikt voor het opslaan van hiërarchische gegevens. In dit geval betekent 'hiërarchische gegevens' in feite gegevens die een boomstructuur vormen, waarbij elk item een ouder en/of kinderen kan hebben. Voorbeelden van dergelijke gegevens zijn:

  • Een organisatiestructuur
  • Een bestandssysteem
  • Een set taken in een project
  • Een taxonomie van taaltermen
  • Een grafiek met koppelingen tussen webpagina's

De database kan vervolgens query's uitvoeren op deze gegevens met behulp van de hiërarchische structuur. Een query kan bijvoorbeeld voorouders en afhankelijke items van opgegeven items vinden, of alle items op een bepaald niveau in de hiërarchie vinden.

HierarchyId gebruiken in .NET en EF Core

Op het laagste niveau bevat het Microsoft.SqlServer.Types NuGet-pakket een type met de naam SqlHierarchyId. Hoewel dit type ondersteuning biedt voor werkende hiërarchie-id-waarden, is het een beetje lastig om mee te werken in LINQ.

Op het volgende niveau is een nieuw Microsoft.EntityFrameworkCore.SqlServer.Abstractions-pakket geïntroduceerd. Dit pakket bevat een HierarchyId type dat is bedoeld voor gebruik in entiteitstypen.

Tip

Het HierarchyId type is meer idiomatisch voor de normen van .NET dan SqlHierarchyId, die wordt gemodelleerd naar de manier waarop .NET Framework-typen worden gehost in de SQL Server-database-engine. HierarchyId is ontworpen voor gebruik met EF Core, maar kan ook buiten EF Core in andere toepassingen worden gebruikt. Het Microsoft.EntityFrameworkCore.SqlServer.Abstractions-pakket verwijst niet naar andere pakketten en heeft dus minimale invloed op de geïmplementeerde toepassingsgrootte en afhankelijkheden.

Voor het gebruik van HierarchyId voor EF Core-functionaliteit, zoals query's en updates, is het microsoft.EntityFrameworkCore.SqlServer.HierarchyId-pakket vereist. Dit pakket brengt Microsoft.EntityFrameworkCore.SqlServer.Abstractions en Microsoft.SqlServer.Types als transitieve afhankelijkheden, en is dus vaak het enige pakket dat nodig is.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Zodra het pakket is geïnstalleerd, wordt het gebruik van HierarchyId ingeschakeld door UseHierarchyId aan te roepen als onderdeel van de aanroep van de toepassing naar UseSqlServer. Bijvoorbeeld:

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

Hiërarchieën modelleren

Het HierarchyId type kan worden gebruikt voor eigenschappen van een entiteitstype. Stel dat we de vaderlijke familiestructuur van een fictief halflingswillen modelleren. In het entiteitstype voor Halflingkan een HierarchyId eigenschap worden gebruikt om elke halfling in de stamboom te vinden.

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

De code die hier en in de onderstaande voorbeelden wordt weergegeven, is afkomstig van HierarchyIdSample.cs.

Advies

Indien gewenst is HierarchyId geschikt voor gebruik als sleuteleigenschapstype.

In dit geval is de stamboom geworteld met de patriarch van de familie. Elke halveling kan worden getraceerd vanuit de patriarch langs de afstammingslijn door middel van zijn PathFromPatriarch-eigenschap. SQL Server maakt gebruik van een compacte binaire indeling voor deze paden, maar het is gebruikelijk om te parseren naar en van een door mensen leesbare tekenreeksweergave wanneer u met code werkt. In deze weergave wordt de positie op elk niveau gescheiden door een / teken. Denk bijvoorbeeld aan de familiestructuur in het onderstaande diagram:

Halfling stamboom

In deze boom:

  • Balbo bevindt zich in de wortel van de boom, vertegenwoordigd door /.
  • Balbo heeft vijf kinderen, vertegenwoordigd door /1/, /2/, /3/, /4/en /5/.
  • Balbo's eerste kind, Mungo, heeft ook vijf kinderen, vertegenwoordigd door /1/1/, /1/2/, /1/3/, /1/4/en /1/5/. U ziet dat de HierarchyId voor Mungo (/1/) het voorvoegsel is voor al zijn kinderen.
  • Op dezelfde manier heeft Balbo's derde kind, Ponto, twee kinderen, vertegenwoordigd door /3/1/ en /3/2/. Opnieuw wordt elk van deze kinderen voorafgegaan door de HierarchyId voor Ponto, die wordt weergegeven als /3/.
  • En zo verder naar beneden in de boom...

Met de volgende code wordt deze familiestructuur in een database ingevoegd met behulp van 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

Indien nodig kunnen decimale waarden worden gebruikt om nieuwe knooppunten tussen twee bestaande knooppunten te maken. /3/2.5/2/ gaat bijvoorbeeld tussen /3/2/2/ en /3/3/2/.

Queries uitvoeren op hiërarchieën

HierarchyId verschillende methoden beschikbaar maakt die kunnen worden gebruikt in LINQ-query's.

Methode Beschrijving
GetAncestor(int n) Hiermee haalt u het knooppunt op n niveaus omhoog in de hiërarchische structuur.
GetDescendant(HierarchyId? child1, HierarchyId? child2) Hiermee wordt de waarde opgehaald van een afstammelingsknooppunt dat groter is dan child1 en kleiner is dan child2.
GetLevel() Hiermee wordt het niveau van dit knooppunt in de hiërarchische structuur bepaald.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) Hiermee wordt een waarde opgehaald die de locatie vertegenwoordigt van een nieuw knooppunt, waarbij een pad van newRoot is gelijk aan het pad van oldRoot naar deze locatie, waardoor deze effectief naar de nieuwe locatie wordt verplaatst.
IsDescendantOf(HierarchyId? parent) Hiermee wordt een waarde opgehaald die aangeeft of dit knooppunt een afstammeling is van parent.

Bovendien kunnen de operators ==, !=, <, <=, > en >= worden gebruikt.

Hier volgen enkele voorbeelden van het gebruik van deze methoden in LINQ-query's.

Entiteiten ophalen op een bepaald niveau in de boom

In de volgende query wordt GetLevel gebruikt om alle halflingen op een bepaald niveau in de stamboom te retourneren.

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

Dit wordt omgezet in de volgende SQL:

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

Door dit in een lus uit te voeren, kunnen we de halflings voor elke generatie verkrijgen:

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

Verkrijg de directe voorouder van een entiteit

In de volgende query wordt GetAncestor gebruikt om de directe voorouder van een halfling te vinden, gezien de naam van die halfling:

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

Dit wordt omgezet in de volgende 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)

Wanneer u deze query uitvoert voor de halfling 'Bilbo', krijgt u 'Bungo' als resultaat.

De directe afstammelingen van een entiteit ophalen

De volgende query maakt ook gebruik van GetAncestor, maar deze keer om de directe afstammelingen van een halfling te vinden, op basis van de naam van de halfling.

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

Dit wordt omgezet in de volgende 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)

Als u deze query uitvoert voor de halfling "Mungo", retourneert "Bungo", "Belba", "Longo" en "Linda".

Alle voorouders van een entiteit ophalen

GetAncestor is handig om één niveau omhoog of omlaag te zoeken, of zelfs een opgegeven aantal niveaus. Aan de andere kant is IsDescendantOf nuttig voor het vinden van alle voorouders of afhankelijke personen. De volgende query gebruikt bijvoorbeeld IsDescendantOf om alle voorouders van een halfling te vinden, gegeven de naam van die halfling.

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

Belangrijk

IsDescendantOf retourneert 'true' voor zichzelf, daarom wordt het gefilterd in de bovenstaande query.

Dit wordt omgezet in de volgende 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

Wanneer u deze query uitvoert voor de halfling 'Bilbo', worden 'Bungo', 'Mungo' en 'Balbo' geretourneerd.

Alle afstammelingen van een entiteit ophalen

De volgende query maakt ook gebruik van IsDescendantOf, maar deze keer om alle nakomelingen van een halfling te vinden, gegeven de naam van die halfling:

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

Dit wordt omgezet in de volgende 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()

Als u deze query uitvoert voor de halfling "Mungo", geeft het de volgende resultaten: "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho", en "Poppy".

Het vinden van een gemeenschappelijke voorouder

Een van de meest voorkomende vragen over deze specifieke familieboom is: "Wie is de gemeenschappelijke voorouder van Frodo en Bilbo?" We kunnen IsDescendantOf gebruiken om een dergelijke query te schrijven:

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

Dit wordt omgezet in de volgende 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

Het uitvoeren van deze query met 'Bilbo' en 'Frodo' vertelt ons dat hun gemeenschappelijke voorouder 'Balbo' is.

Hiërarchieën bijwerken

De normale mechanismen voor het bijhouden van wijzigingen en SaveChanges kunnen worden gebruikt om hierarchyid kolommen bij te werken.

Een sub-hiërarchie opnieuw toewijzen

Ik weet bijvoorbeeld zeker dat we allemaal het schandaal van SR 1752 (a.k.a. "LongoGate") herinneren toen DNA-tests onthulden dat Longo niet in feite de zoon van Mungo was, maar eigenlijk de zoon van Ponto! Een gevolg van dit schandaal was dat de stamboom opnieuw moest worden geschreven. Met name Longo en al zijn nakomelingen moesten van Mungo naar Ponto opnieuw worden geherouderd. GetReparentedValue kan hiervoor worden gebruikt. Bijvoorbeeld: eerst wordt 'Longo' en al zijn nakomelingen opgevraagd.

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

Vervolgens wordt GetReparentedValue gebruikt om de HierarchyId voor Longo en elke afstamming bij te werken, gevolgd door een aanroep naar SaveChangesAsync:

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

await context.SaveChangesAsync();

Dit resulteert in de volgende database-update:

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;

Gebruik deze parameters:

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

Notitie

De parameterwaarden voor HierarchyId eigenschappen worden in de compacte, binaire indeling naar de database verzonden.

Na de update retourneert een query naar de afstammelingen van 'Mungo' 'Bungo', 'Belba', 'Linda', 'Bingo', 'Bilbo', 'Falco' en 'Poppy', terwijl een query naar de afstammelingen van 'Ponto' 'Longo', 'Rosa', 'Polo', 'Otho', 'Posco', 'Prisca', 'Lotho', 'Ponto', 'Porto', 'Peony' en 'Angelica' retourneert.

Functietoewijzingen

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

Aanvullende informatiebronnen