[大数据]SQL Server 使用 Hierarchyid 操作层次结构数据

SQL Server 使用 Hierarchyid 操作层次结构数据 - asdyzh - 博客园

sqlserver：数据类型Hierarchyid的介绍和用法_火焰-CSDN博客

使用EF Core操作层次结构数据

以前我们存储层次结构常用Id+ParentId的方式，例如：

这种方式查询效率不高，比如查询分公司1下的所有小组，必须使用递归。

针对这个问题，如果你是使用Sql Server，可以尝试一下HierarchyId。

HierarchyId

HierarchyId是一种长度可变的Sql Server数据类型，它能存储带有层次结构的数据。

HierarchyId数据类型的值可以直接表示树层次结构中的位置，例如：

HierarchyId可以使用下列函数:

• GetAncestor?：取得第n个祖先

• GetDescendant?：取得第n个子节点

• GetLevel?：取得级别

• GetRoot?：取得根

• Parse?：将字符串转换为HierarchyId

• ToString?：将HierarchyId转换为字符串，与parse正好相反

比如，查询分公司1下的所有小组，可以使用下列语句：

select?*?from?t?where?[Id].GetLevel()?=?3?AND?[Id].GetAncestor(2)?=?'/1/'

HierarchyId数据类型详情请参看官方文档：https://docs.microsoft.com/zh-cn/sql/relational-databases/hierarchical-data-sql-server?view=sql-server-ver15

代码示例

下面，我们通过一个示例，演示如何使用Entity Framework Core操作HierarchyId数据类型。

建表

执行下列Sql，在数据库中建表：

create?table?Organizations(
?Id?hierarchyid?primary?key,
?Name?nvarchar(50)
);?

创建项目

创建控制台应用程序，然后引用nuget包EntityFrameworkCore.SqlServer.HierarchyId。

定义数据模型

新建Organization.cs，代码如下：

public?class?Organization
{
????public?HierarchyId?Id??{?get;?set;?}
????public?string?Name?{?get;?set;?}
}

注意，Id的类型是HierarchyId。

新建DemoContext.cs，代码如下：

public?class?DemoContext?:?DbContext
{
????public?DbSet<Organization>?Organizations?{?get;?set;?}

????protected?override?void?OnConfiguring(DbContextOptionsBuilder?optionsBuilder)
????{
????????string?connectionString?=?"...";
????????optionsBuilder.UseSqlServer(connectionString,?config?=>?config.UseHierarchyId());
????}
}

使用config.UseHierarchyId()开启HierarchyId映射。

增删改查

现在，我们可以对HierarchyId数据类型进行操作了。

代码如下：

//增
using?(var?db?=?new?DemoContext())
{
????db.Organizations.AddRange(
????????????new?Organization?{?Id=?HierarchyId.Parse("/"),?Name=?"总公司"?}
????????????,new?Organization?{?Id?=?HierarchyId.Parse("/1/"),?Name?=?"分公司1"?}
????????????,new?Organization?{?Id?=?HierarchyId.Parse("/2/"),?Name?=?"分公司2"?}
????????????,?new?Organization?{?Id?=?HierarchyId.Parse("/1/1/"),?Name?=?"部门A"?}
????????????,?new?Organization?{?Id?=?HierarchyId.Parse("/1/1/1/"),?Name?=?"小组X"?}
????????????,?new?Organization?{?Id?=?HierarchyId.Parse("/1/1/2/"),?Name?=?"小组Y"?}
????????);

????db.SaveChanges();
}

//删除分公司2
using?(var?db?=?new?DemoContext())
{
????db.Organizations.Remove(db.Organizations.Where(p?=>?p.Id?==?HierarchyId.Parse("/2/")).First());
????db.SaveChanges();
}

//修改小组名称
using?(var?db?=?new?DemoContext())
{
????var?team?=?db.Organizations.Where(p?=>?p.Id?==?HierarchyId.Parse("/1/1/1/")).First();
????team.Name?=?"Team1";

????team?=?db.Organizations.Where(p?=>?p.Id?==?HierarchyId.Parse("/1/1/2/")).First();
????team.Name?=?"Team2";

????db.SaveChanges();
}

//查询分公司1下的所有小组
using?(var?db?=?new?DemoContext())
{
????var?organizations=??db.Organizations.Where(p?=>?p.Id.GetLevel()==3?
????????&&?p.Id.GetAncestor(2)==?HierarchyId.Parse("/1/"))
????????.OrderBy(p=>p.Id).ToList();
????
????foreach?(var?organization?in?organizations)
????{
????????Console.WriteLine(@$"{organization.Id}?{organization.Name}");
????}
}

运行成功：

?