SQL Server EF Core 提供程序中的分层数据

Azure SQL 和 SQL Server 具有一个名为 hierarchyid 的特殊数据类型，用于存储分层数据。 在这种情况下，“分层数据”实质上是指形成树结构的数据，其中每个项都可以有父级和/或子级。 此类数据的示例包括：

• 组织结构
• 文件系统
• 项目中的一组任务
• 语言术语分类
• 网页间链接图

然后，数据库可以使用其分层结构对此数据运行查询。 例如，查询可以查找给定项的上级和依赖项，或查找层次结构中某个深度的所有项。

在 .NET 和 EF Core 中使用 HierarchyId

在最低级别，Microsoft.SqlServer.Types NuGet 包包含一个名为 SqlHierarchyId 的类型。 虽然此类型支持工作 hierarchyid 值，但在 LINQ 中使用它有点麻烦。

在下一个级别，引入了新的 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 包，其中包括用于实体类型的高级 HierarchyId 类型。

对查询和更新等 EF Core 功能使用 HierarchyId 需要 Microsoft.EntityFrameworkCore.SqlServer.HierarchyId 包。 此包将 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 和 Microsoft.SqlServer.Types 作为可传递依赖项引入，因此通常是唯一需要的包。

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

安装包后，通过调用 UseHierarchyId 作为应用程序对 UseSqlServer 的调用的一部分来使用 HierarchyId。 例如：

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

在这种情况下，家谱以家庭的家长为根。 可以使用 PathFromPatriarch 属性根据树下的家长跟踪每个半成年人。 SQL Server 对这些路径使用压缩的二进制格式，但在使用代码时，通常要分析到用户可读的字符串表示形式或从中分析。 在此表示形式中，每个级别的位置由 / 字符分隔。 例如，请考虑下图中的家谱：

在此树中：

• Balbo 位于树的根处，由 / 表示。
• Balbo 有五个孩子，由 /1/、/2/、/3/、/4/ 和 /5/ 表示。
• Balbo 的第一个孩子 Mungo 也有五个孩子，由 /1/1/、/1/2/、/1/3/、/1/4/ 和 /1/5/ 表示。 请注意，HierarchyId for Mungo (/1/) 是其所有子项的前缀。
• 同样，Balbo 的第三个孩子 Ponto 有两个孩子，由 /3/1/ 和 /3/2/ 表示。 同样，其中每个孩子都以 Ponto 的 HierarchyId 为前缀，表示为 /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();

查询层次结构

HierarchyId 公开可用于 LINQ 查询的多种方法。

此外，还可以使用运算符 ==、!=、<、<=、> 和 >=。

下面是在 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”、“Belba”、“Longo”和“Linda”。

获取实体的所有祖先

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

它转换为以下 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”。

查找共同祖先

关于此特殊家谱的最常见问题之一是，“谁是 Frodo 和 Bilbo 的共同祖先？”可以使用 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 列。

重新设置子层次结构的父级

例如，当 DNA 测试显示，Longo 实际上不是 Mungo 的儿子，而是 Ponto 的儿子时，我确定我们都记得 SR 1752（也称为“LongoGate”）的丑闻！ 这场丑闻的一个后果是，家谱需要重新编写。 特别是，Longo 及其所有后代的父系需要从 Mungo 调整为 Ponto。 GetReparentedValue 可用于执行此操作。 例如，查询了第一个“Longo”及其所有后代：

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

然后，GetReparentedValue 用于更新 Longo 和每个子代的 HierarchyId，接着调用 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)

更新后，查询“Mungo”的后代时将返回“Bungo”、“Belba”、“Linda”、“Bingo”、“Bilbo”、“Falco”、“Poppy”；而查询“Ponto”的后代时将返回“Longo”、“Rosa”、“Polo”、“Otho”、“Posco”、“Prisca”、“Lotho”、“Ponto”、“Porto”、“Peony”、“Angelica”。

函数映射

其他资源

• SQL Server 中的分层数据