Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

C# join two DataTable together with inner join

Posted on 2013-11-03
3
Medium Priority
?
27,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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

715 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