Hierarkiska data i SQL Server EF Core-providern

Artikel
01/15/2025

Not

Den här funktionen har lagts till i EF Core 8.0.

Azure SQL och SQL Server har en särskild datatyp som kallas hierarchyid som används för att lagra hierarkiska data. I det här fallet innebär "hierarkiska data" i princip data som utgör en trädstruktur, där varje objekt kan ha en överordnad och/eller underordnade objekt. Exempel på sådana data är:

En organisationsstruktur
Ett filsystem
En uppsättning aktiviteter i ett projekt
En taxonomi av språktermer
Ett diagram över länkar mellan webbsidor

Databasen kan sedan köra frågor mot dessa data med hjälp av dess hierarkiska struktur. En fråga kan till exempel hitta överordnade och beroenden av angivna objekt eller hitta alla objekt på ett visst djup i hierarkin.

Använda HierarchyId i .NET och EF Core

På den lägsta nivån innehåller Microsoft.SqlServer.Types NuGet-paketet en typ som heter SqlHierarchyId. Även om den här typen stöder arbetshierarkiidvärden är det lite besvärligt att arbeta med i LINQ.

På nästa nivå har ett nytt Microsoft.EntityFrameworkCore.SqlServer.Abstractions-paketet introducerats, som innehåller en HierarchyId på högre nivå som är avsedd att användas i entitetstyper.

Tips / Dricks

Den HierarchyId typen är mer idiomatisk för normerna för .NET än SqlHierarchyId, som i stället modelleras efter hur .NET Framework-typer finns i SQL Server-databasmotorn. HierarchyId är utformat för att fungera med EF Core, men det kan också användas utanför EF Core i andra program. Det Microsoft.EntityFrameworkCore.SqlServer.Abstractions paketet refererar inte till några andra paket och har därför minimal påverkan på distribuerad programstorlek och beroenden.

Användning av HierarchyId för EF Core-funktioner som frågor och uppdateringar kräver paketet Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Det här paketet innehåller Microsoft.EntityFrameworkCore.SqlServer.Abstractions och Microsoft.SqlServer.Types som transitiva beroenden, och är därför ofta det enda paket som behövs.

.NET Core CLI
Visual Studio

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Verktyg > NuGet Package Manager > Package Manager Console

Kör följande kommandon:

Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Tips: Du kan också installera paket genom att högerklicka på projektet och välja Hantera NuGet-paket

När paketet har installerats aktiveras användningen av HierarchyId genom att anropa UseHierarchyId som en del av programmets anrop till UseSqlServer. Till exempel:

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

Modelleringshierarkier

Den HierarchyId typen kan användas för egenskaper av en entitetstyp. Tänk dig till exempel att vi vill modellera upp släktträdet för några fiktiva halvlingar. I entitetstypen för Halflingkan en HierarchyId-egenskap användas för att hitta varje halvering i släktträdet.

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

Tips

Koden som visas här och i exemplen nedan kommer från HierarchyIdSample.cs.

Tips

Om du vill kan HierarchyId användas som nyckelegenskapstyp.

I det här fallet är släktträdet rotat med familjens patriark. Varje halvlängd kan spåras från Patriarken ner i trädet med hjälp av dess PathFromPatriarch egenskap. SQL Server använder ett kompakt binärt format för dessa sökvägar, men det är vanligt att parsa till och från en mänsklig läsbar strängrepresentation när du arbetar med kod. I den här representationen avgränsas positionen på varje nivå med ett / tecken. Tänk till exempel på släktträdet i diagrammet nedan:

Halfling familjeträd

I det här trädet:

Balbo ligger vid trädets rot, representerad av /.
Balbo har fem barn som representeras av /1/, /2/, /3/, /4/och /5/.
Balbos första barn, Mungo, har också fem barn, som representeras av /1/1/, /1/2/, /1/3/, /1/4/och /1/5/. Observera att HierarchyId för Mungo (/1/) är prefixet för alla hans barn.
På samma sätt har Balbos tredje barn, Ponto, två barn, representerade av /3/1/ och /3/2/. Återigen är vart och ett av dessa barn försett med prefixet HierarchyId för Ponto, vilket representeras som /3/.
Och så vidare ner i trädet...

Följande kod infogar det här släktträdet i en databas med 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();

Tips

Om det behövs kan decimalvärden användas för att skapa nya noder mellan två befintliga noder. Till exempel går /3/2.5/2/ mellan /3/2/2/ och /3/3/2/.

Fråga hierarkier

HierarchyId exponerar flera metoder som kan användas i LINQ-frågor.

