Données hiérarchiques dans le fournisseur EF Core SQL Server

Remarque

Cette fonctionnalité a été ajoutée dans EF Core 8.0.

Azure SQL et SQL Server ont un type de données spécial appelé hierarchyid utilisé pour stocker données hiérarchiques. Dans ce cas, les « données hiérarchiques » signifient essentiellement des données qui forment une structure d’arborescence, où chaque élément peut avoir un parent et/ou des enfants. Voici quelques exemples de ces données :

• Structure d'organisation
• Système de fichiers
• Ensemble de tâches dans un projet
• Taxonomie de termes langagiers
• Graphique de liens entre pages Web

La base de données peut ensuite exécuter des requêtes sur ces données à l’aide de sa structure hiérarchique. Par exemple, une requête peut trouver des ancêtres et des dépendants d’éléments donnés, ou rechercher tous les éléments à une certaine profondeur dans la hiérarchie.

Utilisation de HierarchyId dans .NET et EF Core

Au niveau le plus bas, le package NuGet Microsoft.SqlServer.Types inclut un type appelé SqlHierarchyId. Bien que ce type prenne en charge les valeurs hierarchyid de travail, il est un peu fastidieux de travailler avec LINQ.

Au niveau suivant, un nouveau package Microsoft.EntityFrameworkCore.SqlServer.Abstractions a été introduit, qui inclut un type HierarchyId de niveau supérieur destiné à être utilisé dans les types d’entités.

Conseil

Le HierarchyIdtype est plus idiomatique aux normes de .NET que SqlHierarchyId, qui est plutôt modélisé après la façon dont les types .NET Framework sont hébergés à l’intérieur du moteur de base de données SQL Server. HierarchyId est conçu pour fonctionner avec EF Core, mais il peut également être utilisé en dehors d’EF Core dans d’autres applications. Le package Microsoft.EntityFrameworkCore.SqlServer.Abstractions ne référence aucun autre package, et a donc un impact minimal sur la taille et les dépendances des applications déployées.

L’utilisation de HierarchyId pour les fonctionnalités EF Core, telles que les requêtes et les mises à jour, nécessite le package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Ce package apporte Microsoft.EntityFrameworkCore.SqlServer.Abstractions et Microsoft.SqlServer.Types en tant que dépendances transitives, et il est donc souvent le seul package nécessaire.

CLI .NET Core

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Visual Studio

• Outils > Gestionnaire de package NuGet > Console du Gestionnaire de package

• Exécutez les commandes suivantes :

  Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId
  

Astuce : Vous pouvez aussi installer des packages en cliquant avec le bouton droit sur le projet, puis en sélectionnant Gérer les packages NuGet.

Une fois le package installé, l’utilisation de HierarchyId est activée en appelant UseHierarchyId dans le cadre de l’appel de l’application à UseSqlServer. Par exemple :

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

Modélisation des hiérarchies

Le type HierarchyId peut être utilisé pour les propriétés d’un type d’entité. Par exemple, supposons que nous voulons modéliser l’arbre familial paternel de certains halflings. Dans le type d’entité pour Halfling, une propriété HierarchyId peut être utilisée pour localiser chaque halfling dans l’arborescence de la famille.

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

Conseil

Le code présenté ici et dans les exemples ci-dessous provient de HierarchyIdSample.cs.

Conseil

Si vous le souhaitez, HierarchyId convient à une utilisation comme type de propriété de clé.

Dans ce cas, l’arbre familial est enraciné avec le patriarche de la famille. Chaque halfling peut être tracé du patriarche vers le bas de l’arbre à l’aide de sa propriétéPathFromPatriarch. SQL Server utilise un format binaire compact pour ces chemins d’accès, mais il est courant d’analyser et à partir d’une représentation sous forme de chaîne lisible par l’homme lors de l’utilisation du code. Dans cette représentation, la position à chaque niveau est séparée par un caractère /. Par exemple, considérez l’arborescence familiale dans le diagramme ci-dessous :

Dans cette arborescence :

• Balbo est à la racine de l’arbre, représenté par /.
• Balbo a cinq enfants, représentés par /1/, /2/, /3/, /4/et /5/.
• Le premier enfant de Balbo, Mungo, a également cinq enfants, représentés par /1/1/, /1/2/, /1/3/, /1/4/et /1/5/. Remarquez que le HierarchyId de Mungo (/1/) est le préfixe de tous ses enfants.
• De même, le troisième enfant de Balbo, Ponto, a deux enfants, représentés par /3/1/ et /3/2/. Là encore, chacun de ces enfants est précédé de HierarchyId pour Ponto, qui est représenté comme /3/.
• Et ainsi de suite sur le bas de l’arbre...

Le code suivant insère cette arborescence familiale dans une base de données à l’aide d’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();

Conseil

Si nécessaire, les valeurs décimales peuvent être utilisées pour créer de nouveaux nœuds entre deux nœuds existants. Par exemple, /3/2.5/2/ passe entre /3/2/2/ et /3/3/2/.

Interrogation des hiérarchies

HierarchyId expose plusieurs méthodes qui peuvent être utilisées dans les requêtes LINQ.

