How to convert HierarchyId to Child/Parent (Tree) structure in C# dotnet?

Question

For some reason such as performance, I have to use HiarachyId in my database. I have to convert the HierarchyId data type to JSON to show up in FancyTree.

I Use the solution here but won't work. My code was

static void Main(string[] args)
    {

        {

        var dd = new List<Field>();
        dd.Add(new Field(1, "Earth", HierarchyId.Parse("/")));
        dd.Add(new Field(2, "Europe", HierarchyId.Parse("/1/")));
        dd.Add(new Field(3, "South America", HierarchyId.Parse("/2/")));
        dd.Add(new Field(4, "Antarctica", HierarchyId.Parse("/3/")));
        dd.Add(new Field(5, "Brazil", HierarchyId.Parse("/2/1/")));
        dd.Add(new Field(6, "France", HierarchyId.Parse("/1/1/")));
        dd.Add(new Field(7, "Germany", HierarchyId.Parse("/1/4/")));
        dd.Add(new Field(8, "test", HierarchyId.Parse("/1/5/")));
        dd.Add(new Field(9, "McMurdo Station", HierarchyId.Parse("/3/1/")));
        dd.Add(new Field(10, "Italy", HierarchyId.Parse("/1/3/")));
        dd.Add(new Field(11, "Spain", HierarchyId.Parse("/1/2/")));
        dd.Add(new Field(12, "Morano", HierarchyId.Parse("/1/3/1/")));
        dd.Add(new Field(13, "Rio de Janeiro", HierarchyId.Parse("/2/1/3/")));
        dd.Add(new Field(14, "Paris", HierarchyId.Parse("/1/1/1/")));
        dd.Add(new Field(15, "Madrid", HierarchyId.Parse("/1/2/1/")));
        dd.Add(new Field(16, "Brasilia", HierarchyId.Parse("/2/1/1/")));
        dd.Add(new Field(17, "Bahia", HierarchyId.Parse("/2/1/2/")));
        dd.Add(new Field(18, "Salvador", HierarchyId.Parse("/2/1/2/1/")));
        dd.Add(new Field(19, "tets1", HierarchyId.Parse("/2/1/3/1/")));
        dd.Add(new Field(20, "test2", HierarchyId.Parse("/2/1/3/1/1/")));
        dd.Add(new Field(21, "test3", HierarchyId.Parse("/2/1/3/1/1/1/")));
        dd.Add(new Field(22, "test24", HierarchyId.Parse("/2/1/3/1/1/2/")));

            MyClass clss = new MyClass();
            var x=  clss.NewMthodTest(dd);

        }

    }

Method to get child:

     public class MyClass
{
    public List<HierarchicalNode> NewMthodTest(List<Field> query)
    {

        var root = new HierarchicalNode("Root", 0);
        foreach (var rec in query)
        {
            var current = root;
            foreach (string part in rec.Node.ToString().Split(new[] { '/' },
                                           StringSplitOptions.RemoveEmptyEntries))
            {
                int parsedPart = int.Parse(part);
                current = current.Children[parsedPart - 1];
            }
            current.Children.Add(new HierarchicalNode(rec.FieldName, rec.Id));
        }
        return null; // in this method i don't know what do we suppose to return 
    }
}

and my input parameter class is :

    public class Field 
    {
        public Field(long id, string fieldName, HierarchyId node)
        {
            Id = id;
            FieldName = fieldName;
            Node = node;
        }
        public long Id { get; set; }
        public string FieldName { get; set; }
        public HierarchyId Node { get; set; }
    }

and output class is

    class HierarchicalNode
{
    private readonly List<HierarchicalNode> children =
        new List<HierarchicalNode>();
    public List<HierarchicalNode> Children { get { return children; } }

    private readonly string name;
    public string Name { get { return name; } }

    private readonly long id;
    public long Id { get { return id; } }

    public HierarchicalNode(string name, long id)
    {
        this.name = name;
        this.id = id;
    }
}

it seems something wrong and it returns this:

Please read https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/ - at the moment this doesn't contain nearly enough information for us to help you. — Jon Skeet, May 29 '20 at 18:42
It's still not a [mcve]. You shouldn't need SQL, you should be able to provide a short but *complete* console app that has hard-coded data and processes it. — Jon Skeet, May 29 '20 at 18:59
Except it's still incomplete. There's no data, and you've provided a single method. I'm expecting something I can copy, paste, compile and run - just as described in that first link. Please read that carefully, and really take the time to make this a useful question. — Jon Skeet, May 30 '20 at 07:09
I change my post and know it seems you can simply copy and paste — Mohammad, May 30 '20 at 17:44
No, I really can't. It's nearly there, but not quite. You've provided several separate methods, but no main method. Try it yourself: create a brand new file, copy and paste your code, and try to run it, without making any other changes. You won't be able to. There's no Main method. Compare it with one of my questions: https://stackoverflow.com/questions/44060524 You can just copy, paste, compile (admittedly with the right dependencies) and run. No copying multiple separate bits of the code, no writing your own Main method. It's just there, ready to run. — Jon Skeet, May 30 '20 at 17:47
Additionally, you still haven't provided the expected output, so we wouldn't know whether an answer actually satisfied your needs. I'm afraid I've spent enough time trying to help you now - I can't keep on repeating myself and making painfully slow progress towards a real complete example. Maybe someone else will be willing to help you after you've edited again, but I'm not going to spend any more time on it myself. — Jon Skeet, May 30 '20 at 17:49
Please see my last chance. I think this is the last one and the best. Thank you for the nice following up and advice. — Mohammad, May 30 '20 at 17:57
I don't believe you've tried copying and pasting *just the code you've provided* into a blank file and then compiling it. (Hint: your `Main` method isn't in a class declaration, and there are no `using `directives anywhere. And *still* no sign of what you expect the result to be.) — Jon Skeet, May 30 '20 at 17:58

