c# - Finding common columns from two datatable and using those for Join condition in LINQ

Question

I have two Data Tables and these are completely dynamic. These would be generated at runtime. Now I want to Join these tables by finding the common columns.

Kindly check below code for further information

public DataTable DataTableJoiner(DataTable dt1, DataTable dt2)
{
    using (DataTable targetTable = dt1.Clone())
    {
        var dt2Query = dt2.Columns.OfType<DataColumn>().Select(dc =>
            new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
        var dt2FilterQuery = from dc in dt2Query.AsEnumerable()
                             where targetTable.Columns.Contains(dc.ColumnName) == false
                             select dc;
        targetTable.Columns.AddRange(dt2FilterQuery.ToArray());
        var rowData=from row1 in dt1.AsEnumerable()
                    join row2 in dt2.AsEnumerable()
                    on row1.Field<int>("ID") equals row2.Field<int>("ID")
                    select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();
        foreach (object[] values in rowData) targetTable.Rows.Add(values); 
        return targetTable;
    }
}

In the above I have hardcoded "ID" as the common column. I need the common column to be produced/recognized dynamically. Please help me.

Answer 1

What about this, which worked for me:

private DataTable DataTableJoiner(DataTable dt1, DataTable dt2)
    {
        var commonColumns = dt1.Columns.OfType<DataColumn>().Intersect(dt2.Columns.OfType<DataColumn>(), new DataColumnComparer());

        var result = new DataTable();
        result.Columns.AddRange(
            dt1.Columns.OfType<DataColumn>()
            .Union(dt2.Columns.OfType<DataColumn>(), new DataColumnComparer())
            .Select(c => new DataColumn(c.Caption, c.DataType, c.Expression, c.ColumnMapping))
            .ToArray());

        var rowData = dt1.AsEnumerable().Join(
            dt2.AsEnumerable(),
            row => commonColumns.Select(col => row[col.Caption]).ToArray(),
            row => commonColumns.Select(col => row[col.Caption]).ToArray(),
            (row1, row2) => 
            {
                var row = result.NewRow();
                row.ItemArray = result.Columns.OfType<DataColumn>().Select(col => row1.Table.Columns.Contains(col.Caption) ? row1[col.Caption] : row2[col.Caption]).ToArray();
                return row;
            },
            new ObjectArrayComparer());

        foreach (var row in rowData)
            result.Rows.Add(row);

        return result;
    }

For this to work, you need to declare these 2 classes in addition:

private class DataColumnComparer : IEqualityComparer<DataColumn>
    {

        #region IEqualityComparer<DataColumn> Members

        public bool Equals(DataColumn x, DataColumn y)
        {
            return x.Caption == y.Caption;
        }

        public int GetHashCode(DataColumn obj)
        {
            return obj.Caption.GetHashCode();
        }

        #endregion
    }

    private class ObjectArrayComparer : IEqualityComparer<object[]>
    {
        #region IEqualityComparer<object[]> Members

        public bool Equals(object[] x, object[] y)
        {
            for (var i = 0; i < x.Length; i++)
            {
                if (!object.Equals(x[i], y[i]))
                    return false;
            }

            return true;
        }

        public int GetHashCode(object[] obj)
        {
            return obj.Sum(item => item.GetHashCode());
        }

        #endregion
    }

I hope this helps!

Comments

Hi,

Thanks for you help. I'm new to LINQ query. for the above answer, I'm getting Arithmetic operation resulted in an overflow error.

I have two DataTable. the 1st table having 40 columns and 2nd one having 3 unique key columns. the same key columns also present in the 1st table.

now I want to join both DataTable with Key columns dynamically.

I shouldn't use like this row1.Field<int>("ID") equals row2.Field<int>("ID")