Datos jerárquicos en el proveedor de SQL Server EF Core

Nota:

Esta característica se agregó en EF Core 8.0.

Azure SQL y SQL Server tienen un tipo de datos especial denominado hierarchyid que se usa para almacenar datos jerárquicos. En este caso, "datos jerárquicos" básicamente significa datos que forman una estructura de árbol, donde cada elemento puede tener un elemento primario o secundario. Algunos ejemplos de estos datos son:

• Una estructura organizativa
• Un sistema de archivos
• Un conjunto de tareas de un proyecto
• Una taxonomía de términos de idioma
• Un gráfico de vínculos entre páginas web

Después, la base de datos puede ejecutar consultas en estos datos mediante su estructura jerárquica. Por ejemplo, una consulta puede buscar antecesores y dependientes de elementos determinados, o los elementos de una profundidad determinada en la jerarquía.

Uso de HierarchyId en .NET y EF Core

En el nivel más bajo, el paquete NuGet Microsoft.SqlServer.Types incluye un tipo denominado SqlHierarchyId. Aunque este tipo admite valores hierarchyid de trabajo, es un poco complicado trabajar con él en LINQ.

En el siguiente nivel, se ha introducido un nuevo paquete Microsoft.EntityFrameworkCore.SqlServer.Abstractions, que incluye un tipo de nivel superior HierarchyId destinado a su uso en tipos de entidad.

Sugerencia

El tipo HierarchyId es más idiomático para las normas de .NET que SqlHierarchyId, que en su lugar se modela según cómo se hospeden los tipos de .NET Framework dentro del motor de base de datos de SQL Server. HierarchyId está diseñado para funcionar con EF Core, pero también se puede usar fuera de EF Core en otras aplicaciones. El paquete Microsoft.EntityFrameworkCore.SqlServer.Abstractions no hace referencia a ningún otro paquete, por lo que tiene un impacto mínimo en el tamaño y las dependencias de la aplicación implementadas.

El uso de HierarchyId para la funcionalidad de EF Core, como las consultas y las actualizaciones, requiere el paquete Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Este paquete incluye Microsoft.EntityFrameworkCore.SqlServer.Abstractions y Microsoft.SqlServer.Types como dependencias transitivas, por lo que suele ser el único paquete necesario.

CLI de .NET Core

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Visual Studio

• Herramientas > Administrador de paquetes NuGet > Consola del Administrador de paquetes

• Ejecute los comandos siguientes:

  Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId
  

Sugerencia: también puede instalar paquetes si hace clic con el botón derecho en el proyecto y selecciona Administrar paquetes NuGet

Una vez instalado el paquete, se habilita el uso de HierarchyId mediante una llamada UseHierarchyId a como parte de la llamada de la aplicación a UseSqlServer. Por ejemplo:

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

Jerarquías de modelado

El tipo HierarchyId se puede usar para las propiedades de un tipo de entidad. Por ejemplo, supongamos que queremos modelar el árbol genealógico paterno de unos medianos. En el tipo de entidad de Halfling, se puede usar una propiedad HierarchyId para buscar los medianos en el árbol genealógico.

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

Sugerencia

El código que se muestra aquí y en los ejemplos siguientes procede de HierarchyIdSample.cs.

Sugerencia

Si lo desea, HierarchyId es adecuado para su uso como tipo de propiedad de clave.

En este caso, el árbol genealógico tiene el patriarca de la familia en la raíz. Cada mediano se puede rastrear desde el patriarca hacia abajo por el árbol con su propiedad PathFromPatriarch. SQL Server usa un formato binario compacto para estas rutas de acceso, pero es habitual analizar hacia y desde una representación de cadena legible cuando se trabaja con código. En esta representación, las posiciones se separan por un carácter / en cada nivel. Por ejemplo, considere el árbol genealógico del diagrama siguiente:

En este árbol:

• Balbo está en la raíz del árbol, que se representa con /.
• Balbo tiene cinco hijos, representados por /1/, /2/, /3/, /4/ y /5/.
• El primer hijo de Balbo, Mungo, también tiene cinco hijos, representados por /1/1/, /1/2/, /1/3/, /1/4/ y /1/5/. Tenga en cuenta que HierarchyId para Mungo (/1/) es el prefijo para todos sus hijos.
• Del mismo modo, el tercer hijo de Balbo, Ponto, tiene dos hijos, representados por /3/1/ y /3/2/. De nuevo, cada hijo tiene el prefijo HierarchyId de Ponto, que se representa como /3/.
• Y así se desciende por el árbol...

El código siguiente inserta este árbol genealógico en una base de datos mediante 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();

Sugerencia

Si es necesario, se pueden usar valores decimales para crear nodos entre dos nodos existentes. Por ejemplo, /3/2.5/2/ va entre /3/2/2/ y /3/3/2/.

