Home > DeveloperSection > Forums > Full outer join, on 2 data tables, with a list of columns
Chintoo Semi

Total Post:135

Points:947
Posted on    June-19-2013 3:54 AM

 ADO.Net ADO.Net 
Ratings:


 1 Reply(s)
 1157  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
    result.Columns.AddRange(
    dt1.Columns.OfType()
    .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?


Sumit Kesarwani

Total Post:378

Points:2694
Posted on    June-19-2013 8:43 AM

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.

Don't want to miss updates? Please click the below button!

Follow MindStick