c# datatable join and select all columns from both table

Question

I need to join two datatable's and select all values from both table because d1 columns are dynamic i have tried with below code and getting the join value but when i select it shows in 2 DataRows it should be in one row

        DataTable dtRtn = new DataTable();

        var result = from d1 in dtFormData.AsEnumerable()
                     join d2 in dtResponderDetails.AsEnumerable()
                     on d1.Field<string>("ResponderId") equals d2.Field<string>("EmployeeId")
                     select new { d1,d2};

I need both d1 and d2 joined result to be copied to dtRtn table

Edit

I have searched but there is not straight forward answer for and all answers shows how to select specific columns

Please help thanks

Possible duplicate of [LINQ - Full Outer Join](https://stackoverflow.com/questions/5489987/linq-full-outer-join) — P.Brian.Mackey, Apr 12 '18 at 02:49

Jon Senchyna · Answer 1 · 2018-04-12T15:46:48.650

You will need to first dynamically add the columns from dtFormData and dtResponderDetails to dtRtn. Then you can iterate through the results of your LINQ query and create new DataRow objects for each.

Here is a quick stab at the necessary code you would need to add to what you currently have:

// Step 1: Add all columns from your two DataTables to dtRtn
// (with a prefix to avoid naming collisions)
DataTable dtRtn = new DataTable();
CopyColumns(dtFormData, dtRtn, "FormData.");
CopyColumns(dtResponderDetails, dtRtn, "ResponderDetails.");

// Step 2: Build a collection containing all of the values for this result
var numFormColumns = dtFormData.Columns.Count;
var numResponderColumns = dtResponderDetails.Columns.Count;
foreach(var row in result) {
    var targetRow = new List<object>();
    PopulateRows(row.d1, numFormColumns, targetRow);
    PopulateRows(row.d2, numResponderColumns, targetRow);
    // Pass the values in as an array, which will convert it to a new DataRow
    dtRtn.Rows.Add(targetRow.ToArray());
}

//...

private void CopyColumns(DataTable sourceTable, DataTable targetTable, string rowPrefix)
{
    foreach (DataColumn column in sourceTable.Columns)
    {
        var rowName = String.Format("{0}{1}", rowPrefix, column.ColumnName);
        targetTable.Columns.Add(rowName, column.DataType);
    }
}

void PopulateRows(DataRow sourceRow, int numColumns, List<object> targetRow)
{
    for(var i = 0; i < numColumns; i++) {
        targetRow.Add(sourceRow[i]);
    }
}

c# datatable join and select all columns from both table

1 Answers1