Dados hierárquicos no fornecedor SQL Server EF Core
Observação
Esse recurso foi adicionado ao 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. Neste caso, "dados hierárquicos" significa essencialmente dados que formam uma estrutura em árvore, onde cada item pode ter um pai e/ou filhos. São exemplos desses dados:
- Uma estrutura organizacional
- Um sistema de arquivos
- Um conjunto de tarefas em um projeto
- Uma taxonomia de termos linguísticos
- 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 antepassados e dependentes de determinados itens ou encontrar todos os itens com uma certa profundidade na hierarquia.
Usando o HierarchyId no .NET e o EF Core
No nível mais baixo, o pacote Microsoft.SqlServer.Types NuGet inclui um tipo chamado SqlHierarchyId. Embora este tipo ofereça suporte a valores de hierarchyid em uso, é um pouco complicado trabalhar com ele no LINQ.
No próximo nível, foi introduzido um novo pacote Microsoft.EntityFrameworkCore.SqlServer.Abstractions, que inclui um tipo de mais alto nível HierarchyId destinado ao uso em tipos de entidade.
Dica
O tipo HierarchyId é mais coerente com as normas do .NET do que SqlHierarchyId, que é modelado com base em como os tipos do .NET Framework são hospedados dentro do 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 de HierarchyId nas funcionalidades do 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.
- Interface de Linha de Comandos do .NET Core
- Visual Studio
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 umas fictícias halflings . No tipo de entidade Halfling, uma propriedade HierarchyId pode ser utilizada 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
Caso deseje, HierarchyId é adequado para uso como um tipo de propriedade de chave.
Neste caso, a árvore genealógica está enraizada no 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. Nesta representação, a posição em cada nível é separada por um caractere /. Por exemplo, considere a árvore genealógica no diagrama abaixo:
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 oHierarchyIdpara 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/. Mais uma vez, cada uma dessas crianças é prefixada peloHierarchyIdpara Ponto, que é representado como/3/. - E assim por diante na descida pela árvore...
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 a níveis acima na árvore hierárquica. |
GetDescendant(HierarchyId? child1, HierarchyId? child2) |
Obtém o valor de um nó descendente cujo valor é maior que child1 e menor que child2. |
GetLevel() |
Obtém o nível deste nó na árvore hierárquica. |
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) |
Obtém um valor que representa a localização de um novo nó que possui um caminho de newRoot, igual ao caminho de oldRoot até este, movendo-o efetivamente para a nova localização. |
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 uso desses métodos em consultas LINQ.
Obter entidades em um determinado nível na árvore
A consulta a seguir usa GetLevel para retornar todos os halflings a um nível específico 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)
Executar esta 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 para cima ou para baixo um único nível, ou, na verdade, 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 verdadeiro para si mesmo, motivo pelo qual é 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" resulta nos nomes "Bungo", "Mungo" e "Balbo".
Obter todos os descendentes de uma entidade
A seguinte consulta 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()
Ao executar esta consulta para o halfling "Mungo", obtém "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" e "Poppy".
Encontrar um ancestral comum
Uma das perguntas mais comuns feitas sobre esta á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
Executar esta consulta com "Bilbo" e "Frodo" indica-nos que o seu ancestral comum é "Balbo".
Atualização de hierarquias
Os normais de controle de alterações
Reorganização de uma sub-hierarquia
Por exemplo, tenho certeza de que todos nos lembramos do escândalo do SR 1752 (também conhecido como "LongoGate") quando testes de DNA revelaram que Longo não era de fato o filho de Mungo, mas na verdade o filho de Ponto! Uma das consequências desse escândalo foi que a árvore genealógica precisou ser reescrita. Em particular, Longo e todos os seus descendentes precisaram ser reeducados 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 por 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 estes 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 dos parâmetros para as propriedades HierarchyId são enviados para a base de dados em formato compacto e binário.
Após a atualização, a consulta para os descendentes de "Mungo" retorna "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" e "Poppy", enquanto a consulta para os descendentes de "Ponto" retorna "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" e "Angelica".
Mapeamentos de função
| .NET | SQL |
|---|---|
| hierarchyId.ObterAntepassado(n) | @hierarchyId.GetAncestor(@n) |
| hierarchyId.GetDescendant(filho) | @hierarchyId.GetDescendant(@child, NULL) |
| hierarchyId.GetDescendant(filho1, filho2) | @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(entrada) | hierarchyid::Parse(@input) |
| hierarchyId.ToString() | @hierarchyId.ToString() |
