C# 递归构造树状数据结构(泛型),如何构造?如何查询?
十年河东,十年河西,莫欺少年穷。
学无止境,精益求精
难得有清闲的一上午,索性写篇博客。
首先,我们需要准备一张表,如下范例:
create table TreeTable ( TreeId varchar(100) not null primary key, NodeName nvarchar(50),--名称 FatherTreeId varchar(100),--父亲Id )
为了节约时间,我直接沿用项目中的数据,作如下批量插入:
insert into TreeTable(TreeId,NodeName,FatherTreeId) select [SpClassifyId],[ClassifyName],[ParentClassifyId] from [dbo].[SM_SupplierClassify] where [ForefatherClassifyName] in('工程类','精装修类','设计类','营销类')
插入的数据,如下所示:
从上图,我们可以看出,这些数据中有四种大类,分别为:('工程类','精装修类','设计类','营销类'),层级关系通过FatherTreeId构造,当然,此表中的层次不仅仅只有两级,理论上支持N级,类似这种结构的数据现实生活中有很多,如:家谱,人员组织架构,营销模式(微信的吸粉,病毒式营销),甚至传销模式等等
那么,我们如何将上述数据转变为树状结构的数据类型呢?
大家都会想到递归,但如何递归呢?
两种方法,
一、如何构造:
1、递归式查询,从根节点入手,查询多次(效率非常低,在此不作介绍)
2、一次性查询出所有数据,根据FatherTreeId,递归式查询泛型(效率高。只查询一次即可)我们介绍此种方法:
首先,我们构造如下Model
/// <summary> /// 我们要构造的树 /// </summary> public class TreeModel { public TreeModel() { children = new List<TreeModel>(); } public string title { get; set; } public string value { get; set; } public string key { get; set; } public List<TreeModel> children { get; set; } public static TreeModel MakeTree(TreeTableModel supplierClassifyModels) { TreeModel Tree = new TreeModel(); Tree.title = supplierClassifyModels.NodeName; Tree.key = supplierClassifyModels.TreeId; Tree.value = supplierClassifyModels.TreeId; if (supplierClassifyModels.children != null) { foreach (var item in supplierClassifyModels.children) { Tree.children.Add(MakeTree(item)); } } return Tree; } } /// <summary> /// 数据库表对应的实体Model /// </summary> public class TreeTableModel { public TreeTableModel() { children = new List<TreeTableModel>(); } public string TreeId { get; set; } public string NodeName { get; set; } public string FatherTreeId { get; set; } //子数据 public List<TreeTableModel> children { get; set; } }
上述的TreeModel中的 MakeTree 方法用于递归构造树!~_~ 重点
构造方法如下:
/// <summary> /// 构造树 /// </summary> /// <returns></returns> public static BaseResponse<List<TreeModel>> BuildTree() { using (AnFuDBEntities context = new AnFuDBEntities()) { List<TreeModel> TreeList = new List<TreeModel>(); var data = context.TreeTable.ToList(); var list = new List<TreeTableModel>(); list = Mapper.DynamicMap<List<TreeTableModel>>(data); List<TreeTableModel> TreeModels = BuildTreeModel(list, string.Empty); foreach (var item in TreeModels) { var Tree = TreeModel.MakeTree(item); TreeList.Add(Tree); } var result = TreeList; return CommonBaseResponse.SetResponse<List<TreeModel>>(result, true); } } /// <summary> /// 递归方法,构造树状泛型 /// </summary> /// <param name="AllList"></param> /// <param name="ParentClassifyId"></param> /// <returns></returns> private static List<TreeTableModel> BuildTreeModel(List<TreeTableModel> AllList, string ParentClassifyId) { List<TreeTableModel> List = new List<TreeTableModel>(); if (!string.IsNullOrEmpty(ParentClassifyId)) { List = AllList.Where(A => A.FatherTreeId == ParentClassifyId).OrderBy(A => A.NodeName).ToList(); // } else { List = AllList.Where(A => string.IsNullOrEmpty(A.FatherTreeId)).ToList(); } if (List != null) { foreach (var item in List) { item.children = BuildTreeModel(AllList,item.TreeId); if (item.children.Count == 0) { item.children = null; } } } return List.ToList(); }
通过上述代码,我们构造了如下数据,工程类(拥有8个子类)->工程类子类中的安装类(拥有11个子类)->......等等,多层次关系!
截止到这儿,树的构造就完成了!
二、如何查询:
如果直接查询数据表,SQL支持的递归查询我们可以用With....As..关键字来进行查询,具体查询语法请参考我的博客:SqlServer共用表达式(CTE)With As 处理递归查询
在此,仅做简单范例,如下:
1、查询子分类,要求带出该子分类的所有上级元素
WITH CTE_Tree(TreeId,NodeName,FatherTreeId ) AS (SELECT TreeId,NodeName,FatherTreeId FROM dbo.TreeTable WHERE NodeName = '电梯工程' UNION ALL SELECT o.TreeId,o.NodeName,o.FatherTreeId FROM dbo.TreeTable o INNER JOIN CTE_Tree oo ON o.TreeId=oo.FatherTreeId) SELECT distinct * FROM CTE_Tree
查询结果如下:
说明:查询NodeName为电梯工程的分类,带出了电梯工程的所有上级关系!
2、查询上级元素,要求带出所有下级元素
WITH CTE_Tree(TreeId,NodeName,FatherTreeId ) AS (SELECT TreeId,NodeName,FatherTreeId FROM dbo.TreeTable WHERE NodeName = '安装类' UNION ALL SELECT o.TreeId,o.NodeName,o.FatherTreeId FROM dbo.TreeTable o INNER JOIN CTE_Tree oo ON o.FatherTreeId=oo.TreeId) SELECT distinct * FROM CTE_Tree order by NodeName
查询结果如下:
安装类及11个子类全部查询出来!
如果我们不使用SQL的... with.... as.....语句,要查询出父节点及该节点下的所有子节点,该怎么查询呢?
通过递归List<T>查询方法如下:
/// <summary> /// 根据父节点ID 查询该节点及该节点下的所有子节点 /// </summary> /// <param name="FatherNodeId"></param> /// <returns></returns> public static List<TreeTableModel> QueryTreeByFatherId(string FatherNodeId) { using (AnFuDBEntities context = new AnFuDBEntities()) { List<TreeModel> TreeList = new List<TreeModel>(); var data = context.TreeTable.ToList(); var list = new List<TreeTableModel>(); list = Mapper.DynamicMap<List<TreeTableModel>>(data); List<TreeTableModel> treeNodes = new List<TreeTableModel>(); //安装类 对应的Id : 650FEF5E-21E6-404A-B8F7-12E82548D432 new QueryTreeModel().GetTreeNodes(list, FatherNodeId, ref treeNodes); var ef = list.Where(A => A.TreeId == FatherNodeId).FirstOrDefault(); treeNodes.Add(ef);//为了和数据库 as with 递归查询一致,将此节点加入返回值中 return treeNodes; } } /// <summary> /// 根据父类节点,查询出所有子节点 /// </summary> /// <param name="list">数据源,非树状结构</param> /// <param name="TreeId">父节点Id</param> /// <param name="treeNodes">得到的子节点</param> public void GetTreeNodes(List<TreeTableModel> list, string TreeId, ref List<TreeTableModel> treeNodes) { if (list == null) return; List<TreeTableModel> sublist; if (!string.IsNullOrWhiteSpace(TreeId)) { sublist = list.Where(t => t.FatherTreeId == TreeId).ToList(); } else { sublist = list.Where(t => string.IsNullOrWhiteSpace(t.FatherTreeId)).ToList(); } if (!sublist.Any()) return; foreach (var item in sublist) { treeNodes.Add(new TreeTableModel() { TreeId=item.TreeId, FatherTreeId=item.FatherTreeId, NodeName=item.NodeName}); GetTreeNodes(list, item.TreeId, ref treeNodes); } }
执行上述待代码:
//安装类 对应的Id : 650FEF5E-21E6-404A-B8F7-12E82548D432 AnfuUser.QueryTreeByFatherId("650FEF5E-21E6-404A-B8F7-12E82548D432");
结果如下:
根据截图可知,我们查询到12条记录!
那么,我们执行SQL如下,看看结果是否一致,如下:
根据上下两个截图,我们得知,查询结果一致!
那么,如果我们要根据子节点,查询该节点及所有上级节点,怎么通过程序查询呢?
代码如下:
/// <summary> /// 根据子ID 获取自身节点及所有父节点 /// </summary> /// <param name="FatherNodeId"></param> /// <returns></returns> public static List<TreeTableModel> QueryTreeByChildId(string FatherNodeId) { using (AnFuDBEntities context = new AnFuDBEntities()) { List<TreeModel> TreeList = new List<TreeModel>(); var data = context.TreeTable.ToList(); var list = new List<TreeTableModel>(); list = Mapper.DynamicMap<List<TreeTableModel>>(data); List<TreeTableModel> treeNodes = new List<TreeTableModel>(); //安装类 对应的Id : 650FEF5E-21E6-404A-B8F7-12E82548D432 new QueryTreeModel().GetTreeNodesByChildId(list, FatherNodeId, ref treeNodes); var ef = list.Where(A => A.TreeId == FatherNodeId).FirstOrDefault(); treeNodes.Add(ef);//为了和数据库 as with 递归查询一致,将此节点加入返回值中 return treeNodes; } } /// <summary> /// 根据子节点,查询出所有父节点 /// </summary> /// <param name="list">数据源,非树状结构</param> /// <param name="TreeId">子节点Id</param> /// <param name="treeNodes">得到的子节点</param> public void GetTreeNodesByChildId(List<TreeTableModel> list, string TreeId, ref List<TreeTableModel> treeNodes) { if (list == null) return; var ef = list.Where(A => A.TreeId == TreeId).FirstOrDefault(); if (ef == null) return; List<TreeTableModel> sublist; if (!string.IsNullOrWhiteSpace(TreeId)) { var FatherTreeId = ef.FatherTreeId; sublist = list.Where(t => t.TreeId == FatherTreeId).ToList(); } else { sublist = list.Where(t => !string.IsNullOrWhiteSpace(t.FatherTreeId)).ToList(); } if (!sublist.Any()) return; foreach (var item in sublist) { treeNodes.Add(new TreeTableModel() { TreeId = item.TreeId, FatherTreeId = item.FatherTreeId, NodeName = item.NodeName }); GetTreeNodesByChildId(list, item.TreeId, ref treeNodes); } }
执行上述代码,我们查询电梯工程及它的所有上级节点,
AnfuUser.QueryTreeByChildId("75269B3A-0086-40A9-BE18-62820E662B69");
结果如下:
我们再通过SQL查询如下:
WITH CTE_Tree(TreeId,NodeName,FatherTreeId ) AS (SELECT TreeId,NodeName,FatherTreeId FROM dbo.TreeTable WHERE TreeId = '75269B3A-0086-40A9-BE18-62820E662B69' UNION ALL SELECT o.TreeId,o.NodeName,o.FatherTreeId FROM dbo.TreeTable o INNER JOIN CTE_Tree oo ON o.TreeId=oo.FatherTreeId) SELECT distinct * FROM CTE_Tree
结果如下:
比对二者查询结果是否一致呢?
@陈卧龙的博客