Consulta de jerarquías

HierarchyId expone varios métodos que pueden usarse en las consultas LINQ.

Método	Descripción
GetAncestor(int n)	Obtiene los niveles n del nodo hacia arriba en el árbol jerárquico.
GetDescendant(HierarchyId? child1, HierarchyId? child2)	Obtiene el valor de un nodo descendiente mayor que child1 y menor que child2.
GetLevel()	Obtiene el nivel de este nodo en el árbol jerárquico.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)	Obtiene un valor que representa la ubicación de un nuevo nodo con una ruta de acceso desde newRoot igual a la ruta de acceso desde oldRoot hasta este, con lo que, de hecho, se mueve este a la nueva ubicación.
IsDescendantOf(HierarchyId? parent)	Obtiene un valor que indica si este nodo es descendiente de parent.

Además, se pueden usar los operadores ==, !=, <, <=, > y >=.

A continuación se muestran ejemplos de uso de estos métodos en consultas LINQ.

Obtención de entidades en un nivel determinado en el árbol

La consulta siguiente usa GetLevel para devolver todos los medianos de un nivel determinado en el árbol genealógico:

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

Esto se traduce en el siguiente código SQL:

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

Al ejecutar esto en bucle, podemos obtener los medianos de cada generación:

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

Obtención del antecesor directo de una entidad

En la consulta siguiente se usa GetAncestor para buscar el antecesor directo de un mediano mediante el nombre de ese mediano:

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

Esto se traduce en el siguiente código 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)

La ejecución de esta consulta para el mediano "Bilbo" devuelve "Bungo".

Obtención de los descendientes directos de una entidad

La consulta siguiente también usa GetAncestor, pero esta vez para buscar los descendientes directos de un mediano mediante el nombre de ese mediano:

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

Esto se traduce en el siguiente código 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)

La ejecución de esta consulta para el mediano "Mungo" devuelve "Bungo", "Belba", "Longo" y "Linda".

Obtención de todos los antecesores de una entidad

GetAncestor es útil para buscar o bajar un único nivel o, de hecho, un número especificado de niveles. Por otro lado, IsDescendantOf es útil para encontrar todos los antepasados o dependientes. Por ejemplo, la consulta siguiente usa IsDescendantOf para buscar todos los antepasados de un mediano mediante el nombre de ese mediano:

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

Importante

IsDescendantOf devuelve true para sí mismo, por lo que se filtra en la consulta anterior.

Esto se traduce en el siguiente código 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

La ejecución de esta consulta para el mediano "Bilbo" devuelve "Bungo", "Mungo" y "Balbo".

Obtención de todos los descendientes de una entidad

La consulta siguiente también usa IsDescendantOf, pero esta vez para buscar todos los descendientes de un mediano mediante el nombre de ese mediano:

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

Esto se traduce en el siguiente código 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()

La ejecución de esta consulta para el mediano "Mungo" devuelve "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" y "Poppy".

Búsqueda de un antepasado común

Una de las preguntas más comunes que se formulan sobre este árbol genealógico en particular es: "¿quién es el antepasado común de Frodo y Bilbo?" Podemos usar IsDescendantOf para escribir una consulta de este 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();

Esto se traduce en el siguiente código 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

La ejecución de esta consulta con "Bilbo" y "Frodo" nos dice que su antepasado común es "Balbo".

Actualización de las jerarquías

Para actualizar las columnas hierarchyid se pueden usar los mecanismos normales de control de cambios y SaveChanges.

Reorganización de la relación de una subjerarquía

Por ejemplo, estoy seguro de que todos recordamos el escándalo de SR 1752 (también conocido como "LongoGate"), cuando las pruebas de ADN revelaron que Longo no era de hecho el hijo de Mungo, sino de Ponto. Un efecto colateral de este escándalo fue que había que reescribir el árbol genealógico. En particular, debía cambiarse la jerarquía en la relación de Longo y todos sus descendientes, de Mungo a Ponto. Para ello, se puede usar GetReparentedValue. Por ejemplo, primero se consulta "Longo" y todos sus descendientes:

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

A continuación, se usa GetReparentedValue para actualizar HierarchyId para Longo y cada descendiente, seguido de una llamada a SaveChangesAsync:

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

await context.SaveChangesAsync();

El resultado es la siguiente actualización en la base de datos:

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;

Con estos parámetros:

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

Nota

Los valores de los parámetros de las propiedades HierarchyId se envían a la base de datos en su formato binario compacto.

Después de la actualización, la consulta de los descendientes de "Mungo" devuelve "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" y "Poppy", mientras que la consulta de los descendientes de "Ponto" devuelve "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" y "Angelica".

Asignaciones de funciones

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

Recursos adicionales

• Datos jerárquicos en SQL Server