C# mysql树结构表设计，及递归返回目录树

第一部分：表设计

及初始化一条根目录，必须有且仅有唯一根节点

DROP TABLE IF EXISTS `doc_manager_dir`;
CREATE TABLE `doc_manager_dir`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键标识',
  `parent_id` int(11) NULL DEFAULT NULL COMMENT '父级标识',
  `dir_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '目录名',
  `dir_path` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '目录路径',
  `sort` int(11) NULL DEFAULT NULL COMMENT '排序号',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `creator` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '创建人',
  `memo` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '说明',
  `is_deleted` int(1) NOT NULL COMMENT ' 0未删除 1已删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `doc_manager_dir`(`id`, `parent_id`, `dir_name`, `dir_path`, `sort`, `create_time`, `creator`, `memo`, `is_deleted`) VALUES (1, NULL, '其他交付物', '', NULL, '2021-05-11 14:55:42', '徐维', '根节点必须唯一存在,且父节点为null', 0);

第二部分：定义目录结构DTO及递归返回目录树结构

  //初始化的根节点pId为0的数据
        public async Task<DirNodeInfo> GetRootTree() 
        {
            List<DirInfo> all = await GetAll();
            DirNodeInfo newNode = new DirNodeInfo();
            all.FindAll(x => x.PId == 0).ForEach(item =>
            {
                if (item.PId == 0)
                {
                    newNode.Id = item.Id;
                    newNode.PId = item.PId;
                    newNode.Name = item.Name;
                    newNode.Childs = GetChild(newNode, all);
                }
            });
            return newNode;
        }

        //递归遍历所有子节点
        public List< DirNodeInfo> GetChild( DirNodeInfo  dirNodeInfo, List<DirInfo> all)
        {
            List<DirInfo> nodes = all.FindAll(x => x.PId == dirNodeInfo.Id);
            List< DirNodeInfo> child = new List< DirNodeInfo>();
            nodes.ForEach(node =>
            {
                 DirNodeInfo n = new  DirNodeInfo()
                {
                    Id = node.Id,
                    PId = node.PId,
                    Name = node.Name,
                };
                child.Add(n);
                if (all.FindAll(x => x.PId == n.Id).Count != 0)
                    n.Childs = GetChild(n,all);
            });
            return child;
        }

public async Task<List<DirInfo>> GetAll()
        {
            string sql = @"select dir.id, dir.parent_id,dir.dir_name from `doc_manager_dir` dir where dir.is_deleted = 0 order by create_time desc";
            return await tntContext.CPQuery.Create(sql).ToListAsync<DirInfo>();
        }

  public class DirInfo
    {
		[DbColumn(Alias = "id")]
		public virtual int Id { get; set; }

		[DbColumn(Alias = "parent_id")]
		public virtual int PId { get; set; }

		[DbColumn(Alias = "dir_name")]
		public virtual string Name { get; set; }

		[DbColumn(Alias = "childs")]
		public virtual string Ids { get; set; }
	}

	public class DirNodeInfo
	{
		public virtual int Id { get; set; }

		public virtual int PId { get; set; }

		public virtual string Name { get; set; }

		public virtual List<DirNodeInfo> Childs { get; set; }
	}