Dynamic LINQ Where query with joining other tables

Question

I have several tables (the exact number is not known when the program is build) looking like this (the number of rows and columns may differ from table to table):

The source data is stored in a data set. Now I want to generate a new table where all data of all ids is stored (the picture shows only the result for id 10 and 20 but the target table should contain the data for all ids):

The equivalent SQLite statement for that looks like this:

SELECT * FROM Dataset 
JOIN Datensatz2 ON (Dataset.ID=Datensatz2.ID)
JOIN Datensatz3 ON (Datensatz3.ID=Dataset.ID)
JOIN Datensatz4 ON (Datensatz4.ID=Dataset.ID)
WHERE Dataset.Id=10
UNION
SELECT * FROM Dataset 
JOIN Datensatz2 ON (Dataset.ID=Datensatz2.ID)
JOIN Datensatz3 ON (Datensatz3.ID=Dataset.ID)
JOIN Datensatz4 ON (Datensatz4.ID=Dataset.ID)
WHERE Dataset.Id=20
...

The double id columns will be removed afterwards so don′t worry about that. The questions is now how to convert it as a dynamic LINQ query?

Answer 1

There are plenty of open question but maybe this helps to solve it. Since the tables are already stored in a DataSet you could use Linq-To-DataSet and Enumerable.GroupBy to group by ID:

var idTables = ds.Tables.Cast<DataTable>().Where(t => t.Columns.Contains("Id"));
if(!idTables.Any()){ MessageBox.Show("No id-tables"); return; }

var idRowGroups = idTables.SelectMany(t => t.AsEnumerable())
    .GroupBy(row => row.Field<int>("Id"))
    .Select(grp => new { ID = grp.Key, Rows = grp });

foreach(var idGroup in idRowGroups)
{
  Console.WriteLine("ID:{0} Rows:{1}"
      , idGroup.ID
      , String.Join(" | ", idGroup.Rows.Select(row => String.Join(",", row.ItemArray))));
}

Sample data:

var ds = new DataSet();
DataTable t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data", typeof(int));
t1.Rows.Add(1, 1);
t1.Rows.Add(2, 10);
t1.Rows.Add(3, 100);
t1.Rows.Add(4, 1000);
ds.Tables.Add(t1);
t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data", typeof(int));
t1.Rows.Add(4, 5);
t1.Rows.Add(5, 50);
t1.Rows.Add(7, 500);
t1.Rows.Add(3, 5997);
ds.Tables.Add(t1);
t1 = new DataTable();
t1.Columns.Add("Id", typeof(int));
t1.Columns.Add("Data1", typeof(int));
t1.Columns.Add("Data2", typeof(int));
t1.Rows.Add(1, 5, 0);
t1.Rows.Add(3, 7, 1);
t1.Rows.Add(5, 9, 11);
t1.Rows.Add(7, 11, 222);
ds.Tables.Add(t1);

Output:

ID:1 Rows:1,1 | 1,5,0
ID:2 Rows:2,10
ID:3 Rows:3,100 | 3,5997 | 3,7,1
ID:4 Rows:4,1000 | 4,5
ID:5 Rows:5,50 | 5,9,11
ID:7 Rows:7,500 | 7,11,222