Advertise with us

Full outer join, on 2 data tables, with a list of columns

Posted by  Chintoo Semi
 2405  View(s)
Rate this:
Hi Expert,

I have 2 data tables, which I do not know their list of data columns. This list must be extracted at run time, and be used for the full outer join.

When using these columns, the columns between the 2 tables need to be merged, and I need all data to be displayed.

Till now what I am doing is 

Get common columns, using intersect, and implementing IEqualityComparer
Create a new datatable, with these columns, so that the 2 datatables will be merged into this new table
However, I am having issues with Linq, on the 2nd step.

Till now I have :

Get common columns

    // Get common columns
    var commonColumns = dt1.Columns.OfType().Intersect(dt2.Columns.OfType(), new DataColumnComparer());

Create new data table

    // Create the result which is going to be sent to the user
    DataTable result = new DataTable();

    // Add all the columns from both tables
    .Union(dt2.Columns.OfType(), new DataColumnComparer())
    .Select(c => new DataColumn(c.Caption, c.DataType, c.Expression, c.ColumnMapping)).ToArray());

How can I obtain an efficient full outer join dynamically, from the List of datacolumns, that is extracted at run time?
  1. Re: Full outer join, on 2 data tables, with a list of columns

    Hi Chintoo,

    try this,

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

            dt1.PrimaryKey = commonColumns.ToArray();

            result.Merge(dt1, false, MissingSchemaAction.AddWithKey);
            result.Merge(dt2, false, MissingSchemaAction.AddWithKey);

    I hope it work fine.