十年河东，十年河西，莫欺少年穷 学无止境，精益求精 实际开发过程中，我们会遇到很多拥有上下级关系的事物，比如：组织架构，系统的左侧菜单等，数据库设计中，我们一般的解
十年河东，十年河西，莫欺少年穷
学无止境，精益求精
实际开发过程中，我们会遇到很多拥有上下级关系的事物，比如：组织架构，系统的左侧菜单等，数据库设计中，我们一般的解决方案就是把这些拥有上下级关系的表结构设计为：ID Name Sort ParentId Description等字段。
上述字段的含义相信小伙伴都知道对应的含义【ID主键，名称，排序，父亲ID，描述】
那么针对这样的数据结构，我们怎么构造树状数据呢？
下面以一个具体的示例探讨，假设我们有如下一张表：
public partial class FranchiseeDept
{
public FranchiseeDept()
{
FranchiseeRole = new HashSet<FranchiseeRole>();
FranchiseeUseraccountDept = new HashSet<FranchiseeUseraccountDept>();
FranchiseeUseraccountRole = new HashSet<FranchiseeUseraccountRole>();
}
public string DeptId { get; set; }
public string FranchiseeId { get; set; }
public string DeptName { get; set; }
public string DeptParentId { get; set; }
public string MenuButtonSystem { get; set; }
public string DeptParentName { get; set; }
public string DeptDescription { get; set; }
public int? DeptSort { get; set; }
public string DeptIcon { get; set; }
public string DeptType { get; set; }
public DateTime? CreateTime { get; set; }
public int DeleteFlag { get; set; }
public virtual ICollection<FranchiseeRole> FranchiseeRole { get; set; }
public virtual ICollection<FranchiseeUseraccountDept> FranchiseeUseraccountDept { get; set; }
public virtual ICollection<FranchiseeUseraccountRole> FranchiseeUseraccountRole { get; set; }
}
View Code
用的是EF模式展示的，相信都能看懂。
1、在接口层定义一个接口，如下：
/// <summary>
/// 获取组织架构树
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
BaseResponse<List<DeptTreeModel>> GetDeptTreeList(SeachDeptModel model);
2、实现层方法如下：
/// <summary>
/// 查询列表树
/// </summary>
/// <returns></returns>
public BaseResponse<List<DeptTreeModel>> GetDeptTreeList(SeachDeptModel model)
{
List<DeptTreeModel> TreeList = new List<DeptTreeModel>();
var searchResult = _context.FranchiseeDept.Where(A => A.DeleteFlag == 0).Select(A => new DeptTreeDataModel
{
DeptId = A.DeptId,
DeptName = A.DeptName,
DeptParentId = A.DeptParentId,
DeptParentName = A.DeptParentName,
DeptDescription = A.DeptDescription,
FranchiseeId=A.FranchiseeId,
DeptType = A.DeptType
});
if (!string.IsNullOrEmpty(model.isJisMs) &&model.isJisMs == "N")
{
searchResult = searchResult.Where(A => A.DeptType == Enum_DeptType.LeYx_Dept.ToString());
}
if (!string.IsNullOrEmpty(model.isJisMs) && model.isJisMs == "Y")
{
//新增时如果是加盟商，则只显示一级组织 供加盟商选择父亲组织
searchResult = searchResult.Where(A => string.IsNullOrEmpty(A.DeptParentId));
}
if (!string.IsNullOrEmpty(model.FranchiseeId))
{
searchResult = searchResult.Where(A => A.FranchiseeId == model.FranchiseeId || string.IsNullOrEmpty(A.DeptParentId));
}
var list = searchResult.ToList();
List<DeptTreeDataModel> TreeModels = DeptTreeModel.BuildTreeModel(list, string.Empty);
foreach (var item in TreeModels)
{
var Tree = DeptTreeModel.MakeTree(item, list);
TreeList.Add(Tree);
}
if (!string.IsNullOrEmpty(model.FranchiseeId))
{
if (TreeList.Count > 0)
{
var result = TreeList[0].children;
return CommonBaseResponse.SetResponse<List<DeptTreeModel>>(result, true);
}
else
{
return CommonBaseResponse.SetResponse<List<DeptTreeModel>>(null, true);
}
}
else
{
var result = TreeList;
return CommonBaseResponse.SetResponse<List<DeptTreeModel>>(result, true);
}
}
View Code
辅助方法如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace FranchiseeDto.Role
{
public class DeptTreeDataModel
{
public string DeptId { get; set; }
public string DeptName { get; set; }
public string DeptParentId { get; set; }
public string DeptParentName { get; set; }
public string DeptDescription { get; set; }
public string FranchiseeId { get; set; }
public string DeptType { get; set; }
public List<DeptTreeDataModel> children { get; set; } = new List<DeptTreeDataModel>();
}
public class DeptTreeModel
{
public DeptTreeModel()
{
children = new List<DeptTreeModel>();
}
public string DeptId { get; set; }
public string DeptName { get; set; }
public string DeptParentId { get; set; }
public string DeptParentName { get; set; }
public string DeptDescription { get; set; }
public string FranchiseeId { get; set; }
public string DeptType { get; set; }
public List<DeptTreeModel> children { get; set; }
public static DeptTreeModel MakeTree(DeptTreeDataModel model, List<DeptTreeDataModel> TreeList)
{
DeptTreeModel Tree = new DeptTreeModel();
Tree.DeptId = model.DeptId;
Tree.DeptName = model.DeptName;
Tree.DeptParentId = model.DeptParentId;
Tree.DeptParentName = model.DeptParentName;
Tree.DeptDescription = model.DeptDescription;
if (model.children != null)
{
foreach (var item in model.children)
{
Tree.children.Add(MakeTree(item, TreeList));
}
}
return Tree;
}
public static List<DeptTreeDataModel> BuildTreeModel(List<DeptTreeDataModel> AllList, string ParentClassifyId)
{
List<DeptTreeDataModel> List = new List<DeptTreeDataModel>();
if (!string.IsNullOrEmpty(ParentClassifyId))
{
List = AllList.Where(A => A.DeptParentId == ParentClassifyId).OrderBy(A => A.DeptName).ToList();
//
}
else
{
List = AllList.Where(A => string.IsNullOrEmpty(A.DeptParentId)).ToList();
}
if (List != null)
{
foreach (var item in List)
{
item.children = BuildTreeModel(AllList, item.DeptId);
if (item.children.Count == 0)
{
item.children = null;
}
}
}
return List.ToList();
}
}
}
View Code
除了上述根据根节点获取子节点，如果根据子节点获取父节点，方法如下：
public class TreeHelper
{
/// <summary>
/// 根据父类节点，查询出所有子节点
/// </summary>
/// <param name="list">数据源，非树状结构</param>
/// <param name="TreeId">父节点Id</param>
/// <param name="treeNodes">得到的子节点</param>
public static 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);
}
}
/// <summary>
/// 根据子节点，查询出所有父节点
/// </summary>
/// <param name="list">数据源，非树状结构</param>
/// <param name="TreeId">子节点Id</param>
/// <param name="treeNodes">得到的子节点</param>
public static 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);
}
}
/// <summary>
/// 递归方法，构造树状泛型
/// </summary>
/// <param name="AllList"></param>
/// <param name="ParentClassifyId"></param>
/// <returns></returns>
public 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();
}
}
以上便是树状的查询方法，适用于数据量不太大的表。
@陈卧龙的博客