Create tree structure from a database query

Question

I have a database query that results in the following table:

How to turn this query into a tree structure like the one below in c#?

tree = [{
text = cliente1,
items = {[
    text = cojunto1,
    items =[{
        text=area1,
        items =[{
            text = maquina1},
            {
            text = maquina2},
            {
            text = maquina3
            ]}
        ]}
    },{
    text = cojunto1,
    items =[{
        text=area1,
        items =[{
            text = maquina1},
            {
            text = maquina2},
            {
            text = maquina3
            ]}
        ]}
    }]
]}

Answer 1

class Program
{
    static void Main(string[] args)
    {
        var rows = Tabela.GetData();
        var nodes = Node.GetClientes(rows);

        Console.ReadLine();
    }

    public class Node
    {
        public string Text { get; set; }
        public List<Node> Items { get; set; }

        public static List<Node> GetClientes(List<Tabela> rows)
        {
            List<Node> nodes = new List<Node>();

            foreach (var cliente in rows.Select(a => a.Cliente).Distinct())
            {
                nodes.Add(new Node { Text = cliente, Items = GetConjuntos(cliente, rows) });
            }

            return nodes;
        }

        public static List<Node> GetConjuntos(string cliente, List<Tabela> rows)
        {
            List<Node> nodes = new List<Node>();

            foreach (var row in rows)
            {
                if(row.Cliente == cliente)
                {
                    var conjunto = row.Conjunto;
                    if(!nodes.Any(a => a.Text == conjunto))
                    {
                        nodes.Add(new Node { Text = conjunto, Items = GetAreas(conjunto, rows) });
                    }
                }
            }

            return nodes;
        }

        public static List<Node> GetAreas(string conjunto, List<Tabela> rows)
        {
            List<Node> nodes = new List<Node>();

            foreach (var row in rows)
            {
                if (row.Conjunto == conjunto)
                {
                    var area = row.Area;
                    if (!nodes.Any(a => a.Text == area))
                    {
                        nodes.Add(new Node { Text = area, Items = GetMaquinas(area, rows) });
                    }
                }
            }

            return nodes;
        }

        public static List<Node> GetMaquinas(string area, List<Tabela> rows)
        {
            List<Node> nodes = new List<Node>();

            foreach (var row in rows)
            {
                if (row.Area == area)
                {
                    var maquina = row.Maquina;
                    if (!nodes.Any(a => a.Text == maquina))
                    {
                        nodes.Add(new Node { Text = maquina });
                    }
                }
            }

            return nodes;
        }

    }

    public class Tabela
    {
        public int Id { get; set; }
        public int IdMaquina { get; set; }
        public string Cliente { get; set; }
        public string Conjunto { get; set; }
        public string Area { get; set; }
        public string Maquina { get; set; }

        public static List<Tabela> GetData()
        {
            return new List<Tabela>
            {
                new Tabela { Id = 65, IdMaquina = 74, Cliente = "cliente1", Conjunto = "conjunto1", Area = "area1", Maquina = "maquina1" },
                new Tabela { Id = 65, IdMaquina = 75, Cliente = "cliente1", Conjunto = "conjunto1", Area = "area1", Maquina = "maquina2" },
                new Tabela { Id = 65, IdMaquina = 73, Cliente = "cliente1", Conjunto = "conjunto1", Area = "area1", Maquina = "maquina3" },
                new Tabela { Id = 65, IdMaquina = 71, Cliente = "cliente1", Conjunto = "conjunto2", Area = "area2", Maquina = "maquina1" },
                new Tabela { Id = 65, IdMaquina = 72, Cliente = "cliente1", Conjunto = "conjunto2", Area = "area2", Maquina = "maquina2" },
                new Tabela { Id = 65, IdMaquina = 70, Cliente = "cliente1", Conjunto = "conjunto2", Area = "area2", Maquina = "maquina3" },
            };
        }
    }
}

The Table class is a reflection of your table (properties representing columns) with a method that returns the same data as your query.

The Node class has two properties: Text and Items. The first defines the text and the second its items, exactly the structure you expect as a result. Class methods define the nodes construction rule.

In the Program class, in the Main method, I execute the two main methods:

var rows = Tabela.GetData();
var nodes = Node.GetClientes(rows);

The first returns the data (equal to the ones you need) and the second transforms them into nodes.