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

Статья
08/25/2023

Примечание.

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

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

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

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

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

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

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

Совет

Тип 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 Core
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; }
}

Совет

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

Совет

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

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

Halfling family tree

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

Балбо находится в корне дерева, представленного /.
Балбо имеет пять детей, представленных /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();

Совет

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

Запросы иерархий

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

Способ	Описание
`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", "Белба", "Longo" и "Линда".

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

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

Важно!

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 (a.k.a. "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)

Примечание.

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

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

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

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

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

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

Share via

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

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

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

Запросы иерархий

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

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

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

Обратная связь

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