Compartilhar via

Dados hierárquicos no provedor SQL Server do EF Core

  • Artigo

Observação

Esse recurso foi adicionado no EF Core 8.0.

O SQL do Azure e o SQL Server têm um tipo de dados especial chamado hierarchyid, que é usado para armazenar dados hierárquicos. Nesse caso, "dados hierárquicos" significa essencialmente dados que formam uma estrutura de árvore, onde cada item pode ter um pai e/ou filhos. Exemplos desses dados são:

  • Uma estrutura organizacional
  • Um sistema de arquivos
  • Um conjunto de tarefas em um projeto
  • Uma taxonomia de termos de linguagem
  • Um gráfico de links entre páginas da Web

O banco de dados é então capaz de executar consultas nesses dados usando sua estrutura hierárquica. Por exemplo, uma consulta pode encontrar ancestrais e dependentes de determinados itens ou localizar todos os itens em uma determinada profundidade na hierarquia.

Uso de HierarchyId no .NET e no EF Core

No nível mais baixo, o pacote NuGet Microsoft.SqlServer.Types que inclui um tipo chamado SqlHierarchyId. Embora esse tipo seja compatível com valores hierárquicos, é um pouco complicado trabalhar com ele no LINQ.

No próximo nível, um novo pacote Microsoft.EntityFrameworkCore.SqlServer.Abstractions foi introduzido, que inclui um tipo HierarchyId de nível superior destinado ao uso em tipos de entidade.

Dica

O tipo HierarchyId é mais idiomático para as normas do .NET do que SqlHierarchyId, que é modelado de acordo com a forma como os tipos do .NET Framework são hospedados no mecanismo de banco de dados do SQL Server. HierarchyId foi projetado para funcionar com o EF Core, mas também pode ser usado fora do EF Core em outros aplicativos. O pacote Microsoft.EntityFrameworkCore.SqlServer.Abstractions não faz referência a nenhum outro pacote e, portanto, tem um impacto mínimo no tamanho e nas dependências do aplicativo implantado.

O uso da funcionalidade HierarchyId para EF Core, como consultas e atualizações, requer o pacote Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Este pacote traz Microsoft.EntityFrameworkCore.SqlServer.Abstractions e Microsoft.SqlServer.Types como dependências transitivas e, portanto, muitas vezes é o único pacote necessário.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Depois que o pacote é instalado, o uso do HierarchyId é habilitado chamando UseHierarchyId como parte da chamada do aplicativo para UseSqlServer. Por exemplo:

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

Hierarquias de modelagem

O tipo HierarchyId pode ser usado para propriedades de um tipo de entidade. Por exemplo, suponha que queremos modelar a árvore genealógica paterna de alguns halflings fictícios. No tipo de entidade para Halfling, uma propriedade HierarchyId pode ser usada para localizar cada halfling na árvore genealógica.

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

Dica

O código mostrado aqui e nos exemplos abaixo vem de HierarchyIdSample.cs.

Dica

Se desejar, HierarchyId é adequado para uso como um tipo de propriedade chave.

Neste caso, a árvore genealógica está enraizada com o patriarca da família. Cada halfling pode ser rastreado do patriarca até a árvore usando sua propriedade PathFromPatriarch. O SQL Server usa um formato binário compacto para esses caminhos, mas é comum analisar de e para uma representação de cadeia de caracteres legível por humanos ao trabalhar com código. Nessa representação, a posição em cada nível é separada por um caractere /. Por exemplo, considere a árvore genealógica no diagrama abaixo:

Árvore genealógica Halfling

Nesta árvore:

  • Balbo está na raiz da árvore, representada por /.
  • Balbo tem cinco filhos, representados por /1/, /2/, /3/, /4/ e /5/.
  • O primeiro filho de Balbo, Mungo, também tem cinco filhos, representados por /1/1/, /1/2/, /1/3/, /1/4/ e /1/5/. Observe que o HierarchyId para Mungo (/1/) é o prefixo para todos os seus filhos.
  • Da mesma forma, o terceiro filho de Balbo, Ponto, tem dois filhos, representados por /3/1/ e /3/2/. Novamente cada uma dessas crianças é prefixada pela HierarchyId para Ponto, que é representada como /3/.
  • E assim por diante...

O código a seguir insere essa árvore genealógica em um banco de dados usando o 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();

Dica

Se necessário, os valores decimais podem ser usados para criar novos nós entre dois nós existentes. Por exemplo, /3/2.5/2/ vai entre /3/2/2/ e /3/3/2/.