Metod	Beskrivning
`GetAncestor(int n)`	Hämtar noden `n` jämnar upp det hierarkiska trädet.
`GetDescendant(HierarchyId? child1, HierarchyId? child2)`	Hämtar värdet för en underordnade nod som är större än `child1` och mindre än `child2`.
`GetLevel()`	Hämtar nivån för den här noden i det hierarkiska trädet.
`GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)`	Hämtar ett värde som representerar platsen för en ny nod som har en sökväg från `newRoot` lika med sökvägen från `oldRoot` till detta, vilket effektivt flyttar den till den nya platsen.
`IsDescendantOf(HierarchyId? parent)`	Hämtar ett värde som anger om den här noden är en efterföljande nod till `parent`.

Dessutom kan operatorerna ==, !=, <, <=, > och >= användas.

Följande är exempel på hur du använder dessa metoder i LINQ-frågor.

Hämta entiteter på en viss nivå i trädet

Följande fråga använder GetLevel för att returnera alla halvlingar på en viss nivå i släktträdet:

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

Detta översätts till följande SQL:

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

Genom att köra detta i en loop kan vi få fram halvlingar för varje generation:

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

Hämta den direkta överordnade för en entitet

Följande fråga använder GetAncestor för att hitta den direkta förfadern till en halvlängdsmänniska, givet halvlängdsmänniskans namn.

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

Detta översätts till följande 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)

Om du kör den här frågan för den halva "Bilbo" returneras "Bungo".

Hämta direkta underordnade till en entitet

Följande fråga använder också GetAncestor, men den här gången för att hitta de direkta ättlingarna till en halvlängdsman, givet halvlängdsmannens namn.

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

Detta översätts till följande 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)

Om du kör den här frågan för den halva "Mungo" returneras "Bungo", "Belba", "Longo" och "Linda".

Hämta alla förfäder till en entitet

GetAncestor är användbart för att söka upp eller ned en enda nivå, eller faktiskt ett angivet antal nivåer. Å andra sidan är IsDescendantOf användbart för att hitta alla överordnade eller beroenden. Följande fråga använder till exempel IsDescendantOf för att hitta alla förfäder till en halvlängdsman, givet halvlängdsmanens namn.

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

Viktig

IsDescendantOf returnerar sant för sig själv, vilket är varför det filtreras bort i sökfrågan ovan.

Detta översätts till följande 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

Om du kör den här frågan för den halva "Bilbo" returneras "Bungo", "Mungo" och "Balbo".

Hämta alla efterkommande till en entitet

Följande fråga använder också IsDescendantOf, men den här gången till alla efterkommande till en hober, med tanke på hoberns namn.

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

Detta översätts till följande 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()

Om du kör den här frågan för hälftlingen "Mungo" returneras "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" och "Poppy".

Hitta en gemensam förfader

En av de vanligaste frågorna som ställs om just det här släktträdet är"vem är den gemensamma förfadern till Frodo och Bilbo?" Vi kan använda IsDescendantOf för att skriva en sådan fråga:

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

Detta översätts till följande 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

Att köra den här frågan med "Bilbo" och "Frodo" säger oss att deras gemensamma förfader är "Balbo".

Uppdatera hierarkier

De normala ändringsspårningsmekanismerna och SaveChanges kan användas för att uppdatera hierarchyid kolumner.

Överordna en underhierarki

Till exempel är jag säker på att vi alla minns skandalen med SR 1752 (även kallat "LongoGate") när DNA-tester avslöjade att Longo faktiskt inte var son till Mungo, utan faktiskt son till Ponto! Ett resultat av denna skandal var att släktträdet behövde skrivas om. I synnerhet behövde Longo och alla hans ättlingar bli omföräldrade från Mungo till Ponto. GetReparentedValue kan användas för att göra detta. Till exempel efterfrågas "Longo" och alla hans ättlingar:

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

Sedan används GetReparentedValue för att uppdatera HierarchyId för Longo och varje underordnad, följt av ett anrop till SaveChangesAsync:

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

await context.SaveChangesAsync();

Detta resulterar i följande databasuppdatering:

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;

Använd följande parametrar:

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

Not

Parametrarnas värden för HierarchyId egenskaper skickas till databasen i deras kompakta binära format.

Efter uppdateringen returnerar frågan efter ättlingarna till "Mungo" "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" och "Poppy", medan frågan efter ättlingarna till "Ponto" returnerar "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" och "Angelica".

Funktionsmappningar

.NÄT	SQL
hierarchyId.GetAncestor(n)	@hierarchyId.GetAncestor(@n)
hierarchyId.GetDescendant(underordnad)	@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()

Ytterligare resurser

hierarkiska data i SQL Server

Dela via

Hierarkiska data i SQL Server EF Core-providern

Använda HierarchyId i .NET och EF Core

Modelleringshierarkier

Fråga hierarkier

Uppdatera hierarkier

Funktionsmappningar

Ytterligare resurser

Ytterligare resurser