Иерархические данные в поставщике SQL Server EF Core

Note

Эта функция была добавлена в EF Core 8.0.

Sql Azure и SQL Server имеют специальный тип hierarchyid данных, который используется для хранения иерархических данных. В этом случае "иерархические данные" по сути означают данные, формирующие структуру дерева, где каждый элемент может иметь родительский элемент и/или дочерние элементы. Примерами таких данных являются:

Организационная структура
Файловая система
группа задач в проекте;
Классификация языковых терминов
Диаграмма связей между веб-страницами

Затем база данных сможет выполнять запросы к этим данным с помощью иерархической структуры. Например, запрос может найти предков и зависимых от заданных элементов или найти все элементы в определенной глубине иерархии.

Использование HierarchyId в .NET и EF Core

На самом низком уровне пакет NuGet Microsoft.SqlServer.Types включает тип SqlHierarchyId. Хотя этот тип поддерживает рабочие значения иерархии, это немного сложно для работы с LINQ.

На следующем уровне появился новый пакет Microsoft.EntityFrameworkCore.SqlServer.Abstractions , который включает более высокий HierarchyId тип, предназначенный для использования в типах сущностей.

Tip

Тип HierarchyId является более идиоматичным для норм .NET, в отличие от SqlHierarchyId, который смоделирован по принципу того, как типы .NET Framework размещаются в ядре СУБД SQL Server. HierarchyId предназначен для работы с EF Core, но его также можно использовать за пределами EF Core в других приложениях. Пакет Microsoft.EntityFrameworkCore.SqlServer.Abstractions не ссылается на другие пакеты и поэтому имеет минимальное влияние на развернутый размер приложения и зависимости.

HierarchyId Для использования функций EF Core, таких как запросы и обновления, требуется пакет Microsoft.EntityFrameworkCore.SqlServer.HierarchyId. Этот пакет включает Microsoft.EntityFrameworkCore.SqlServer.Abstractions и Microsoft.SqlServer.Types как транзитивные зависимости, поэтому это часто единственный необходимый пакет.

Интерфейс командной строки .NET
Visual Studio

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

"Сервис" > "Диспетчер пакетов NuGet" > "Консоль диспетчера пакетов".

Выполните следующие команды:

Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Совет. Вы также можете установить пакеты, щелкнув проект правой кнопкой мыши и выбрав " Управление пакетами NuGet"

После установки пакета использование HierarchyId включается путем вызова UseHierarchyId в рамках вызова UseSqlServer приложения. Рассмотрим пример.

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

Иерархии моделирования

Тип HierarchyId можно использовать для свойств типа сущности. Например, предположим, что мы хотим смоделировать родословное дерево по отцовской линии некоторых вымышленных хоббитов. В типе сущности для Halfling свойство HierarchyId можно использовать для поиска каждого полурослика в семействе.

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

Tip

Код, показанный здесь, и в приведенных ниже примерах происходит из HierarchyIdSample.cs.

Tip

При необходимости HierarchyId подходит для использования в качестве типа свойства ключа.

В этом случае семейное дерево начинается с патриарха семьи. Каждого халфлинга можно проследить от патриарха вниз по дереву, используя его PathFromPatriarch свойство. SQL Server использует компактный двоичный формат для этих путей, но при работе с кодом обычно выполняется синтаксический анализ удобочитаемого строкового представления. В этом представлении позиция на каждом уровне разделена символом / . Например, рассмотрим дерево семьи на схеме ниже:

Генеалогическое древо халфлингов

В этом дереве:

Балбо находится в корне дерева, представленного /.
Балбо имеет пять детей, представленных /1/, /2/, /3/, /4/и /5/.
Первый ребенок Балбо, Мунго, также имеет пять детей, представленных /1/1/, , /1/2/, /1/3//1/4/и /1/5/. Обратите внимание, что HierarchyId, относящийся к Мунго (/1/), является префиксом для всех его детей.
Аналогичным образом третий ребенок Балбо, Понто, имеет двух детей, представленных /3/1/ и /3/2/. Снова каждый из этих потомков получает префикс HierarchyId для Ponto, который представлен как /3/.
И так далее вниз по дереву...

Следующий код вставляет это дерево семейства в базу данных с помощью 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();

Tip

При необходимости десятичные значения можно использовать для создания новых узлов между двумя существующими узлами. Например, /3/2.5/2/ идет между /3/2/2/ и /3/3/2/.

Запросы в иерархиях

HierarchyId предоставляет несколько методов, которые можно использовать в запросах LINQ.

