Hierarchikus adatok az SQL Server EF Core-szolgáltatójában

Az Azure SQL és az SQL Server egy hierarchyid nevű speciális adattípussal rendelkezik, amely hierarchikus adatoktárolására szolgál. Ebben az esetben a "hierarchikus adatok" lényegében olyan adatokat jelentenek, amelyek fastruktúrát alkotnak, ahol minden elem rendelkezhet szülővel és/vagy gyermekekkel. Ilyen adatok például a következők:

• Szervezeti struktúra
• Fájlrendszer
• Tevékenységek egy projektben
• A nyelvi kifejezések osztályozása
• Weblapok közötti hivatkozások grafikonja

Az adatbázis ezután hierarchikus struktúrájával képes lekérdezéseket futtatni ezen adatokon. Egy lekérdezés például megkeresheti az adott elemek elődeit és függőségeit, vagy megkeresheti az összes elemet a hierarchia bizonyos mélységében.

HierarchyId használata a .NET-ben és az EF Core-ban

A legalacsonyabb szinten a Microsoft.SqlServer.Types NuGet-csomag tartalmaz egy SqlHierarchyIdnevű típust. Bár ez a típus támogatja a hierarchiaértékek használatát, a LINQ-ban kissé nehézkes a használata.

A következő szinten bevezettünk egy új Microsoft.EntityFrameworkCore.SqlServer.Abstractions csomagot, amely egy magasabb szintű HierarchyId típust tartalmaz, amely az entitástípusokban való használatra szolgál.

A EF Core-funkciókhoz, például lekérdezésekhez és frissítésekhez a Microsoft.EntityFrameworkCore.SqlServer.HierarchyId csomag szükséges. Ez a csomag magával hozza a Microsoft.EntityFrameworkCore.SqlServer.Abstractions és Microsoft.SqlServer.Types tranzitív függőségeket, és így gyakran ez az egyetlen szükséges csomag.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

A csomag telepítése után a HierarchyId használata engedélyezve lesz, ha meghívja UseHierarchyId az alkalmazás UseSqlServerhívásának részeként. Például:

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

Hierarchiák modellezése

A HierarchyId típus egy entitástípus tulajdonságaihoz használható. Tegyük fel például, hogy néhány fiktív félszerzetapai családfáját szeretnénk modellezni. A Halflingentitástípusnál egy HierarchyId tulajdonság használható a családfa minden félvérének megkeresésére.

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

Ebben az esetben a családfa gyökere a család pátriárkája. Minden félszerzet nyomon követhető a leszármazási fa csúcsától lefelé a PathFromPatriarch tulajdonságukkal. Az SQL Server kompakt bináris formátumot használ ezekhez az elérési utakhoz, de kóddal való munka során gyakori az átalakítás az ember által olvasható sztring formátumra és onnan vissza. Ebben a ábrázolásban az egyes szinteken lévő pozíciót egy / karakter választja el egymástól. Vegyük például a családfát az alábbi ábrán:

Ebben a fában:

• Balbo a fa gyökerénél van, amelyet /képvisel.
• Balbónak öt gyermeke van, akiket a következők képviselnek: /1/, /2/, /3/, /4/és /5/.
• Balbo első gyermeke, Mungo szintén öt gyermekkel rendelkezik , amelyeket /1/1/, /1/2/, /1/3/, /1/4/és /1/5/képvisel . Figyelje meg, hogy a Mungo esetében a HierarchyId (/1/) előtagként szerepel az összes gyermeke előtt.
• Hasonlóképpen, Balbo harmadik gyermeke, Ponto, két gyermekkel rendelkezik, amelyeket /3/1/ és /3/2/képviselnek. Ismét minden ilyen gyermek előtagja a Ponto HierarchyId, amelyet /3/képvisel.
• És így tovább a fán...

A következő kód beszúrja ezt a családfát egy adatbázisba az EF Core használatával:

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

Hierarchiák lekérdezése

HierarchyId a LINQ-lekérdezésekben használható számos metódust tesz elérhetővé.

Emellett az operátorok ==, !=, <, <=, > és >= is használhatók.

