Solved

C# join two DataTable together with inner join

Posted on 2013-11-03
3
26,049 Views
Last Modified: 2016-02-10
I have 2 datatables that I want to merge into 1 in the same way that one merges with an inner join in sql server.

One catch is that some of the field names are the same in both tables, but the values may be different (eg. pricing values). The tables both have a column 'ID' that have the same value and can be joined.
0
Comment
Question by:esak2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39619921
Hi esak2000;

In order to do what you want you will need to create a new DataTable object with all the columns you wish to include from the two original DataTable's. You will need to give those columns with the same name different names in the new table. Once you have that in place you can run a Linq to Object query as follows:

var resultingTable = from t1 in dataTable1.AsEnumerable()
                     join t2 in dataTable2.AsEnumerable() 
                         on t1.Field<int>("ID") equals t2.Field<int>("ID")
                     select new { t1, t2 };
                     
// Now with the results of the query fill in the columns of the new DataTable
foreach(var dr in resultingTable)
{
    DataRow newRow = newDataTable.NewRow();
    // Now fill the row with the value from the query t1
    newRow["ColumnName1"] = dr.t1.Field<DataType of Column>("ColumnName1");
    // ... Continue with all the columns from t1 in the same way
    
    // Now fill the row with the value from the query t2
    // In the same way as above
    
    // When all columns have been filled in then add the row to the table
    newDataTable.Rows.Add(newRow);
} 

Open in new window

0
 

Author Comment

by:esak2000
ID: 39620108
That's amazing, you save me hours of time, thank you!!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39620179
Not a problem, glad to help.
0

Featured Post

Myth Busting: MongoDB Scalability (it scales!)

I was talking with one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. He mentioned to me that several customers have been telling him that “MongoDB doesn’t scale!” MongoDB’s scalability was in question?

My response was, “Is that a joke?"

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question