title: Create combined DataTable from two DataTables joined with LINQ. C#
body: <p>I have the following code that fills <code>dataTable1</code> and <code>dataTable2</code> with two simple SQL queries, <code>dataTableSqlJoined</code> is filled from the same tables but joined together.</p>
  
  <p>I'm trying to write a LINQ query that can create the <code>dataTableLinqJoined</code> as if it had been created using SQL. In my example below, it only returns the values from dataTable1.</p>
  
  <p>The problem I have is what to put in the <code>SELECT</code> 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.</p>
  
  <pre><code>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&lt;int&gt;("ID") equals dataRows2.Field&lt;int&gt;("ID")
      select
          dataRows1; // + dataRows2;
  
  DataTable dataTableLinqJoined = dataRows.CopyToDataTable();
  </code></pre>
  
  <p>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.</p>