Az alábbiakban példákat láthat ezekre a módszerekre a LINQ-lekérdezésekben.

Entitások lekérése egy adott szinten a fában

Az alábbi lekérdezés GetLevel használatával adja vissza az összes félszerzetet egy adott szinten a családfában.

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

Ez a következő SQL-hez lesz lefordítva:

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

Ezt ciklusban futtatva minden generációhoz lekérhetjük a felezési műveletet:

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

Szerezd meg egy entitás közvetlen ősét

Az alábbi lekérdezés a GetAncestor-t használja a félszerzet közvetlen ősének megkereséséhez, tekintettel a félszerzet nevére.

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

Ez a következő SQL-hez lesz lefordítva:

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)

A "Bilbo" félszerzet lekérdezésének futtatása a "Bungo" értéket adja vissza.

Entitás közvetlen leszármazottainak lekérése

A következő lekérdezés szintén használja a GetAncestor-t, de ezúttal egy félszerzet közvetlen leszármazottainak megkeresésére, annak nevét figyelembe véve.

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

Ez a következő SQL-hez lesz lefordítva:

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)

A "Mungo" nevű félszerzet lekérdezésének futtatása a következő eredményeket adja: "Bungo", "Belba", "Longo" és "Linda".

Entitás összes elődjének lekérése

A GetAncestor hasznos egy szint felfelé vagy lefelé történő kereséséhez, vagy akár egy megadott számú szint esetén. Másrészt a IsDescendantOf hasznos az összes előd vagy függő megtalálásához. A következő lekérdezés például IsDescendantOf használatával keresi meg egy félszerzet összes elődjét, ha ismerjük a félszerzet nevét.

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

Ez a következő SQL-hez lesz lefordítva:

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

A "Bilbo" félszerzet lekérdezésének futtatása a "Bungo", a "Mungo" és a "Balbo" eredményeket adja vissza.

Entitás összes leszármazottjának lekérése

A következő lekérdezés IsDescendantOfis használ, de ezúttal egy félszerzet összes leszármazottja számára, figyelembe véve az adott félszerzet nevét.

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

Ez a következő SQL-hez lesz lefordítva:

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

A "Mungo" nevű félszerzetre vonatkozó lekérdezés futtatása a következő neveket adja vissza: "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" és "Poppy".

Közös ős keresése

Az egyik leggyakrabban feltett kérdés az adott családfáról: "ki Frodo és Bilbo közös őse?" Az ilyen lekérdezések írásához használhatjuk a 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();

Ez a következő SQL-hez lesz lefordítva:

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

A lekérdezés "Bilbo" és "Frodo" használatával való futtatása azt jelzi, hogy a közös ősük a "Balbo".

Hierarchiák frissítése

A normál változáskövetési és SaveChanges mechanizmusokkal frissíthetők hierarchyid oszlopok.

alhierarchia újrasznevezése

Például tudom, hogy mindannyian emlékszünk az SR 1752-es botrányra (más néven "LongoGate"), amely során a DNS-vizsgálatok kiderítették, hogy Longo nem Mungo fia, hanem Ponto fia! A botrány egyik következménye az volt, hogy újra kellett írni a családfát. Különösen Longót és minden leszármazottját újra kellett nevelni Mungóról Pontóra. GetReparentedValue erre használható. Például lekérdezik az első "Longót" és az összes leszármazottját:

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

Ezután a GetReparentedValue Longo és minden leszármazott HierarchyId-jének frissítésére szolgál, ezt követően pedig a SaveChangesAsynchívásra kerül sor.

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

await context.SaveChangesAsync();

Ez a következő adatbázis-frissítést eredményezi:

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;

Az alábbi paraméterek használatával:

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

A frissítés után a "Mungo" leszármazottainak lekérdezése a "Bungo" értéket adja vissza, A "Belba", a "Linda", a "Bingo", a "Bilbo", a "Falco" és a "Poppy" a "Ponto" leszármazottainak lekérdezése során a "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" és "Angelica" értéket adja vissza.

Függvényleképezések

További erőforrások

• Hierarchikus adatok az SQL Serverben