Unable to correctly transform data into hierarchy

Question

I have two DataTables. 1 containing the data and other contains the relationship information between rows. Code seems to be working fine expect some nodes are missing. I know the reason for issue but not sure how to resolve the issue.

DataTable with data:

DataTable dataTable = new DataTable();
dataTable.Columns.Add("Name");
dataTable.Columns.Add("America");
dataTable.Columns.Add("Japan");
dataTable.Columns.Add("Singapore");

dataTable.Rows.Add("A", 200, 100, 300);
dataTable.Rows.Add("B", 300, 300, 600);
dataTable.Rows.Add("C", 400, 400, 700);
dataTable.Rows.Add("D", 500, 500, 800);
dataTable.Rows.Add("E", 600, 600, 900);
dataTable.Rows.Add("F", 700, 700, 1000);
dataTable.Rows.Add("G", 800, 800, 600);
dataTable.Rows.Add("H", 900, 100, 400);
dataTable.Rows.Add("I", 100, 200, 300);
dataTable.Rows.Add("J", 200, 300, 200);
dataTable.Rows.Add("K", 300, 500, 500);
dataTable.Rows.Add("L", 200, 300, 200);
dataTable.Rows.Add("M", 300, 500, 500);

Mapping DataTable

DataTable mappingTable = new DataTable();
mappingTable.Columns.Add("Name", typeof(string));
mappingTable.Columns.Add("id", typeof(int));
mappingTable.Columns.Add("parentID", typeof(int));

mappingTable.Rows.Add("A", 1, 0);
mappingTable.Rows.Add("B", 2, 1);
mappingTable.Rows.Add("C", 3, 1);
mappingTable.Rows.Add("D", 4, 0);
mappingTable.Rows.Add("E", 5, 4);
mappingTable.Rows.Add("F", 6, 0);
mappingTable.Rows.Add("G", 7, 6);
mappingTable.Rows.Add("H", 8, 6);
mappingTable.Rows.Add("I", 9, 1);
mappingTable.Rows.Add("J", 10, 0);
mappingTable.Rows.Add("K", 11, 10);
mappingTable.Rows.Add("L", 12, 0);
mappingTable.Rows.Add("M", 13, 0);

Code for converion

var data = dataTable.Rows.Cast<DataRow>()
    .Select(r => dataTable.Columns.Cast<DataColumn>().ToDictionary(c => c.ColumnName, c => r[c.ColumnName]))
    .ToList();
ILookup<int, int> mapping;
mapping = mappingTable.Rows.Cast<DataRow>()
    .Where(r => !r["parentID"].Equals(0))
    .ToLookup(r => (int)r["parentID"], r => (int)r["id"]);

var output = new List<Dictionary<string, Object>>();
foreach (var group in mapping)
{
    data[@group.Key - 1].Add("children", @group.Select(c => data[c - 1]).ToList());
    output.Add(data[@group.Key - 1]);
}

var json = Newtonsoft.Json.JsonConvert.SerializeObject(output);

Missing nodes (L & M) The ones with parent id 0 as that group is already being created.

 {
        "Name": "L",
        "America": "200",
        "Japan": "300",
        "Singapore": "200"

    }, {
        "Name": "M",
        "America": "200",
        "Japan": "300",
        "Singapore": "200"

    }

Current output:

[{
        "Name": "A",
        "America": "200",
        "Japan": "100",
        "Singapore": "300",
        "children": [{
                "Name": "B",
                "America": "300",
                "Japan": "300",
                "Singapore": "600"
            }, {
                "Name": "C",
                "America": "400",
                "Japan": "400",
                "Singapore": "700"
            }, {
                "Name": "I",
                "America": "100",
                "Japan": "200",
                "Singapore": "300"
            }
        ]
    }, {
        "Name": "D",
        "America": "500",
        "Japan": "500",
        "Singapore": "800",
        "children": [{
                "Name": "E",
                "America": "600",
                "Japan": "600",
                "Singapore": "900"
            }
        ]
    }, {
        "Name": "F",
        "America": "700",
        "Japan": "700",
        "Singapore": "1000",
        "children": [{
                "Name": "G",
                "America": "800",
                "Japan": "800",
                "Singapore": "600"
            }, {
                "Name": "H",
                "America": "900",
                "Japan": "100",
                "Singapore": "400"
            }
        ]
    }
]

Expected output: Add the missing node

Answer 1

Personally I prefer creating a model first which makes it much easier to create a tree afterwards.

The model:

    public class DataModel
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int America { get; set; }
        public int Japan { get; set; }
        public int Singapore { get; set; }
        public IList<DataModel> Children { get; set; }

        public DataModel()
        {
            Children = new List<DataModel>();
        }
    }

    public class DataMapping
    {
        public string Name { get; set; }
        public int Id { get; set; }
        public int ParentId { get; set; }
    }

Mapping the data:

        var mappings = mappingTable.Rows.Cast<DataRow>()
            .Select(
                r => new DataMapping
                {
                    Name = r.Field<string>("Name"),
                    Id = r.Field<int>("id"),
                    ParentId = r.Field<int>("parentID"),
                }).ToList();

        var data = dataTable.Rows.Cast<DataRow>()
            .Select(
                r => new DataModel
                {
                    Name = r.Field<string>("Name"),
                    America = Convert.ToInt32(r.Field<string>("America")),
                    Japan = Convert.ToInt32(r.Field<string>("Japan")),
                    Singapore = Convert.ToInt32(r.Field<string>("Singapore")),
                    Id = mappings.Single(m => m.Name.Equals(r.Field<string>("Name"))).Id
                }).ToList();

Creating the tree:

        foreach (var mapping in mappings.Where(m => m.ParentId != 0))
        {
            var parent = data.Single(d => d.Id == mapping.ParentId);
            var child = data.Single(d => d.Id == mapping.Id);
            parent.Children.Add(child);
        }

The output:

        var json = Newtonsoft.Json.JsonConvert.SerializeObject(data);

Answer 2

Here is my change to loop over all parents and add their children, then add them to the output:

var data = dataTable.Rows.Cast<DataRow>()
                    .Select(r => dataTable.Columns.Cast<DataColumn>().ToDictionary(c => c.ColumnName, c => r[c.ColumnName]))
                    .ToList();

var mapping = mappingTable.Rows.Cast<DataRow>()
                          .Where(r => !r["parentID"].Equals(0))
                          .ToLookup(r => (int)r["parentID"], r => (int)r["id"]);

var output = new List<Dictionary<string, Object>>();
foreach (var parent in mappingTable.Rows.Cast<DataRow>().Where(r => r["parentID"].Equals(0))) {
    var parentID = (int)parent["id"];

    if (mapping.Contains(parentID))
        data[parentID-1].Add("children", mapping[parentID].Select(c => data[c-1]).ToList());
    output.Add(data[parentID-1]);
}