CODEWITHSUNDEEP
c#sql-server.net-coretreenested-set-model
XardasLord
XardasLord

Reputation: 1937

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

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?

Upvotes: 1

Views: 754

Answers (1)

dazedandconfused
dazedandconfused

Reputation: 3186

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...

Sample output

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.

Upvotes: 1

Related Questions