Consultando hierarquias

HierarchyId expõe vários métodos que podem ser usados em consultas LINQ.

Método Descrição
GetAncestor(int n) Obtém o nó n sobe de nível na árvore hierárquica.
GetDescendant(HierarchyId? child1, HierarchyId? child2) Obtém o valor de um nó descendente que é maior que child1 e menor que child2.
GetLevel() Obtém o nível desse nó na árvore hierárquica.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) Obtém um valor que representa o local de um novo nó que tem um caminho de newRoot igual ao caminho de oldRoot para este, movendo-o efetivamente para o novo local.
IsDescendantOf(HierarchyId? parent) Obtém um valor que indica se esse nó é um descendente de parent.

Além disso, os operadores ==, !=, <, <=, > e >= podem ser usados.

A seguir estão exemplos de como usar esses métodos em consultas LINQ.

Obtenha entidades em um determinado nível na árvore

A consulta a seguir usa GetLevel para retornar todos os halflings em um determinado nível na árvore genealógica:

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

Isso se traduz no seguinte SQL:

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

Executando isso em um loop, podemos obter os halflings para cada geração:

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

Obter o ancestral direto de uma entidade

A consulta a seguir usa GetAncestor para encontrar o ancestral direto de um halfling, dado o nome desse halfling:

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

Isso se traduz no seguinte 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)

A execução dessa consulta para o halfling "Bilbo" retorna "Bungo".

Obter os descendentes diretos de uma entidade

A consulta a seguir também usa GetAncestor, mas desta vez para encontrar os descendentes diretos de um halfling, dado o nome desse halfling:

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

Isso se traduz no seguinte 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)

Executando esta consulta para o halfling "Mungo" retorna "Bungo", "Belba", "Longo" e "Linda".

Obter todos os antepassados de uma entidade

GetAncestor é útil para pesquisar um único nível ou, de fato, um número especificado de níveis. Por outro lado, IsDescendantOf é útil para encontrar todos os antepassados ou dependentes. Por exemplo, a consulta a seguir usa IsDescendantOf para encontrar todos os ancestrais de um halfling, dado o nome desse 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());

Importante

IsDescendantOf retorna true para si mesmo, e é por isso que ele é filtrado na consulta acima.

Isso se traduz no seguinte 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

Executar esta consulta para o halfling "Bilbo" retorna "Bungo", "Mungo" e "Balbo".

Obter todos os descendentes de uma entidade

A consulta a seguir também usa IsDescendantOf, mas desta vez para todos os descendentes de um halfling, dado o nome desse 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());

Isso se traduz no seguinte 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()

Executando esta consulta para o halfling "Mungo" retorna "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" e "Poppy".

Encontrar um ancestral comum

Uma das perguntas mais comuns feitas sobre essa árvore genealógica em particular é: "quem é o ancestral comum de Frodo e Bilbo?" Podemos usar IsDescendantOf para escrever tal consulta:

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

Isso se traduz no seguinte 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

Executando esta consulta com "Bilbo" e "Frodo" nos diz que seu ancestral comum é "Balbo".

Atualização de hierarquias

Os mecanismos normais controle de alterações e SaveChanges podem ser usados para atualizar hierarchyid colunas.

Recriação de uma sub-hierarquia

Por exemplo, tenho certeza de que todos nós nos lembramos do escândalo da SR 1752 (também conhecida como. "LongoGate") quando o teste de DNA revelou que Longo não era de fato o filho de Mungo, mas na verdade o filho de Ponto! Uma consequência desse escândalo foi que a árvore genealógica precisava ser reescrita. Em particular, a hierarquia de Longo e todos os seus descendentes precisou de ser alterada de Mungo para Ponto. GetReparentedValue pode ser usado para fazer isso. Por exemplo, primeiro "Longo" e todos os seus descendentes são consultados:

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

Em seguida, GetReparentedValue é usado para atualizar o HierarchyId para Longo e cada descendente, seguido de uma chamada para SaveChangesAsync:

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

await context.SaveChangesAsync();

Isso resulta na seguinte atualização do banco de dados:

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;

Usando esses 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)

Observação

Os valores de parâmetros para propriedades HierarchyId são enviados para o banco de dados em seu formato binário compacto.

Após a atualização, a consulta aos descendentes de "Mungo" retorna "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" e "Poppy", enquanto a consulta aos descendentes de "Ponto" retorna "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" e "Angelica".

Mapeamentos de função

.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 adicionais