score 2 · Answer 1 · answered Apr 06 '21 at 01:29

One of the benefits of using HierarchyId is so that you can build a tree without doing recursive calls.

I would also name things a big differently. Let's say you call your database table Nodes. Here is the table structure:

CREATE TABLE dbo.Nodes
(
       [NodeId]          [int]            NOT NULL,
       [NodeName]        [nvarchar](100)  NOT NULL,
       [HierarchyId]     [hierarchyid]    NOT NULL,
       [Level]       AS [HierarchyId].GetLevel() PERSISTED,
       CONSTRAINT Primary_Key_Nodes PRIMARY KEY CLUSTERED ([NodeId]) 
);

CREATE UNIQUE NONCLUSTERED INDEX
     [Nodes_1]
ON
[dbo].[Nodes] ([HierarchyId] ASC);

 
--Important - put level as the first column to index
CREATE UNIQUE NONCLUSTERED INDEX
    [Nodes_2]
ON
[dbo].[Nodes] ([Level] ASC, [HierarchyId] ASC);

Here is the SQL to return nodes for a given parent. I would wrap this up in a function called GetDescendantsAndSelf():

SELECT
      [NodeId]
      ,[NodeName]
      ,[HierarchyId].ToString() AS 'HierarchyPath'
FROM
      [dbo].[Nodes]
WHERE
      [HierarchyId].IsDescendantOf(@parentHierarchyId) = 1
ORDER BY
    [Level] ASC
    ,[NodeName] ASC;

My data transfer object could look like this:

public class TreeNode
{
   public string Text { get; set; } = String.Empty;
   public List<TreeNode> Nodes { get; set; } = new List<TreeNode>();
}

GetDescendantsAndSelf() should return a list of Node data access objects like this one:

public class Node
{
    public int NodeId { get; set; }
    public string NodeName { get; set; } = String.Empty;
    public SqlHierarchyId HierarchyId { get; set; }
    public int Level => HierarchyId.GetLevel().ToSqlInt32().Value;
}

Here is the code to build a tree:

TreeNode? rootNode = null;

Dictionary<string, TreeNode> treeBuilder = new Dictionary<string, TreeNode>();
string parentHierarchyId = "/1/2/3";
var nodes = GetDescendantsAndSelf(parentHierarchyId);
foreach (Node node in nodes)
{
    TreeNode currentNode = new TreeNode() { Text = node.NodeName };
    treeBuilder[node.HierarchyId.ToString()] = currentNode;

    if (node.Level == 1)
    {
        rootNode = currentNode;
    }
    else
    {
        string parentKey = node.HierarchyId.GetAncestor(1).ToString();
        treeBuilder[parentKey].Nodes.Add(currentNode);
    }
}

if (rootNode is {})
{
     //rootNode contains your tree structure
}
else
{
     //no data found for parentHierarchyId
}

I like your non-recursive approach! – Caveman74 Jan 23 '23 at 17:10 — Caveman74, Jan 23 '23 at 17:10

Mohammad · Accepted Answer · 2020-05-30T20:19:26.697

After a lot of search On the net with no result, I solve this by myself with a recursive method in c#.

I put my total code here to help people who search in the future for this question. If someone has any nice advice to make it better please leave a note and make me happy.

This is my code:

This is My Main method which calls GetTreeMethod

  public List<TreeView> GetTree()
    {
       //get all nodes from DB to make a collection in RAM
        var nodesColect = _getFieldsList.GetFieldsByNode("/");

        var x = GetTreeMethod("/", nodesColect);

        return x;
    }

This is my main recursive method

private List<TreeView> GetTreeMethod(string nodeStr,List<FieldListDto> lstCollection)
    {
        List<TreeView> lst = new List<TreeView>();
        HierarchyId node = HierarchyId.Parse(nodeStr);
        var lastItemInCurrentLevel = GetChilds(node, lstCollection);

        foreach (var item in lastItemInCurrentLevel)
        {
            TreeView tr = new TreeView
            {
                title = item.title,
                id = item.id,
                node = item.node,
                fin = item.fin,
            };
            tr.children = GetTreeMethod(item.node.ToString(), lstCollection);
            lst.Add(tr);
        }

        return lst;
    }

this just gives children of a specific node

   private List<TreeView> GetChilds(HierarchyId node, List<FieldListDto> lstCollection)
    {
        List<TreeView> child = lstCollection.Where(x => x.Node.ToString() != "/" && x.Node.GetAncestor(1).ToString() == node.ToString()).Select(q => new TreeView { id = q.Id, node = q.Node, title = q.FieldName }).ToList();
        return child;

    }

Models

public class FieldListDto  
{
    public long id { get; set; }
    public string FieldName { get; set; }
    public HierarchyId Node { get; set; }
}
 public class TreeView
{
    public long id { get; set; }
    public string title { get; set; }
    public HierarchyId node { get; set; }
    public List<TreeView> children { get; set; }
}

here my SQL data and here my final result

How to convert HierarchyId to Child/Parent (Tree) structure in C# dotnet?

2 Answers2

Linked