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

Answer 2

Ok, I finally made it but it seems to be much too complicated. If someone is able to help me improve the solution he is very welcome.

DataSet dsResult = new DataSet();
        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<Int64>("ID"))
            .Select(grp => new { ID = grp.Key, Rows = grp });

        foreach (var idGroup in idRowGroups)
        {
            var liste = idGroup.Rows.ToList();
            for (int i = 0; i < liste.Count; i++)
            {
                if (!dsResult.Tables.Contains(liste[i].Table.TableName))
                {
                    dsResult.Tables.Add(liste[i].Table.TableName);
                    foreach (DataColumn dtCol in liste[i].Table.Columns)
                    {
                        if (dsResult.Tables[liste[i].Table.TableName].Columns.Contains("ID"))
                            dsResult.Tables[liste[i].Table.TableName].Columns.Add(dtCol.ColumnName+i.ToString());
                        else
                        {
                            dsResult.Tables[liste[i].Table.TableName].Columns.Add(dtCol.ColumnName);
                        }
                        dsResult.Tables[liste[i].Table.TableName].Columns[dtCol.ColumnName].DataType = dtCol.DataType;
                    }
                 }
                DataRow dRow = dsResult.Tables[liste[i].Table.TableName].NewRow();
                dRow.ItemArray = liste[i].ItemArray;
                dsResult.Tables[liste[i].Table.TableName].Rows.Add(dRow);
            }

            IEnumerable<IEnumerable<DataRow>> allTablesRows = dsResult.Tables.Cast<DataTable>()
            .Select(table => table.AsEnumerable())
            .CartesianProduct();

            int k = 0;

            foreach (var rows in allTablesRows)
            {
                DataRow zRow = dsErgebnis.Tables[2].NewRow();
                foreach (DataRow dRow in rows)
                {
                    for (int i = 0; i < dRow.ItemArray.Length; i++)
                    {
                        zRow[k] = dRow.ItemArray[i];
                        k++;
                    }
                }
                k = 0;
                dsErgebnis.Tables[2].Rows.Add(zRow);
            }
            dsResult.Clear();
       }

First I filter the content by ID.

Then I put the result in a new tables (all rows with the ID 10 I found in 'Datasatz2' in dataset 'ds' for example I put to a new table 'Datasatz2' in the dataset 'dsResult').

At least I build the cartesian product of all tables and store it in dtaset dsErgebnis.

The result is what I expect but as mentioned before I am not satisfied with the solution.