Retrieve relational data using linq

Question

I am developing rest API in .net core and dapper ORM framework. My issue is I am trying to retrieve hierarchical menu structure in from database table using linq and pass from API response. problem is Menu table has 3 levels of menus but only two levels are shown in output, someone please guide me how I can fetch/add/retrieve third level of child menus and return in response instead of null

I have two tables like below:

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; }
}

Handler code:

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

Below code actually makes n_level tree. Try this one:

.//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);