Linq - value as comma seperated

Question

0.00/5 (No votes)

See more:

i have Table1

HTML

Drug_no DrugName
  1     A
  2     B
  3     C
  4     D

table 2

HTML

Drug_No   Substance
   1      Num
   1      Feb
   2      tell
   2      tak

need To Produce merge table like

HTML

DrugNo  DrugName  Substance
1       A           Num,Feb
2       B           tell,tak
3       C
4       D

C#

i tried 
Dim rowData = (From row1 In drugTable.AsEnumerable() _
                       Join row2 In SubstanceTable.AsEnumerable() _
                            On row1.Field(Of Integer)("Drug_id") Equals row2.Field(Of Integer)("Drug_id") _
                       Select row1.ItemArray.Concat(row2.ItemArray).ToArray())

        For Each values As Object() In rowData
            targetTable.Rows.Add(values)
        Next

What I have tried:

i tried
Dim rowData = (From row1 In drugTable.AsEnumerable() _
Join row2 In SubstanceTable.AsEnumerable() _
On row1.Field(Of Integer)("Drug_id") Equals row2.Field(Of Integer)("Drug_id") _
Select row1.ItemArray.Concat(row2.ItemArray).ToArray())

For Each values As Object() In rowData
targetTable.Rows.Add(values)
Next

Posted 14-Mar-16 19:09pm

7045Jeegnesh

Updated 22-Mar-16 7:46am

v2

Add a Solution

Comments

7045Jeegnesh 15-Mar-16 1:10am

FYI i Don't know how much column is in table 1

2 solutions

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)

Garth J Lancaster · Answer 1 · 2016-03-14T19:54:00

I dont know what it looks like in vb.net (or C# for that matter), but, my approach would have been through an intermediate step to capture the matching substances into a list - look at 'gs' below, and think 'grouped substances'

C#

var query = 
  join Table1 to Table2 
     by Table1.ID = Table2.ID
       into gs // gs = grouped substances !! 
  select new {drugNo = Table1.Drug_no, drugName = Table1.DrugName, substances = gs};

That would give you a list of Objects in query with drugNo, drugName with an Inner List of substances - you could then go from here to your eventual representation

Maciej Los · Answer 2 · 2016-03-22T07:46:00

Check this:

VB.NET

Dim table1 As DataTable = New DataTable()
table1.Columns.Add(New DataColumn("Drug_no", Type.GetType("System.Int32")))
table1.Columns.Add(New DataColumn("DrugName", Type.GetType("System.String")))
table1.Rows.Add(New Object(){1, "A"})
table1.Rows.Add(New Object(){2, "B"})
table1.Rows.Add(New Object(){3, "C"})
table1.Rows.Add(New Object(){4, "D"})

Dim table2 As DataTable = New DataTable()
table2.Columns.Add(New DataColumn("Drug_no", Type.GetType("System.Int32")))
table2.Columns.Add(New DataColumn("Substance", Type.GetType("System.String")))
table2.Rows.Add(New Object(){1, "Num"})
table2.Rows.Add(New Object(){1, "Feb"})
table2.Rows.Add(New Object(){2, "tell"})
table2.Rows.Add(New Object(){2, "tak"})


Dim result = table1.AsEnumerable() _
			.GroupJoin(table2.AsEnumerable(), _
				Function(t1) t1.Field(Of Integer)("Drug_no"),
				Function(t2) t2.Field(Of Integer)("Drug_no"),
				Function(a, b) New With { _
					.DrugNo = a.Field(Of Integer)("Drug_no"), _
					.DrugName =a.Field(Of String)("DrugName"), _
					.Substances = String.Join(",", b.Select(Function(x) x.Field(Of String)("Substance")).ToArray())})

Result:

C#

DrugNo DrugName Substances
1      A        Num,Feb 
2      B        tell,tak 
3      C   
4      D

For further information please, see:
Group Join Clause (Visual Basic)[^]
101 Linq samples (lambda style): GroupJoin[^]

Linq - value as comma seperated

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0