Linq - 以逗号分隔的值

问题描述：

我有桌子1

i have Table1

Drug_no DrugName
  1     A
  2     B
  3     C
  4     D

table 2

Drug_No   Substance
   1      Num
   1      Feb
   2      tell
   2      tak

需要生成合并表格如

need To Produce merge table like

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

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

我尝试过：

i尝试

Dim rowData =（来自row1在drugTable.AsEnumerable（）_

加入row2在SubstanceTable中。 AsEnumerable（）_

在row1.Field（Of Integer）（Drug_id）等于row2.Field（Of Integer）（Drug_id）_

选择row1。 ItemArray.Concat（row2.ItemArray）.ToArray（））

For Each values As Object（）in rowData

targetTable.Rows。添加（值）

下一步

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

答

我不知道它在vb.net中的含义（或C＃），但是，我的方法将通过captu的中间步骤将匹配的物质重新列入清单 - 查看下面的'gs'，并考虑'分组物质'

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'

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};

这将为您提供查询中的对象列表 drugNo ， drugName 带有物质的内在列表 - 然后你可以从这里到最终的代表

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

检查一下：

Check this:

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:

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

如需了解更多信息，请参阅：

群组加入条款（视觉效果）基本） [ ^ ]

101 Linq样本（lambda样式）：GroupJoin [ ^ ]

Linq - 以逗号分隔的值

相关推荐