C# LINQ - Map a flat list of children and parents to a hierarchical list-CodePudding

I use this LINQ code for select a flat list of children and their parents from an SQL DB:

 (from c in _context.Children
  join cp in _context.ChildParents on c.Id equals cp.ChildId
  join p in _context.Parents on cp.ParentId equals p.Id
  select new ChildWithParentFlat()
     {
      ChildId = c.Id,
      ChildName = c.Name,
      ParentId = p.Id,
      ParentName = p.Name
     }
 ).ToList();

It brings up a list in this format:

[
{
    "ChildId": 1,
    "ParentId": 1,
    "ChildName": "FirstChild",
    "ParentName": "FirstParent"
},
{
    "ChildId": 1,
    "ParentId": 2,
    "ChildName": "FirstChild",
    "ParentName": "SecondParent"
},
{
    "ChildId": 2,
    "ParentId": 3,
    "ChildName": "SecondChild",
    "ParentName": "ThirdParent"
},
{
    "ChildId": 2,
    "ParentId": 4,
    "ChildName": "SecondChild",
    "ParentName": "FourthParent"
}
]

I want to convert this list to hierarchical list, so that each object will contain a child with its parents. something like this:

[
{
    "ChildId": 1,      
    "ChildName": "FirstChild",        
    "Parents": [
        {
            "ParentId": 1,
            "ParentName": "FirstParent"
        },
        {
            "ParentId": 2,
            "ParentName": "SecondParent"
        }
    ]
},
{
    "ChildId": 2,
    "Parents": [
        {
            "ChildName": "SecondChild",
            "ParentName": "ThirdParent",
        },
        {
            "ParentId": 4,
            "ParentName": "FourthParent"
        }
    ]
},

]

What is the best way to do this?

CodePudding user response：

I want to convert this list to hierarchical list

Use GroupBy:

var hyrarchical = flatList
   .GroupBy(x => (x.ChildId, x.ChildName))
   .Select(cg => (
       ChildId: cg.Key.ChildId, 
       ChildName: cg.Key.ChildName,
       Parents: cg.GroupBy(x => (x.ParentId, x.ParentName))
                  .Select(pg => (ParentId: pg.Key.ParentId, ParentName: pg.Key.ParentName))) 
   ));

You can add ToList where needed, you could also use a custom class instead of these tuples,