C# DataTable Inner join with dynamic columns

Question

I'm trying to join two DataTables together in a similar way to this question:

I'm trying to get the output to be a single 'combined' table, with columns from both of the original tables. They will both have a datestamp column in common.

The answer given is good for DataTables with fixed columns, but what if they are created dynamically, and can have any number of columns, how can I join them?

e.g.

T1 (datestamp, t1Column1, t1Column2, t1ColumnN...)
T2 (datestamp, t2Column1, t2Column2, t2ColumnN...)

I would like to join to create the following:

J1 (datestamp, t1Column1, t1Column2, t1ColumnN, ..., t2Column1, t2Column2, t2ColumnN...)

Is this possible?

Could you give an example of some data which would be the result you want out? — andres descalzo, May 08 '12 at 17:40
I don't think Linq is an option as you would need to know the column names you were trying to project into. — finoutlook, May 09 '12 at 11:43
ok, but could get me to do so, please can you give an example with two datatable and results you want. — andres descalzo, May 09 '12 at 11:52

score 9 · Accepted Answer · edited May 09 '12 at 23:18

I found a solution which doesn't rely on looping through the columns.

It uses the 'Merge' method, which I had previously dismissed as I thought both tables required the same structure.

First you need to create a primary key on the two data-tables:

// set primary key
T1.PrimaryKey = new DataColumn[] { T1.Columns["DateStamp"] };
T2.PrimaryKey = new DataColumn[] { T2.Columns["DateStamp"] };

Then add both tables to a data-set so a relationship can be added:

// add both data-tables to data-set
DataSet dsContainer = new DataSet();
dsContainer.Tables.Add(T1);
dsContainer.Tables.Add(T2);

Next add the relationship between the two key columns in the data-set:

// add a relationship between the two timestamp columns
DataRelation relDateStamp = new DataRelation("Date", new DataColumn[] { T1.Columns["DateStamp"] }, new DataColumn[] { T2.Columns["DateStamp"] });
dsContainer.Relations.Add(relDateStamp);

Finally you can now copy the first data-table into a new 'combined' version, and then merge in the second:

// populate combined data
DataTable dtCombined = new DataTable();
dtCombined = T1.Copy();
dtCombined.Merge(T2, false, MissingSchemaAction.Add);

Note: The Merge method requires the second argument to be false or else it copies the structure but not the data of the second table.

This would then combine the following tables:

T1 (2012-05-09, 111, 222)
T2 (2012-05-09, 333, 444, 555)

into a combined version based on the primary-key:

J1 (2012-05-09, 111, 222, 333, 444, 555)

mafue · Answer 2 · 2012-05-08T16:41:15.967

1

I think you can adapt the answer in the linked question to use the index of the column, rather than the column name. Or you could just loop through the items in each Row, like this:

foreach(DataRow row in table.Rows)
{
    foreach(DataColumn column in table.Columns)
    {
        object value = row[column]; // add this to your combined table
    }
}

edited May 08 '12 at 16:41

answered May 08 '12 at 16:34

mafue

1,858
1
21
27

Thanks - I was hoping to avoid looping through all the columns as I already have a few nested loops to set up the original tables. This could get quite complex if the data in the tables has lots of different dates/keys. – finoutlook May 09 '12 at 11:56

chkimes · Answer 3 · 2012-07-16T14:01:34.473

After getting tired of seeing all these inner join functions that don't reliably emulate SQL, I decided to make my own here:

private DataTable JoinDataTables(DataTable t1, DataTable t2, params Func<DataRow, DataRow, bool>[] joinOn)
{
    DataTable result = new DataTable();
    foreach (DataColumn col in t1.Columns)
    {
        if (result.Columns[col.ColumnName] == null)
            result.Columns.Add(col.ColumnName, col.DataType);
    }
    foreach (DataColumn col in t2.Columns)
    {
        if (result.Columns[col.ColumnName] == null)
            result.Columns.Add(col.ColumnName, col.DataType);
    }
    foreach (DataRow row1 in t1.Rows)
    {
        var joinRows = t2.AsEnumerable().Where(row2 =>
            {
                foreach (var parameter in joinOn)
                {
                    if (!parameter(row1, row2)) return false;
                }
                return true;
            });
        foreach (DataRow fromRow in joinRows)
        {
            DataRow insertRow = result.NewRow();
            foreach (DataColumn col1 in t1.Columns)
            {
                insertRow[col1.ColumnName] = row1[col1.ColumnName];
            }
            foreach (DataColumn col2 in t2.Columns)
            {
                insertRow[col2.ColumnName] = fromRow[col2.ColumnName];
            }
            result.Rows.Add(insertRow);
        }
    }
    return result;
}

An example of how you might use this:

var test = JoinDataTables(transactionInfo, transactionItems,
               (row1, row2) =>
               row1.Field<int>("TransactionID") == row2.Field<int>("TransactionID"));

Well done. Is there a way to modify it to be able to specify an AND or an OR operator between multiple "join on" conditions? — Igor, Feb 01 '15 at 15:39

C# DataTable Inner join with dynamic columns

3 Answers3

Linked