A way to draw tree hierarchy from the Adjacency Model + Nested Sets Model (MPTT)

Question

I have a .NET Core 2.2 application with SQL Server DB and I'm storing the hierarchy of the users using the Adjacency Model + Nested Sets Model. I'm using EF Core. My domain looks like this:

    public class MatrixPosition
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Guid? ParentId { get; set; }
        public int Left { get; set; }
        public int Right { get; set; }
        public int DepthLevel { get; set; }
}

The very top element has of course ParentId = null and DepthLevel = 0

I would like to show this structure as some tree structure graphics. Did anyone have some ideas or working examples of this?

When you say "show this structure" do you want a static image or are you trying to produce an interactive chart where users can expand/collapse/reorder/etc? And when you say "tree" do you mean a traditional org chart or the type of tree you might see in a file explorer using people's names instead of folders? — dazedandconfused, May 08 '19 at 17:11
@dazedandconfused By saying "show this structure" I only want a static image, only readonly. And by "tree" I mean a traditional org chart only. You know I only want something like this -> http://imrannazar.com/content/img/mptt-tree.png - or of course something similar and simple just to see the structure — XardasLord, May 08 '19 at 20:03
@dazedandconfused You meant about interactive chart where users can expand/collapse/reorder/etc. Did you have an opportunity to do it in the past? — XardasLord, May 20 '19 at 06:24

score 1 · Accepted Answer · answered May 08 '19 at 20:50

One approach I've used in the past is to move some of the heavy lifting to an external framework like Graphviz. You can have your app produce a text file that is processed by a utility called "dot" to produce a graphic. The pros are that it is open source, widely used, and cross-platform. Cons are that the syntax to get the chart looking exactly the way you want can be confusing. Here's an example I created using your schema.

First, I created a recursive query to return records and their parents. I populated it with names as though it were an org chart. You can find the fiddle here if you want to experiment, but here is the query...

WITH cteEEs AS (
  SELECT employees.Id, employees.ParentId, employees.Name, employees.[Left], employees.[Right] FROM employees WHERE employees.ParentId IS NULL
  UNION ALL 
  SELECT employees.Id, employees.ParentId, employees.Name, employees.[Left], employees.[Right] FROM employees JOIN cteEEs ON (employees.ParentId = cteEEs.Id)
)

SELECT * FROM cteEEs

By using the following code...

var nodeRelationships = new StringBuilder();
var nodeDetails = new StringBuilder();

foreach(var node in results)
{
    if(!string.IsNullOrEmpty(node.ParentId))
    {
        nodeRelationships.AppendLine($"\t\"{node.ParentId}\" -> \"{node.Id}\"");
    }

    nodeDetails.AppendLine($"\t\"{node.Id}\" [label=\"{node.Name}\"]");
}

using(StreamWriter sw = new StreamWriter(@"c:\temp\test.dot"))
{
    sw.WriteLine("digraph G {");
    sw.Write(nodeRelationships.ToString());
    sw.Write(nodeDetails.ToString());
    sw.WriteLine("}");
}

... I can produce the following text file...

digraph G {
    "bec9439f-d4fb-4ee5-9d69-c33f54f91124" -> "79b5be71-0e01-4d0c-9640-ddfd98634b96"
    "bec9439f-d4fb-4ee5-9d69-c33f54f91124" -> "a5718293-1f93-4450-ad92-a89458d5600e"
    "a5718293-1f93-4450-ad92-a89458d5600e" -> "eaf07cbc-fd5c-4cc5-829e-166ff20b2ef9"
    "a5718293-1f93-4450-ad92-a89458d5600e" -> "7b4dbc24-6ab6-4585-bca3-95888c713f3c"
    "bec9439f-d4fb-4ee5-9d69-c33f54f91124" [label="Doe, Jane"]
    "79b5be71-0e01-4d0c-9640-ddfd98634b96" [label="Smith, Jim"]
    "a5718293-1f93-4450-ad92-a89458d5600e" [label="Jones, Bill"]
    "eaf07cbc-fd5c-4cc5-829e-166ff20b2ef9" [label="Adams, Sherri"]
    "7b4dbc24-6ab6-4585-bca3-95888c713f3c" [label="Walker, Dan"]
}

Running the following command

dot -Tpng test.dot -o test.png

takes my text file (test.dot) as input and produces test.png as output. The output looks like this...

If you look at the reference for the dot language you'll find that this is a bare-bones example, there are lots of things you can do to influence the appearance of the result.

So, your application could produce the appropriate text and use dot behind the scenes to create the image which your app then displays.

I will try it tonight, but thank you very much for your very detailed answer! :) Looks great! — XardasLord, May 09 '19 at 05:05

A way to draw tree hierarchy from the Adjacency Model + Nested Sets Model (MPTT)

1 Answers1