Join two datables using LINQ

Question

1.00/5 (1 vote)

See more:

Hello Experts,

I have two Datatables
dt1 has columns as below

Item	     Description	Size	 Bin	MoveQty	ToBin	Comment
AD1952203863	ADVANTA CLT 120/116Q E	AQ042	38	AF118  Reduced Bin AQ042
AD1952203863	ADVANTA CLT 120/116Q E	AQ042	2	AX050	Cleared Bin AQ042
AD1952204763	ADVANTA CLT 120/116Q E	AK088	16	AI110	Reduced Bin AK088

And second datatable as below

Bin	                 Item	          Action
AQ042	                      AD1952203863	  Reduced,Cleared
AR085(Committed Remains)	AD1952233863	Cleared
AR106	                       AD1952234760	Cleared

Now I want a LINQ query to get combined results like

Bin	Item	Description 	Size	MoveQty	ToBin	Action

I have tried one query which is not working.

Dim JoinResult = _
         From o In dtcleared.AsEnumerable _
         Join a In dtNew.AsEnumerable _
         On a.Field(Of String)("Item") Equals o.Field(Of String)("Item") _
       Select New With _
       { _
         Key .Item = a.Field(Of String)("Item"), _
         Key .Bin = a.Field(Of String)("Bin"), _
         Key .Size = a.Field(Of String)("Size"), _
         Key .Description = a.Field(Of String)("Description"), _
         Key .MoveQty = a.Field(Of String)("Move Qty"), _
         Key .Action = o.Field(Of String)("Action")
       }

Can someone help me please?

Posted 19-Oct-15 6:01am

sudevsu

Updated 19-Oct-15 7:10am

v6

Add a Solution

Comments

Maciej Los 19-Oct-15 12:06pm

Sudevsu, you should provide example data. Who knows what you mean by "MoveQty", "ToBin", "Action".
"Not working" is not informative at all.

Maciej Los 19-Oct-15 12:08pm

a.Field(Of String)("Move Qty") or "MoveQty"?

sudevsu 19-Oct-15 12:36pm

a.Field(Of String)("Move Qty")

Richard Deeming 19-Oct-15 12:37pm

According to your sample data, the "Item" column in the first table contains "1", "2" or "3", but the "Item" column in the second table contains "ABC1", "PQR" or "LMN5".

There are no rows in the sample data you've provided where the column values are equal, so therefore your query will not match any rows.

If this is just a quirk of the sample data you've provided, then update the question with more realistic sample data.

If it's the real data, then you're joining on the wrong column.

sudevsu 19-Oct-15 12:56pm

Updated

Richard Deeming 19-Oct-15 13:07pm

Based on the table structures you've posted, the Action column should be o.Field..., not a.Field....

Other than that, and the name of the "MoveQty" column, your code works for me, and produces two results.

You'll need to define "not working" - do you get an error message?

sudevsu 19-Oct-15 13:11pm

I did change those two. But in the Joinresult variable I get "Type Expected".

Other than that there is no error or no result.

Maciej Los 19-Oct-15 13:25pm

What line? Have you tried to debug the programme?

sudevsu 19-Oct-15 13:35pm

yeah I did, instead of going each line on the datatable, only after I press F11, it comes to next line and if I watch the resultjoin, it was showing me that "Type Expected"

Maciej Los 19-Oct-15 13:39pm

MSDN documentation states that "The context calls for either an elementary data type or a declared element name representing a type. To correct this error...". Please, see: Type expected

sudevsu 19-Oct-15 13:56pm

Have been there already. :(

sudevsu 19-Oct-15 14:09pm

Thank you all

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sudevsu · Accepted Answer · 2015-10-19T08:09:00

Solution 1

Finally found solution.

VB

Dim JoinResult1 = _
         (From o In dtcleared.AsEnumerable _
         Join a In dtNew.AsEnumerable _
         On a.Field(Of String)("Item") Equals o.Field(Of String)("Item") _
       Select New With _
       { _
         Key .Item = a.Field(Of String)("Item"), _
         Key .Bin = a.Field(Of String)("Bin"), _
         Key .Size = a.Field(Of String)("Size"), _
         Key .Description = a.Field(Of String)("Description"), _
         Key .MoveQty = a.Field(Of String)("Move Qty"), _
         Key .Action = o.Field(Of String)("Action")
       }).ToList()

I just added ToList() at the end. That's it. Now the results are showing up.

Posted 19-Oct-15 8:09am

sudevsu

Comments

sudevsu 19-Oct-15 14:13pm

@Maciej Los .

Earlier, The solution you gave me here http://www.codeproject.com/Questions/1039700/LINQ-Query-on-Datatable-in-Vb-net?arn=0

Is kind of for this. But I belive I am doing same thing over and over again.
For this reason, I would like to ask you if you can do that with above datatable1 dt1. How would that be possible? I have been trying to figure it out but no luck like the other one. Can you help me please?

Maciej Los 19-Oct-15 14:33pm

Not sure, what you want from me...

sudevsu 19-Oct-15 14:54pm

I want a Right outer Join for

<pre> Dim JoinResult1 = _
(From o In dtcleared.AsEnumerable _
Join a In dtNew.AsEnumerable _
On a.Field(Of String)("Item") Equals o.Field(Of String)("Item") _
Select New With _
{ _
Key .Item = a.Field(Of String)("Item"), _
Key .Bin = a.Field(Of String)("Bin"), _
Key .Size = a.Field(Of String)("Size"), _
Key .Description = a.Field(Of String)("Description"), _
Key .MoveQty = a.Field(Of String)("Move Qty"), _
Key .ToBin = a.Field(Of String)("To Bin"), _
Key .Comment = a.Field(Of String)("Comment"), _
Key .Action = o.Field(Of String)("Action")
}).ToList()
</Pre>

Meaning results something like below
For the columns it should show Empty or ""
<pre>
Item BIN Size Description MoveQty ToBin Comment Action
AD1952234763 AN033 LT24575R16 120/116Q E ADVANTA A/T ROBL 16 AR029 Reduced Bin AN033 Reduced,Reduced,Cleared
AD1952234763 AN033 LT24575R16 120/116Q E ADVANTA A/T ROBL 26 AL031 Reduced Bin AN033 Reduced,Reduced,Cleared
AD1952234763 AN033 LT24575R16 120/116Q E ADVANTA A/T ROBL 13 AL036 Cleared Bin AN033 Reduced,Reduced,Cleared
Reduced,Reduced,Cleared
Reduced,Reduced,Cleared
</Pre>