Join two datatables

[jmcilhinney]

E.g.

Dim parentTable As New DataTable
 
With parentTable.Columns
    .Add("ParentId", GetType(Integer))
    .Add("ParentName", GetType(String))
End With
 
With parentTable.Rows
    .Add(1, "Parent 1")
    .Add(2, "Parent 2")
    .Add(3, "Parent 3")
End With
 
Dim childTable As New DataTable
 
With childTable.Columns
    .Add("ChildId", GetType(Integer))
    .Add("ChildName", GetType(String))
    .Add("ParentId", GetType(Integer))
End With
 
With childTable.Rows
    .Add(1, "Child 1.1", 1)
    .Add(2, "Child 1.2", 1)
    .Add(3, "Child 1.3", 1)
    .Add(4, "Child 2.1", 2)
    .Add(5, "Child 2.2", 2)
    .Add(6, "Child 2.3", 2)
    .Add(7, "Child 3.1", 3)
    .Add(8, "Child 3.2", 3)
    .Add(9, "Child 3.3", 3)
End With
 
'Create a deferred query.
Dim query = From parentRow In parentTable.AsEnumerable()
            Join childRow In childTable.AsEnumerable()
            On parentRow.Field(Of Integer)("ParentId") Equals childRow.Field(Of Integer)("ParentId")
            Select New With {.ParentId = parentRow.Field(Of Integer)("ParentId"),
                             .ParentName = parentRow.Field(Of String)("ParentName"),
                             .ChildId = parentRow.Field(Of Integer)("ChildId"),
                             .ChildName = parentRow.Field(Of String)("ChildName")}
 
'Create a generic List of an anonymous type.
Dim list = query.ToList()

The demonstrates a basic join and shows how it's very similar to a SQL join. This example creates a List(Of T) where T is an anonymous type. You can bind that list directly to a grid or whatever you like. You could also define your own type if you need to be able to access its members outside the code that creates the instances, which you can't really do with an anonymous type. If you want a DataTable then you'll have to create one yourself. There is a CopyToDataTable method that is part of LINQ to DataSet but you need DataRows to begin with in order to call that, and you can't have DataRows without a DataTable.