Sdílet prostřednictvím

Hierarchická data ve zprostředkovateli SQL Server EF Core

  • Článek

Poznámka:

Tato funkce byla přidána v EF Core 8.0.

Azure SQL a SQL Server mají speciální datový typ, hierarchyid který se používá k ukládání hierarchických dat. V tomto případě "hierarchická data" v podstatě znamenají data, která tvoří strukturu stromu, kde každá položka může mít nadřazený objekt nebo podřízené položky. Mezi příklady těchto dat patří:

  • Organizační struktura
  • Systém souborů
  • Sada úkolů v projektu
  • Taxonomie jazykových termínů
  • Graf odkazů mezi webovými stránkami

Databáze pak může spouštět dotazy na tato data pomocí své hierarchické struktury. Dotaz může například najít nadřazené položky a závislé položky nebo najít všechny položky v určité hloubkě v hierarchii.

Použití HierarchyId v .NET a EF Core

Na nejnižší úrovni obsahuje balíček NuGet Microsoft.SqlServer.Types typ s názvem SqlHierarchyId. I když tento typ podporuje pracovní hodnoty hierarchií, je trochu těžkopádný pracovat s jazykem LINQ.

Na další úrovni byl zaveden nový balíček Microsoft.EntityFrameworkCore.SqlServer.Abstractions , který obsahuje typ vyšší úrovně HierarchyId určený pro použití v typech entit.

Tip

Typ HierarchyId je více idiomatice pro normy .NET než SqlHierarchyId, který je místo toho modelován po tom, jak jsou typy rozhraní .NET Framework hostovány uvnitř databázového stroje SQL Server. HierarchyId je navržený tak, aby fungoval s EF Core, ale dá se použít i mimo EF Core v jiných aplikacích. Balíček Microsoft.EntityFrameworkCore.SqlServer.Abstractions neodkazuje na žádné další balíčky, a proto má minimální dopad na nasazenou velikost a závislosti aplikace.

HierarchyId Použití funkcí EF Core, jako jsou dotazy a aktualizace, vyžaduje balíček Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Tento balíček přináší Microsoft.EntityFrameworkCore.SqlServer.Abstractions tranzitivní Microsoft.SqlServer.Types závislosti a proto je často jediným potřebným balíčkem.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Po instalaci balíčku je použití HierarchyId povoleno voláním UseHierarchyId v rámci volání UseSqlServeraplikace . Příklad:

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

Hierarchie modelování

Typ HierarchyId lze použít pro vlastnosti typu entity. Předpokládejme například, že chceme modelovat paternální rodinný strom některých fiktivních poločasů. V typu entity pro HalflingHierarchyId lze vlastnost použít k vyhledání každé poloviny v rodinném stromu.

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

Kód uvedený zde a v následujících příkladech pochází z HierarchyIdSample.cs.

Tip

V případě potřeby HierarchyId je vhodný pro použití jako typ klíčové vlastnosti.

V tomto případě je rodinný strom kořenem identity rodiny. Každý poločas lze vysledovat ze stromu směrem dolů pomocí jeho PathFromPatriarch vlastnosti. SQL Server pro tyto cesty používá kompaktní binární formát, ale při práci s kódem je běžné parsovat a od reprezentace řetězce čitelné pro člověka. V této reprezentaci je pozice na každé úrovni oddělena znakem / . Představte si například rodinný strom v následujícím diagramu:

Poločasový rodokmen

V tomto stromu:

  • Balbo je v kořeni stromu, reprezentovaný /.
  • Balbo má pět dětí reprezentovaných /1/, /2/, /3/, /4/a /5/.
  • První dítě Balbo, Mungo, má také pět dětí, reprezentované /1/1/, /1/2/, /1/3/, /1/4/a /1/5/. Všimněte si, že HierarchyId pro Mungo (/1/) je předpona pro všechny jeho děti.
  • Podobně, Balbo třetí dítě, Ponto, má dvě děti, reprezentované /3/1/ a /3/2/. Opět každý z těchto dětí je předponou HierarchyId pro Ponto, který je reprezentován jako /3/.
  • A tak dál dolů po stromě...

Následující kód vloží tento rodinný strom do databáze pomocí 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

V případě potřeby lze desetinné hodnoty použít k vytvoření nových uzlů mezi dvěma existujícími uzly. Například /3/2.5/2/ jde mezi /3/2/2/ a /3/3/2/.

Dotazování hierarchií

HierarchyId zveřejňuje několik metod, které lze použít v dotazech LINQ.

