Create combined DataTable from two DataTables joined with LINQ. C#

c#, datatable, join, linq

Solution

Have you looked at this page yet?

HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET

If that approach isn't LINQy enough for you, you could break out the row data into object arrays:

DataTable targetTable = dataTable1.Clone();
var dt2Columns = dataTable2.Columns.OfType<DataColumn>().Select(dc => 
    new DataColumn(dc.ColumnName, dc.DataType, dc.Expression, dc.ColumnMapping));
targetTable.Columns.AddRange(dt2Columns.ToArray());
var rowData =
    from row1 in dataTable1.AsEnumerable()
    join row2 in dataTable2.AsEnumerable()
        on row1.Field<int>("ID") equals row2.Field<int>("ID")
    select row1.ItemArray.Concat(row2.ItemArray).ToArray();
foreach (object[] values in rowData)
    targetTable.Rows.Add(values);

I think that's about as terse as you're going to be able to make it and I'll explain why: it's the schema.

A `DataRow` is not an independent object; it depends on its owning `DataTable` and cannot live without it. There is no supported way to create a "disconnected" `DataRow`; the `CopyToDataTable()` extension method works on rows that already exist in one `DataTable` and simply copy the schema from the source (remember, every `DataRow` has a reference to its parent `Table`) before copying the rows themselves (most likely using `ImportRow`, though I haven't actually opened up Reflector to check).

In this case you have a new schema that you need to create. Before you can create any (new) rows, you need to create the table to hold them first, and that means writing at least the 3 lines of code at the top of the method above.

Then you can finally create the rows - but only one at a time, since the `DataTable` and its associated `DataRowCollection` don't expose any methods to add multiple rows at a time. You could, of course, add your own extension method for the `DataRowCollection` to make this "look" nicer:

public static void AddRange(this DataRowCollection rc,
    IEnumerable<object[]> tuples)
{
    foreach (object[] data in tuples)
        rc.Add(tuples);
}

Then you could get rid of the `foreach` in the first method and replace it with:

targetTable.Rows.AddRange(rowData);

Although that's really just moving the verbosity, not eliminating it.

Bottom line, as long as you're working with the legacy `DataSet` class hierarchy, there's always going to be a little cruft. The Linq to DataSet extensions are nice, but they are only extensions and can't alter the limitations above.

Problem

I have the following code that fills `dataTable1` and `dataTable2` with two simple SQL queries, `dataTableSqlJoined` is filled from the same tables but joined together. I'm trying to write a LINQ query that can create the `dataTableLinqJoined` as if it had been created using SQL. In my example below, it only returns the values from dataTable1. The problem I have is what to put in the `SELECT` of the linq query. How can I create a new DataRow containing all the Columns from both DataRows. I will not know the exact column names / schema of the queries until runtime. ``` sqlCommand = new SqlCommand("SELECT ID, A, B FROM Table1", sqlConnection, sqlTransaction); sqlAdapter = new SqlDataAdapter(sqlCommand); DataTable dataTable1 = new DataTable(); sqlAdapter.Fill(dataTable1); sqlCommand = new SqlCommand("SELECT ID, C, D FROM Table2", sqlConnection, sqlTransaction); sqlAdapter = new SqlDataAdapter(sqlCommand); DataTable dataTable2 = new DataTable(); sqlAdapter.Fill(dataTable2); sqlCommand = new SqlCommand("SELECT Table1.ID, A, B, Table2.ID, C, D FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID", sqlConnection, sqlTransaction); sqlAdapter = new SqlDataAdapter(sqlCommand); DataTable dataTableSqlJoined = new DataTable(); sqlAdapter.Fill(dataTableSqlJoined); var dataRows = from dataRows1 in dataTable1.AsEnumerable() join dataRows2 in dataTable2.AsEnumerable() on dataRows1.Field<int>("ID") equals dataRows2.Field<int>("ID") select dataRows1; // + dataRows2; DataTable dataTableLinqJoined = dataRows.CopyToDataTable(); ``` For a bit more background, the combined query is very DB intensive and is causing performance issues. The data returned by the first query is fairly static and can be heavily cached. The data returned by the second query changes constantly but is fast to run and therefore doesn't need to be cached. There is also a lot of code reliant upon the passing of the combined DataTable and therefore there are not many feasible options available in passing the data in a different format.

Original source