Join datatables in a dataset into a single table

Question

Im trying to join multiple datatables to a single datatable.

var response = await client.ExecuteStaticQueryAsync(new ExecuteStaticQueryRequest() {
    queryName = "CustTableListPage"
});

var dataset = response.ExecuteStaticQueryResult.ToDataSet();

This gives me a dataset with 13 tables defined with relationships. Is it possible to join the tables into a single datatable using the existing relations?

I have tried the Merge() method, but this method seems to union the rows and creates a table with 9000 rows. The parent table has 1401 rows so the resulting table should also have 1401 rows.

What i tried:

var datatable = new DataTable("MergedCustTable");

for(int i = 0; i < 2; i++) 
    datatable.Merge(dataset.Tables[i]);

Here i tried to merge the first and second table. The first table is the parent. This results with a datatable with 2802 rows.

Answer 1

This has not been properly tested, but works on my dataset

public static class Extensions{
    public static DataTable Join(this DataTable table) {
        var columns = table.GetJoinedColumns();
        var newDt = new DataTable();
        newDt.Columns.AddRange(columns);

        foreach (DataRow row in table.Rows) {
            var newRow = newDt.NewRow();
            ProcessRows(row, newRow);
            newDt.Rows.Add(newRow);
        }

        return newDt;
    }

    private static DataColumn[] GetJoinedColumns(this DataTable table) {
        List<DataColumn> dataColumns = new List<DataColumn>();

        //Add columns from parent table
        foreach (DataColumn dc in table.Columns)
            dataColumns.Add(new DataColumn(dc.Table.TableName + "_" + dc.ColumnName, dc.DataType));

        //Add columns from from child tables
        foreach (DataRelation relation in table.ChildRelations)
            dataColumns.AddRange(GetJoinedColumns(relation.ChildTable));

        return dataColumns.ToArray();
    }

    private static void ProcessRows(DataRow currentRow, DataRow newRow) {
        //Add values from current row
        for (int i = 0; i < currentRow.ItemArray.Length; i++) {
            string columnName = currentRow.Table.Columns[i].ColumnName;
            string tableName = currentRow.Table.TableName;
            newRow[tableName + "_" + columnName] = currentRow.ItemArray[i];
        }

        //Add values from child rows
        foreach (DataRelation relation in currentRow.Table.ChildRelations) {
            var childRows = currentRow.GetChildRows(relation);
            foreach (DataRow childRow in childRows) 
                ProcessRows(childRow, newRow);
        }
    }
}

usage:

var dataset = response.ExecuteStaticQueryResult.ToDataSet();
var custTable = dataset.Tables[0];
DataTable dt = custTable.Join();