metoda Popis
GetAncestor(int n) Získá úrovně uzlu n hierarchický strom.
GetDescendant(HierarchyId? child1, HierarchyId? child2) Získá hodnotu potomkového uzlu, který je větší než child1 a menší než child2.
GetLevel() Získá úroveň tohoto uzlu v hierarchickém stromu.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) Získá hodnotu představující umístění nového uzlu, který má cestu od newRoot stejné cesty k této cestě oldRoot , a efektivně ji přesune do nového umístění.
IsDescendantOf(HierarchyId? parent) Získá hodnotu určující, zda tento uzel je potomkem parent.

Kromě toho lze operátory ==, , <!=, <=> a >= lze použít.

Tady jsou příklady použití těchto metod v dotazech LINQ.

Získání entit na dané úrovni ve stromu

Následující dotaz používá GetLevel k vrácení všech polovin na dané úrovni v rodinném stromu:

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

To se přeloží na následující SQL:

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

Když to spustíme ve smyčce, můžeme získat poloviny pro každou generaci:

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

Získání přímého předka entity

Následující dotaz používá GetAncestor k vyhledání přímého předka poloviny, vzhledem k tomu, že název poloviny:

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

To se přeloží na následující 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)

Spuštění tohoto dotazu pro polovinu "Bilbo" vrátí "Bungo".

Získání přímých potomků entity

Následující dotaz také používá GetAncestor, ale tentokrát k vyhledání přímých potomků poloviny, vzhledem k jeho názvu:

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

To se přeloží na následující 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)

Spuštění tohoto dotazu pro polovinu "Mungo" vrátí "Bungo", "Belba", "Longo" a "Linda".

Získání všech předků entity

GetAncestor je užitečné pro vyhledávání na jedné nebo nižší úrovni, nebo skutečně zadaného počtu úrovní. Na druhé straně IsDescendantOf je užitečné pro vyhledání všech předků nebo závislých. Například následující dotaz používá IsDescendantOf k vyhledání všech předků poloviny, vzhledem k jeho názvu:

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

Důležité

IsDescendantOf vrátí hodnotu true pro sebe, což je důvod, proč je filtrován v dotazu výše.

To se přeloží na následující 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

Spuštění tohoto dotazu pro polovinu "Bilbo" vrátí "Bungo", "Mungo" a "Balbo".

Získání všech potomků entity

Následující dotaz také používá IsDescendantOf, ale tentokrát všem potomkům poloviny, vzhledem k tomu, že název poloviny:

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

To se přeloží na následující 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()

Spuštění tohoto dotazu pro polovinu "Mungo" vrátí "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" a "Poppy".

Nalezení společného předka

Jednou z nejčastějších otázek týkajících se tohoto konkrétního rodinného stromu je, "kdo je společný předek Frodo a Bilbo?" Tento dotaz můžeme použít IsDescendantOf k zápisu tohoto dotazu:

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

To se přeloží na následující 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

Spuštění tohoto dotazu s "Bilbo" a "Frodo" nám říká, že jejich společný předek je "Balbo".

Aktualizace hierarchií

K aktualizaci hierarchyid sloupců lze použít normální mechanismy sledování změn a SaveChanges.

Opětovné závorky pod hierarchií

Jsem si například jistý, že všichni pamatujeme na skandál SR 1752 (a.k.a. "LongoGate"), když dna testování ukázalo, že Longo nebyl ve skutečnosti syn Mungo, ale ve skutečnosti syn Ponto! Jedním z pádů z tohoto skandálu bylo, že rodinný strom musel být znovu napsán. Zejména Longo a všichni jeho potomci museli být znovu nadřazení z Mungo do Ponto. GetReparentedValue se dá použít k tomu. Například první "Longo" a všechny jeho potomky jsou dotazovány:

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

Pak GetReparentedValue se používá k aktualizaci HierarchyId pro Longo a každé sestupné, následované voláním SaveChangesAsync:

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

await context.SaveChangesAsync();

Výsledkem je následující aktualizace databáze:

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;

Pomocí těchto parametrů:

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

Poznámka:

Hodnoty parametrů vlastností HierarchyId se posílají do databáze v kompaktním binárním formátu.

Po aktualizaci se dotazování na potomky "Mungo" vrátí "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" a "Poppy", zatímco dotazování na potomky "Ponto" vrátí "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" a "Angelica".

Mapování funkcí

.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::P arse(@input)
hierarchyId.ToString() @hierarchyId.ToString()

Další materiály