CODEWITHSUNDEEP
c#linqjoindatatable
Cannon
Cannon

Reputation: 2783

DataTable Join using LINQ in C#

I am joining two data tables using LINQ this way:

 DataTable targetTable = dataTable1.Clone();
 var dt2Columns = dataTable2.Columns.OfType<DataColumn>().Select(dc =>
        new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
 var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
                       where targetTable.Columns.Contains(dc.ColumnName) == false
                       select dc;
 targetTable.Columns.AddRange(dt2FinalColumns.ToArray());

 var rowData = from row1 in dataTable1.AsEnumerable()
               join row2 in dataTable2.AsEnumerable()
               on row1.Field<string>("keyCol") equals row2.Field<string>("keyCol")
               select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();
 foreach (object[] values in rowData)
     targetTable.Rows.Add(values);

I am facing three issues here:

  1. In case of row count is not same for two tables, I want to pass default value or assign empty string for values not found in other table. How do I achieve this ?
  2. If I have multiple columns and need to compare with AND how is that possible ?
  3. What if I have to join multiple tables in run time. Is there any way to generate dynamic LINQ ?

Upvotes: 2

Views: 5884

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460048

If both tables have the same primary-key DataTable.Merge will work:

dataTable1.Merge(dataTable2 ,false, MissingSchemaAction.Add);

This will merge the schema(columns) of both tables, joins rows which have the same primary-key and add the other rows.

Upvotes: 2

Related Questions