DataTable collection to single table

[Zzazaya]

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.

 

[jmcilhinney]

There's nothing built into the DataSet to do that. You could write code to do it manually, which could probably be condensed into a LINQ query. You could start with two tables and get that working, then add one table at a time until they are all involved.

 

[cjard]

he parent table has 1401 rows so the resulting table should also have 1401 rows.

Only if you have one child record per parent record. And if you have that, why are there even separate tables at all?

That said, if you have relations and child rows you can just create more columns in the parent and set their Expression property to retrieve the child data. Note, you must use an aggregate, and as it's likely you have multiple children, you must also name the relation. For example if you have Department and Employee (child of department, but there is only ever one employee per department, and your relation is called FK_Department_Employees) then your parent table has e.g. a column called EmployeeName and its Expression is Max(Child(FK_Department_Employees).Name))