C# Merge two datatable with different scheme and return hierarchical json

Question

I have a DataTable which is dynamic and can have 5-10 columns. In the below example I am only showing 4 columns.

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);

This datatable is guaranteed have to Name column and other columns may or may not appear.

I have a mapping DataTable which also has Name column (which can be used to join two datatables) & it also has two more columns which establishes relation between rows.

 DataTable mappingTable = new DataTable();
            mappingTable.Columns.Add("Name");
            mappingTable.Columns.Add("id");
            mappingTable.Columns.Add("parentID");


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

Currently I am returning JSON from DataTable by using:

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

and it produces following output:

[
  {
    "name": "A",
    "america": "200",
    "japan": "100",
    "singapore": "300"
  },
  {
    "name": "B",
    "america": "300",
    "japan": "300",
    "singapore": "600"
  },
  {
    "name": "C",
    "america": "400",
    "japan": "400",
    "singapore": "700"
  },
  {
    "name": "D",
    "america": "500",
    "japan": "500",
    "singapore": "800"
  },
  {
    "name": "E",
    "america": "600",
    "japan": "600",
    "singapore": "900"
  },
  {
    "name": "F",
    "america": "700",
    "japan": "700",
    "singapore": "1000"
  },
  {
    "name": "G",
    "america": "800",
    "japan": "800",
    "singapore": "600"
  },
  {
    "name": "H",
    "america": "900",
    "japan": "100",
    "singapore": "400"
  },
  {
    "name": "I",
    "america": "100",
    "japan": "200",
    "singapore": "300"
  },
  {
    "name": "J",
    "america": "200",
    "japan": "300",
    "singapore": "200"
  },
  {
    "name": "K",
    "america": "300",
    "japan": "500",
    "singapore": "500"
  }
]

But instead of returning this kind of JSON, I need to use Mapping DataTable and establish relation between rows and return a hierarchical JSON which looks like this:

[{
        "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"
            }
        ]
    }, {
        "name": "J",
        "america": "200",
        "japan": "300",
        "singapore": "200",
        "children": [{
                "name": "K",
                "america": "300",
                "japan": "500",
                "singapore": "500"
            }
        ]
    }
]

The structure of DataTable can change as I may get more or less number of columns. How can I produce the required JSON output ?

Answer 1

I'm not sure if there is an elegant way to do this using DataTable relations. If you can't get away from using DataTables you can manually map the data into the structure you need. This assumes the ids in the mapping table always match up with the row numbers in the data table.

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(DBNull.Value))
    .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);