使用LINQ检索关系数据

Question

我正在开发.NET Core和Dapper ORM框架中的REST API。我的问题是，我正在尝试使用LINQ从数据库表中检索层次菜单结构，并通过API响应传递。 问题是菜单表具有 3级菜单，但在输出中只显示了两个级别，请指导我如何获取/添加/添加/检索第三级的儿童菜单和响应返回而不是null

我有两个表格，例如：

tbluicomponents：

Id       Name        ParentId
1        Home          0
2        About         0
3        ContactUs     0
4        Department    2
5        Achivements   2
6        CSE           4
7        CIVIL         4 
8        ETC           4
9        MECH          4

tblroleui：

id     role     uiid
1      admin     1
2      admin     2
3      admin     3
4      admin     4
5      admin     5
6      admin     6
7      admin     7
8      admin     8
9      admin     9

public class Demo
{
  public List<Menus> Demos{ get; set; }
}

public class Menus
{
 public int Id { get; set; }
 public string Name { get; set; }
 public int ParentId { get; set; 
 public List<Menus> ChildMenus { get; set; }
}

处理程序代码：

IQueryable data = await UIControlRepository.GetMenusByRoleAsync(request.Role);
            if (data == null)
                return new QueryResponse($"No data exists", false);
            List<Menu> menus = JsonConvert.DeserializeObject<List<Menu>>(JsonConvert.SerializeObject(data));
            Demo demos = new Demo();
            if (menus != null && menus.Count() > 0 && menus.Any(d => d.ParentId == 0)) {
                demos.Demos= new List<Menu>();
                
                demos..AddRange(menus.Where(d => d.ParentId == 0));
                if(demos.Demos != null && demos.Demos.Any())
                {
                    foreach (var menu in demos.Demos)
                    {
                        menu.ChildMenu = new List<Menu>(menus.Where(d => d.ParentId != 0 && d.ParentId == menu.Id));
                                            }


                }
            }
return new QueryResponse() { Data = demos, IsSuccessful = true };

API output:
"demos": [
      {
        "id": 1,
        "name": "Home"
        "parentId": 0,
        "ChildMenu": []
      },
      {
        "id": 2,
        "name": "About",
        "parentId": 0,
        "ChildMenu": [
{
            "id": 4,
            "name": "Department",
            "parentId": 2,
            "ChildMenu": null
          }
,{
            "id": 5,
            "name": "Achievements",
            "parentId": 2,
            "ChildMenu": null
          }]
      },
      {
        "id": 3,
        "name": "ContactUs",
        "parentId": 0,
        "ChildMenu": null
      }
]

Answer 1

下面的代码实际上是N_level树。尝试这个：

.//Fetch data and etc
.
.
    Demo demos = new Demo();

    #region Local method 'FillResponseTree()'

    List<Menu> FillResponseTree(List<Menu> menus)
    {
        var node = menus.Select(x => new Menu()
        {
            Id = x.Id,
            ParentId = x.ParentId,
            Name = x.Name,
            ChildMenus = x.ChildMenus != null && x.ChildMenus.Any() ?
            FillResponseTree(x.ChildMenus).ToList()
            : null
        });
        return node.ToList();
    }
    
    #endregion
    
    demos.Demos = FillResponseTree(menus);