Méthode	Description
GetAncestor(int n)	Obtient le nœud n niveaux de l’arborescence hiérarchique.
GetDescendant(HierarchyId? child1, HierarchyId? child2)	Obtient la valeur d’un nœud descendant supérieur à child1 et inférieur à child2.
GetLevel()	Obtient le niveau de ce nœud dans l’arborescence hiérarchique.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)	Obtient une valeur représentant l’emplacement d’un nouveau nœud qui a un chemin d’accès de newRoot égal au chemin d’accès de oldRoot jusqu’à cela, en déplaçant cela vers le nouvel emplacement.
IsDescendantOf(HierarchyId? parent)	Obtient une valeur indiquant si ce nœud est un descendant de parent.

En outre, les opérateurs ==, !=, <, <=, > et >= peuvent être utilisés.

Voici des exemples d’utilisation de ces méthodes dans les requêtes LINQ.

Obtenir des entités à un niveau donné dans l’arborescence

La requête suivante utilise GetLevel pour retourner tous les demi-points à un niveau donné dans l’arborescence de la famille :

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

Cela se traduit par le code SQL suivant :

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

En exécutant cela dans une boucle, nous pouvons obtenir les demi-points pour chaque génération :

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

Obtenir l’ancêtre direct d’une entité

La requête suivante utilise GetAncestor pour trouver l’ancêtre direct d’un halfling, compte tenu du nom de ce demi-point :

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

Cela se traduit par le code SQL suivant :

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’exécution de cette requête pour le halfling de « Bilbo » retourne « Bungo ».

Obtenir les descendants directs d’une entité

La requête suivante utilise également GetAncestor, mais cette fois pour rechercher les descendants directs d’un halfling, compte tenu du nom de ce halfling :

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

Cela se traduit par le code SQL suivant :

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’exécution de cette requête pour le halfling de « Mungo » retourne « Bungo », « Belba », « Longo » et « Linda ».

Obtenir tous les ancêtres d’une entité

GetAncestor est utile pour rechercher un niveau unique ou, en effet, un nombre spécifié de niveaux. En revanche, IsDescendantOf est utile pour trouver tous les ancêtres ou dépendants. Par exemple, la requête suivante utilise IsDescendantOf pour rechercher tous les ancêtres d’un halfling, compte tenu du nom de ce 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());

Important

IsDescendantOf retourne la valeur true pour elle-même, c’est pourquoi elle est filtrée dans la requête ci-dessus.

Cela se traduit par le code SQL suivant :

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’exécution de cette requête pour le halfling de « Bilbo » retourne « Bungo », « Mungo » et « Balbo ».

Obtenir toutes les décroissantes d’une entité

La requête suivante utilise également IsDescendantOf, mais cette fois-ci pour tous les descendants d’un halfling, compte tenu du nom de ce demi-point :

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

Cela se traduit par le code SQL suivant :

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’exécution de cette requête pour le halfling de « Mungo » retourne « Bungo », « Belba », « Longo », « Linda », « Bingo », « Bilbo », « Otho », « Falco », « Lotho », « Lotho », et « Poppy ».

Trouver un ancêtre commun

L’une des questions les plus courantes posées sur cet arbre familial particulier est « qui est l’ancêtre commun de Frodo et Bilbo ? » Nous pouvons utiliser IsDescendantOf pour écrire une telle requête :

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

Cela se traduit par le code SQL suivant :

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’exécution de cette requête avec « Bilbo » et « Frodo » nous indique que leur ancêtre commun est « Balbo ».

Mise à jour des hiérarchies

Les mécanismes de SaveChanges et SaveChanges peuvent être utilisés pour mettre à jour les colonnes hierarchyid.

Re-parenter une sous-hiérarchie

Par exemple, je suis sûr que nous nous souvenons tous du scandale de SR 1752 (a.k.a. « LongoGate ») quand des tests d’ADN ont révélé que Longo n’était pas en fait le fils de Mungo, mais en fait le fils de Ponto ! L’une des retombées de ce scandale était que l’arbre familial devait être réécrit. En particulier, Longo et tous ses descendants devaient être re-parentés de Mungo à Ponto. GetReparentedValue peut être utilisé pour ce faire. Par exemple, tout d’abord « Longo » et tous ses descendants sont interrogés :

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

Ensuite, GetReparentedValue est utilisée pour mettre à jour le HierarchyId pour Longo et chaque descendant, suivi d’un appel à SaveChangesAsync:

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

await context.SaveChangesAsync();

Cela entraîne la mise à jour de base de données suivante :

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;

À l’aide de ces paramètres :

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

Remarque

Les valeurs des paramètres des propriétés HierarchyId sont envoyées à la base de données dans leur format binaire compact et binaire.

Après la mise à jour, l’interrogation des descendants de « Mungo » retourne « Bungo », « Belba », « Linda », « Bingo », « Bilbo », « Falco », et « Poppy », tout en interrogeant pour les descendants de « Ponto », « Longo », « Rosa », « Polo », « Otho », « Posco », « Prisca », « Lotho », « Ponto », « Porto », « Peony », et « Angelica ».

Mappages de fonctions

.NET	SQL
hierarchyId.GetAncestor(n)	@hierarchyId.GetAncestor(@n)
hierarchyId.GetDescendant(enfant)	@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()

Ressources supplémentaires

• Données hiérarchiques dans SQL Server