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

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

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

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

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

Id	ParentId	Name
1	null	总公司
2	1	分公司1
3	1	分公司2
4	2	部门A
5	4	小组X
6	4	小组Y

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

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

HierarchyId

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

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

Id	Name
/	总公司
/1/	分公司1
/2/	分公司2
/1/1/	部门A
/1/1/1/	小组X
/1/1/2/	小组Y

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

运行成功：