Hi Everyone,
Can you help on how to to do this using linq in vb.net?
The Datatable1 columns should be group same like Datatable2 result.
Datatable1
ID# | NAME | DEPT
E12345 | Joe | HR
E12345 | Joe | HR
E12345 | Joe | HR
T54333 | Fred | IT
T54333 | Fred | IT
I22544 | Kate | AC
I22544 | Kate | AC
Expected result
Datatable2
ID# | NAME | DEPT
E12345 | Joe | HR
T54333 | Fred | IT
I22544 | Kate | AC
What I have tried:
Tried below code but the column NAME, DEPT has system error result.
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn() _
{
New DataColumn("ID#", Type.GetType("System.String")),
New DataColumn("Name", Type.GetType("System.String")),
New DataColumn("Dept", Type.GetType("System.String"))
})
dt.Rows.Add(New Object() {"E12345", "Joe", "HR"})
dt.Rows.Add(New Object() {"E12345", "Joe", "HR"})
dt.Rows.Add(New Object() {"E12345", "Joe", "HR"})
dt.Rows.Add(New Object() {"T54333", "Fred", "IT"})
dt.Rows.Add(New Object() {"T54333", "Fred", "IT"})
dt.Rows.Add(New Object() {"I22544", "Kate", "AC"})
dt.Rows.Add(New Object() {"I22544", "Kate", "AC"})
Dim groupID = dt.AsEnumerable() _
.GroupBy(Function(x) x.Field(Of String)("ID#")) _
.Select(Function(grp) New With
{
.ID = grp.Key,
.Name = grp.Select(Function(x) x.Field(Of String)("Name")),
.Dept = grp.Select(Function(x) x.Field(Of String)("Dept"))
}) _
.ToList()
Dim dtF As DataTable = dt.Clone()
For Each item In groupID
Dim dr = dtF.NewRow()
'dr("ID#") = item.ID
'dr("Name") = item.Name
dtF.Rows.Add(item.ID, item.Name, item.Dept)
Next
DataGridView1.DataSource = dtF