c# dynamic linq query at runtime on datset

Question

I need your help for a small problem. I would like to make the following LINQ query dynamic (at runtime) because I don´t know how many tables there will be in the database (the tablenames are known while runtime).

var query = from row1 in ds.Tables["tab1"].AsEnumerable()
        from row2 in ds.Tables["tab2"].AsEnumerable()
        from row3 in ds.Tables["tab3"].AsEnumerable()
        select new { row1, row2, row3 };

Is this possible at generate a dynamic LINQ query in this case and write the result to a datatable or array?

Thanks in advance!

So you want a cartesian product of all tables rows? – Tim Schmelter Aug 27 '15 at 11:29 — Tim Schmelter, Aug 27 '15 at 11:29
Yes Tim that´s what I want – derchrome Aug 27 '15 at 11:29 — derchrome, Aug 27 '15 at 11:29

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

This extension method can create a cartesian product of all tables rows in a DataSet.

public static class Extensions
{
    public static IEnumerable<IEnumerable<T>> CartesianProduct<T>(this IEnumerable<IEnumerable<T>> sequences)
    {
        IEnumerable<IEnumerable<T>> emptyProduct = new[] { Enumerable.Empty<T>() };
        return sequences.Aggregate(
            emptyProduct,
            (accumulator, sequence) =>
                from accseq in accumulator
                from item in sequence
                select accseq.Concat(new[] { item })
            );
    } 
}

Now you can use it in this way:

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

Output:

foreach (var x in allTablesRows)
{
    foreach (DataRow row in x)
    {
        Console.WriteLine("table:{0} fields:{1}", 
            row.Table.TableName, 
            string.Join(",", row.ItemArray));
    }
}

edited May 23 '17 at 10:26

Community

1
1

answered Aug 27 '15 at 11:51

Tim Schmelter

450,073
74
686
939

Impressive! THANK YOU VERY MUCH TIM! – derchrome Aug 27 '15 at 12:13
Just one more question: I had a small error in reasoning :-) Every table has an ID as the first column. I only need to multiply those rows/tables which have the same ID at the beginning... Is it possible to add a "where" statement? – derchrome Aug 27 '15 at 14:07
Maybe it would help to provide a small example n your question. Or - since the question changes - ask another. Do you want to join the rows with the same ID or do you want a cartesian product of all rows where the id appears in another table? It's getting more and more unclear – Tim Schmelter Aug 27 '15 at 14:15
I do not need to build a cartesian product of all rows that was my missunderstanding, sorry! I need to have all rows joined on the same id and this must be done for all id´s. I hope it´s getting clearer now. – derchrome Aug 27 '15 at 14:21
Maybe it is possible to add a where condition (parameter) to the "CartesianProduct) Method? – derchrome Aug 27 '15 at 14:28
@derchrome: ask another question with meaningful sample data. It's still not clear what you want as result or what should happen if one ID appears in one table, another in two tables and a third ID appears in five tables. Must an ID appear in all tables? Is the column-name always the same, what's the typeof the column. As i've said, ask a different questions and provide a meaningful sample, a desired result and something that you've tried. Then people are willing to help. I'm currently busy, but you could add a link to that question here if you want. – Tim Schmelter Aug 27 '15 at 15:31
Good morning Tim, thank you very much for your time. The new question can be found here: http://stackoverflow.com/questions/32263948/dynamic-linq-where-query-with-joining-other-tables – derchrome Aug 28 '15 at 05:34

c# dynamic linq query at runtime on datset

1 Answers1

Linked