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

x
?
Solved

Linq - join 2 datatables

Posted on 2008-06-25
5
Medium Priority
?
3,845 Views
Last Modified: 2013-11-26
I'm learning LINQ. But I'm already stuck. (the codebehind may be c# or vb.net - I know them both)
I have 2 datatables like below and I want to create the third table using LINQ.
The key of both is ID. I want to join the two datatables so that I have all the records of the first table (left join).
Can someone help me figure this out.


FirstDataTable - Table 1
 ID      Type            Col1            Col2            
----      ---------                           --------                           ---------
2      Blue            XYZ            ABC
4      Green            LMN            CDF
1      Orange            OPE            BGD
 
Second Table: Table2
 ID      Part            Quantity      
----      ---------                          ---------
1      425687            10
2      425659            25
3      395678            30
4      426589            50
5      423568            25
 
Desired result (in a dataTable if it's possible)
 ID      Type              Part            Quantity            
----      ---------      ---------      ---------
1      Orange            425687            10
2      Blue            425659            25
4      Green            426589            50
0
Comment
Question by:Dhaest
[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
  • 3
  • 2
5 Comments
 
LVL 21

Accepted Solution

by:
naspinski earned 2000 total points
ID: 21863470
This is assuming your 2 datatables are dt1 and dt2
// get the rows you want and combine them into logical rows here:
var combinedRows = from p1 in dt1.AsEnumerable() join p2 in dt2.AsEnumerable() on p1["ID"] equals p2["ID"] orderby p1["ID"] select new { p1,p2 };
 
// now make the new datatable
DataTable dt = new DataTable();
DataColumn dcId = new DataColumn("ID", typeof(int));
DataColumn dcType = new DataColumn("Type", typeof(string));
DataColumn dcPart = new DataColumn("Part", typeof(int));
DataColumn dcQuantity = new DataColumn("Quantity", typeof(int));
dt.Columns.Add(dcId);
dt.Columns.Add(dcType);
dt.Columns.Add(dcPart);
dt.Columns.Add(dcQuantity);
//table is made, now just take and insert your values into the new DataTable
 
foreach (var v in combinedRows)
{
    DataRow dr = dt.NewRow();
    dr["ID"] = v.p1["ID"];//this is in v.p1
    dr["Type"] = v.p1["Type"];
    dr["Part"] = v.p2["Part"];//notice we are looking in v.p2 here now
    dr["Quantity"] = v.p2["Quantity"];
    dt.Rows.Add(dr);
}
 
//now put it on the screent o make sure it worked
GridView gv = new GridView();
gv.DataSource = dt;
gv.DataBind();
form1.Controls.Add(gv);

Open in new window

0
 
LVL 53

Author Comment

by:Dhaest
ID: 21863525
So easy (just use the join directly, how could I overlook it). Thanks anyway.
For c# I found this link: http://msdn.microsoft.com/en-us/library/bb397676.aspx
0
 
LVL 21

Expert Comment

by:naspinski
ID: 21863568
the more I learn about linq, the easier everything seems :)
0
 
LVL 53

Author Comment

by:Dhaest
ID: 21863629
Do you know how I need to do this statement in vb.net ? I can't figure out the last part: select ...

Dim combinedRows = From p1 In FirstTable.AsEnumerable() Join p2 In secondTable.AsEnumerable On FirstTable("id") Equals secondTable("id") Order By FirstTable("id") Select New p1
0
 
LVL 21

Expert Comment

by:naspinski
ID: 21863664
if you are just selecting p1, you can just do this:

Dim combinedRows = From p1 In FirstTable.AsEnumerable() Join p2 In secondTable.AsEnumerable() On FirstTable("id") Equals secondTable("id") Order By FirstTable("id") Select p1

but if you are looking for the same way to do "Select new {something, sometheing_else}" it isn't that easy in VB, you have to aggregate yourself:
http://www.infoq.com/news/2007/09/LINQ-Aggregates
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Summary Displaying images in RichTextBox is a common requirement with limited solutions available. Pasting through clipboard or embedding into RTF content only support static images.  This article describes how to insert Windows control objects int…
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

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