Dati gerarchici nel provider EF Core di SQL Server

Azure SQL e SQL Server hanno un tipo di dati speciale denominato hierarchyid usato per archiviare dati gerarchici. In questo caso, "dati gerarchici" significa essenzialmente i dati che costituiscono una struttura ad albero, in cui ogni elemento può avere un elemento padre e/o figlio. Tra questi dati sono incluse:

• Una struttura organizzativa
• Un file system
• Un set di attività di un progetto
• Una tassonomia di termini del linguaggio
• Un grafico di collegamenti tra pagine Web

Il database è quindi in grado di eseguire query su questi dati usando la relativa struttura gerarchica. Ad esempio, una query può trovare predecessori e dipendenti di elementi specificati o trovare tutti gli elementi in una certa profondità nella gerarchia.

Uso di HierarchyId in .NET ed EF Core

Al livello più basso, il pacchetto NuGet Microsoft.SqlServer.Types include un tipo denominato SqlHierarchyId. Anche se questo tipo supporta valori hierarchyid funzionanti, è un po' complesso da usare in LINQ.

Al livello successivo è stato introdotto un nuovo pacchetto Microsoft.EntityFrameworkCore.SqlServer.Abstractions, che include un tipo di livello HierarchyId superiore destinato all'uso nei tipi di entità.

L'utilizzo di HierarchyId per funzionalità di EF Core, come le query e gli aggiornamenti, richiede il pacchetto Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Questo pacchetto inserisce Microsoft.EntityFrameworkCore.SqlServer.Abstractions e Microsoft.SqlServer.Types come dipendenze transitive e quindi è spesso l'unico pacchetto necessario.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Dopo aver installato il pacchetto, l'uso di HierarchyId viene abilitato chiamando UseHierarchyId come parte della chiamata dell'applicazione a UseSqlServer. Per esempio:

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

Modellazione di gerarchie

Il tipo HierarchyId può essere usato per le proprietà di un tipo di entità. Si supponga, ad esempio, di voler modellare l'albero della famiglia paterna di alcuni halfling fittizi. Nel tipo di entità per Halfling, una proprietà HierarchyId può essere usata per individuare ogni halfling nell'albero genealogico.

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 questo caso, l'albero della famiglia è radicato con il patriarca della famiglia. Ogni halfling può essere tracciato dal patriarca lungo l'albero usando la proprietà di PathFromPatriarch. SQL Server usa un formato binario compatto per questi percorsi, ma è comune convertire in e da una rappresentazione di stringa leggibile quando si lavora con il codice. In questa rappresentazione, la posizione a ogni livello è separata da un carattere /. Si consideri ad esempio l'albero della famiglia nel diagramma seguente:

In questo albero:

• Balbo si trova nella radice dell'albero, rappresentato da /.
• Balbo ha cinque figli, rappresentati da /1/, /2/, /3//4/ e /5/.
• Il primo figlio di Balbo, Mungo, ha anche cinque figli, rappresentati da /1/1/, /1/2//1/3/, /1/4/ e /1/5/. Si noti che il HierarchyId per Mungo (/1/) è il prefisso per tutti i suoi figli.
• Analogamente, il terzo figlio di Balbo, Ponto, ha due figli, rappresentati da /3/1/ e /3/2/. Anche in questo caso, ognuno di questi figli è preceduto da HierarchyId per Ponto, rappresentato come /3/.
• E così via giù per l'albero...

Il codice seguente inserisce questo albero di famiglia in un database usando 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();

Esecuzione di query sulle gerarchie

HierarchyId espone diversi metodi che possono essere usati nelle query LINQ.

Inoltre, gli operatori ==, !=<, <=> e >= possono essere usati.

Di seguito sono riportati esempi di utilizzo di questi metodi nelle query LINQ.

Ottenere entità a un determinato livello nell'albero

La query seguente usa GetLevel per restituire tutti i halfling a un determinato livello nell'albero di famiglia:

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

Questo comportamento si traduce nel codice SQL seguente:

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

Eseguendo questa operazione in un ciclo possiamo ottenere i "halflings" per ogni generazione:

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

Ottenere il predecessore diretto di un'entità

La query seguente usa GetAncestor per trovare l'antenato diretto di un halfling, dato il nome dell'halfling:

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

Questo comportamento si traduce nel codice SQL seguente:

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)

L'esecuzione di questa query per il halfling "Bilbo" restituisce "Bungo".

Ottenere i discendenti diretti di un'entità

La query seguente usa anche GetAncestor, ma questa volta per trovare i discendenti diretti di un halfling, dato il nome dell'halfling:

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

Questo comportamento si traduce nel codice SQL seguente:

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)

L'esecuzione di questa query per il halfling "Mungo" restituisce "Bungo", "Belba", "Longo" e "Linda".

Ottenere tutti i predecessori di un'entità

GetAncestor è utile per la ricerca su o verso il basso di un singolo livello, o, in effetti, un numero specificato di livelli. D'altra parte, IsDescendantOf è utile per trovare tutti i predecessori o dipendenti. Ad esempio, la query seguente usa IsDescendantOf per trovare tutti gli antenati di un halfling, dato il nome di quel 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());

Questo comportamento si traduce nel codice SQL seguente:

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

L'esecuzione di questa query per il halfling "Bilbo" restituisce "Bungo", "Mungo" e "Balbo".

Ottenere tutti i discendenti di un'entità

La query seguente usa anche IsDescendantOf, ma questa volta per trovare tutti i discendenti di un halfling, dato il nome dell'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());

Questo comportamento si traduce nel codice SQL seguente:

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

L'esecuzione di questa query per il halfling "Mungo" restituisce "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" e "Poppy".

Ricerca di un predecessore comune

Una delle domande più comuni poste su questo particolare albero di famiglia è: "chi è l'antenato comune di Frodo e Bilbo?" È possibile usare IsDescendantOf per scrivere una query di questo tipo:

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

Questo comportamento si traduce nel codice SQL seguente:

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

L'esecuzione di questa query con "Bilbo" e "Frodo" indica che il loro predecessore comune è "Balbo".

Aggiornamento delle gerarchie

Il normale rilevamento delle modifiche e i meccanismi SaveChanges possono essere usati per aggiornare le hierarchyid colonne.

Riposizionamento di una sotto-gerarchia

Ad esempio, sono sicuro che tutti ricordiamo lo scandalo della SR 1752 (a.k.a. "LongoGate") quando il test del DNA ha rivelato che Longo non era in realtà il figlio di Mungo, ma in realtà il figlio di Ponto! Una conseguenza di questo scandalo fu che l'albero genealogico doveva essere riscritto. In particolare, Longo e tutti i suoi discendenti dovevano essere ri-parented da Mungo a Ponto. GetReparentedValuepuò esser usato per svolgere questa azione. Ad esempio, si eseguono delle query su "Longo" e su tutti i suoi discendenti:

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

Viene quindi GetReparentedValue usato per aggiornare HierarchyId per Longo e ogni discendente, seguito da una chiamata a SaveChangesAsync:

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

await context.SaveChangesAsync();

Questo si traduce nel seguente aggiornamento dei database:

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;

Uso di questi parametri:

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

Dopo l'aggiornamento, l'esecuzione di query per i discendenti di "Mungo" restituisce "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" e "Poppy", mentre l'esecuzione di una query per i discendenti di "Ponto" restituisce "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" e "Angelica".

Mapping delle funzioni

Risorse aggiuntive

• Dati gerarchici in SQL Server