Method	Description
`GetAncestor(int n)`	Возвращает узел `n` на заданное количество уровней вверх по иерархическому дереву.
`GetDescendant(HierarchyId? child1, HierarchyId? child2)`	Возвращает значение узла-потомка, которое больше, чем `child1`, и меньше, чем `child2`.
`GetLevel()`	Возвращает уровень этого узла в иерархическом дереве.
`GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)`	Получает значение, отражающее местоположение нового узла, имеющего путь от `newRoot`, который совпадает с путем от `oldRoot` до данного узла, эффективно перемещая его в новое местоположение.
`IsDescendantOf(HierarchyId? parent)`	Возвращает значение, указывающее, является ли этот узел потомком `parent`.

Кроме того, операторы ==, !=, <<=> и >= можно использовать.

Ниже приведены примеры использования этих методов в запросах LINQ.

Получение сущностей на заданном уровне в дереве

Следующий запрос с использованием GetLevel возвращает всех полуросликов на заданном уровне в семейном дереве:

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

Это преобразуется в следующий SQL:

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

Запустив это в цикле, мы можем получить полуросликов для каждого поколения.

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

Получение прямого предка сущности

Следующий запрос использует GetAncestor для поиска прямого предка полурослика по его имени:

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

Это преобразуется в следующий 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)

Выполнение этого запроса для полурослика "Bilbo" возвращает "Bungo".

Получите прямых потомков сущности

Следующий запрос также использует GetAncestor, но на этот раз для поиска прямых потомков полурослика, учитывая имя этого полурослика.

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

Это преобразуется в следующий 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)

Выполнение этого запроса для полурослика "Mungo" возвращает "Bungo", "Белба", "Лонго" и "Линда".

Получите всех предков сущности

GetAncestor полезен для поиска на один уровень вверх или вниз, или на указанное количество уровней. С другой стороны, IsDescendantOf полезен для нахождения всех предков или зависимых элементов. Например, следующий запрос использует IsDescendantOf для поиска всех предков полурослика на основе его имени.

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 возвращает значение true для себя, поэтому оно отфильтровывается в приведенном выше запросе.

Это преобразуется в следующий 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

Выполнение этого запроса для полузащиты "Bilbo" возвращает "Bungo", "Mungo" и "Balbo".

Получите всех потомков сущности

Следующий запрос также использует IsDescendantOf, но на этот раз для всех потомков полурослика, учитывая имя этого полурослика.

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

Это преобразуется в следующий 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()

Выполнение этого запроса для полурослика "Mungo" возвращает "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" и "Poppy".

Поиск общего предка

Одним из наиболее распространенных вопросов, задаваемых об этом конкретном дереве семьи, является "кто общий предок Фродо и Бильбо?" Мы можем использовать IsDescendantOf для написания такого запроса:

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

Это преобразуется в следующий 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

Выполнение этого запроса с "Bilbo" и "Frodo" показывает нам, что их общий предок — "Balbo".

Обновление иерархий

Обычные механизмы отслеживания изменений и SaveChanges можно использовать для обновления hierarchyid столбцов.

Повторное создание родительского элемента в под иерархии

Например, я уверен, что мы все помним скандал SR 1752 (также известный как "LongoGate"), когда тестирование ДНК показало, что Лонго не являлся сыном Мунго, а был сыном Понто! Одним из последствий этого скандала было то, что семейное дерево должно быть переписано. В частности, Лонго и все его потомки должны быть переназначены от Мунго к Понто. GetReparentedValue это можно использовать для этого. Например, сначала запрашиваются "Longo" и все его потомки:

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

Затем GetReparentedValue используется для обновления HierarchyId для Longo и каждого его потомка, после чего вызывается SaveChangesAsync:

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

await context.SaveChangesAsync();

Это приводит к следующему обновлению базы данных:

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;

Использование следующих параметров:

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

Note

Значения параметров для HierarchyId свойств отправляются в базу данных в их компактном двоичном формате.

После обновления запрос потомков "Мунго" возвращает "Бунго", "Белба", "Линда", "Бинго", "Бильбо", "Фалько" и "Поппи", при запросе потомков "Понто" возвращается "Лонго", "Роза", "Поло", "Ото", "Поско", "Приска", "Лото", "Понто", "Порто", "Пиони" и "Анжелика".

Сопоставления функций

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

Дополнительные ресурсы

Иерархические данные в SQL Server

Кері байланыс

Бет пайдалы болды ма?

Last updated on 2026-03-27

Иерархические данные в поставщике SQL Server EF Core

Использование HierarchyId в .NET и EF Core

Иерархии моделирования

Запросы в иерархиях

Обновление иерархий

Сопоставления функций

Дополнительные ресурсы

Кері байланыс

Қосымша ресурстар