在业务开发中，树形结构的无限极查询是非常常见的需求，比如部门层级、商品分类、菜单权限等场景，对应的数据库表通常会设计成带有父节点ID的树形表结构。要实现这类表的无限极查询，CTE递归语句是高效的解决方案，结合C#的Dapper框架可以快速完成数据查询和实体映射。
一、树形表结构设计
首先我们需要设计基础的树形表，以部门表为例，表结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| Id | int | 主键ID |
| DeptName | nvarchar(50) | 部门名称 |
| ParentId | int | 父部门ID，顶级部门为0 |
对应的C#实体类定义如下：
public class Department
{
public int Id { get; set; }
public string DeptName { get; set; }
public int ParentId { get; set; }
// 子部门集合，用于存放递归查询后的层级数据
public List<Department> Children { get; set; } = new List<Department>();
}
二、CTE递归语句基础
CTE即公用表表达式，递归CTE包含两个部分：锚定成员和递归成员。锚定成员是递归的初始结果集，递归成员会不断引用CTE自身，直到满足终止条件。查询部门及其所有子部门的CTE语句示例如下：
-- 查询指定部门ID下的所有子部门（包含自身）
WITH DeptCTE AS (
-- 锚定成员：查询初始部门
SELECT Id, DeptName, ParentId
FROM Department
WHERE Id = @DeptId
UNION ALL
-- 递归成员：查询父ID等于CTE中ID的部门
SELECT d.Id, d.DeptName, d.ParentId
FROM Department d
INNER JOIN DeptCTE c ON d.ParentId = c.Id
)
SELECT * FROM DeptCTE
上面的语句中，首先通过锚定成员获取初始部门数据，然后递归成员不断关联查询父ID为已查询部门ID的子部门，直到没有更多子部门为止，最终返回所有层级的部门数据。
三、Dapper结合CTE实现无限极查询
Dapper是轻量级的ORM框架，支持直接执行SQL语句并映射结果到实体。我们首先需要在项目中引入Dapper包，然后通过以下步骤实现查询：
1. 基础查询获取扁平数据
首先执行CTE递归语句，获取所有层级的扁平部门数据：
using Dapper;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
public class DepartmentService
{
private readonly string _connectionString = "Server=.;Database=TestDB;Trusted_Connection=True;";
// 获取指定部门下的所有扁平子部门（包含自身）
public List<Department> GetFlatDepartments(int deptId)
{
using (var conn = new SqlConnection(_connectionString))
{
string sql = @"
WITH DeptCTE AS (
SELECT Id, DeptName, ParentId
FROM Department
WHERE Id = @DeptId
UNION ALL
SELECT d.Id, d.DeptName, d.ParentId
FROM Department d
INNER JOIN DeptCTE c ON d.ParentId = c.Id
)
SELECT Id, DeptName, ParentId FROM DeptCTE";
return conn.Query<Department>(sql, new { DeptId = deptId }).ToList();
}
}
}
2. 构建树形结构
获取到扁平数据后，我们需要将数据转换为树形结构，方便前端展示或后续业务处理：
public Department BuildDepartmentTree(int rootDeptId)
{
// 获取所有扁平数据
var flatList = GetFlatDepartments(rootDeptId);
if (flatList == null || flatList.Count == 0)
{
return null;
}
// 找到根节点
var rootNode = flatList.FirstOrDefault(d => d.Id == rootDeptId);
if (rootNode == null)
{
return null;
}
// 递归构建子节点
BuildChildren(rootNode, flatList);
return rootNode;
}
private void BuildChildren(Department parent, List<Department> flatList)
{
// 找到当前节点的所有直接子节点
var children = flatList.Where(d => d.ParentId == parent.Id).ToList();
if (children.Count == 0)
{
return;
}
parent.Children.AddRange(children);
// 递归构建每个子节点的子节点
foreach (var child in children)
{
BuildChildren(child, flatList);
}
}
四、使用示例
调用上述方法即可获取完整的树形部门数据：
class Program
{
static void Main(string[] args)
{
var service = new DepartmentService();
// 查询ID为1的部门及其所有子部门，构建树形结构
var deptTree = service.BuildDepartmentTree(1);
if (deptTree != null)
{
Console.WriteLine($"根部门：{deptTree.DeptName}");
PrintTree(deptTree, 1);
}
}
// 递归打印树形结构
static void PrintTree(Department dept, int level)
{
foreach (var child in dept.Children)
{
Console.WriteLine($"{new string(' ', level * 2)}├─ {child.DeptName}");
PrintTree(child, level + 1);
}
}
}
注意事项
- CTE递归语句默认有递归次数限制，SQL Server中默认最大递归次数是100，如果层级超过100需要在语句末尾添加OPTION (MAXRECURSION 0)来取消限制，0表示无限制。
- 如果只需要查询某个节点下的所有子节点不需要包含自身，可以调整锚定成员的逻辑，去掉初始节点的查询，直接从子节点开始关联。
- Dapper的Query方法会自动映射字段名和实体属性名，需要确保SQL查询的字段名和实体属性名一致，不一致时可以使用AS关键字重命名。