Join Method-Based Query Syntax Examples (LINQ to DataSet)

Joining is an important operation in queries that target data sources that have no navigable relationships to each other, such as relational database tables. A join of two data sources is the association of objects in one data source with objects that share a common attribute in the other data source. For more information, see Standard Query Operators Overview.

The examples in this topic demonstrate how to use the Join method to query a DataSet using the method query syntax.

The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.

The examples in this topic use the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the using/Imports statements found in Method-Based Query Examples (LINQ to DataSet).

For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.

Join

Example

This example performs a join over the Contact and SalesOrderHeader tables.

' Fill the DataSet. 
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    contacts.AsEnumerable().Join(orders.AsEnumerable(), _
    Function(order) order.Field(Of Int32)("ContactID"), _
    Function(contact) contact.Field(Of Int32)("ContactID"), _
    Function(contact, order) New With _
        { _
            .ContactID = contact.Field(Of Int32)("ContactID"), _
            .SalesOrderID = order.Field(Of Int32)("SalesOrderID"), _
            .FirstName = contact.Field(Of String)("FirstName"), _
            .Lastname = contact.Field(Of String)("Lastname"), _
            .TotalDue = order.Field(Of Decimal)("TotalDue") _
        })

For Each contact_order In query
    Console.WriteLine("ContactID: {0} " _
                    & "SalesOrderID: {1} " _
                    & "FirstName: {2} " _
                    & "Lastname: {3} " _
                    & "TotalDue: {4}", _
        contact_order.ContactID, _
        contact_order.SalesOrderID, _
        contact_order.FirstName, _
        contact_order.Lastname, _
        contact_order.TotalDue)
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query =
    contacts.AsEnumerable().Join(orders.AsEnumerable(),
    order => order.Field<Int32>("ContactID"),
    contact => contact.Field<Int32>("ContactID"),
    (contact, order) => new
    {
        ContactID = contact.Field<Int32>("ContactID"),
        SalesOrderID = order.Field<Int32>("SalesOrderID"),
        FirstName = contact.Field<string>("FirstName"),
        Lastname = contact.Field<string>("Lastname"),
        TotalDue = order.Field<decimal>("TotalDue")
    });


foreach (var contact_order in query)
{
    Console.WriteLine("ContactID: {0} "
                    + "SalesOrderID: {1} "
                    + "FirstName: {2} "
                    + "Lastname: {3} "
                    + "TotalDue: {4}",
        contact_order.ContactID,
        contact_order.SalesOrderID,
        contact_order.FirstName,
        contact_order.Lastname,
        contact_order.TotalDue);
}

Example

This example performs a join over the Contact and SalesOrderHeader tables, grouping the results by contact ID.

' Fill the DataSet. 
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)

Dim contacts As DataTable = ds.Tables("Contact")
Dim orders As DataTable = ds.Tables("SalesOrderHeader")

Dim query = _
    contacts.AsEnumerable().Join(orders.AsEnumerable(), _
    Function(order) order.Field(Of Int32)("ContactID"), _
    Function(contact) contact.Field(Of Int32)("ContactID"), _
        Function(contact, order) New With _
        { _
            .ContactID = contact.Field(Of Int32)("ContactID"), _
            .SalesOrderID = order.Field(Of Int32)("SalesOrderID"), _
            .FirstName = contact.Field(Of String)("FirstName"), _
            .Lastname = contact.Field(Of String)("Lastname"), _
            .TotalDue = order.Field(Of Decimal)("TotalDue") _
        }) _
        .GroupBy(Function(record) record.ContactID)

For Each group In query
    For Each contact_order In group
        Console.WriteLine("ContactID: {0} " _
                        & "SalesOrderID: {1} " _
                        & "FirstName: {2} " _
                        & "Lastname: {3} " _
                        & "TotalDue: {4}", _
            contact_order.ContactID, _
            contact_order.SalesOrderID, _
            contact_order.FirstName, _
            contact_order.Lastname, _
            contact_order.TotalDue)
    Next 
Next

// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);

DataTable contacts = ds.Tables["Contact"];
DataTable orders = ds.Tables["SalesOrderHeader"];

var query = contacts.AsEnumerable().Join(orders.AsEnumerable(),
    order => order.Field<Int32>("ContactID"),
    contact => contact.Field<Int32>("ContactID"),
    (contact, order) => new
    {
        ContactID = contact.Field<Int32>("ContactID"),
        SalesOrderID = order.Field<Int32>("SalesOrderID"),
        FirstName = contact.Field<string>("FirstName"),
        Lastname = contact.Field<string>("Lastname"),
        TotalDue = order.Field<decimal>("TotalDue")
    })
        .GroupBy(record => record.ContactID);

foreach (var group in query)
{
    foreach (var contact_order in group)
    {
        Console.WriteLine("ContactID: {0} "
                        + "SalesOrderID: {1} "
                        + "FirstName: {2} "
                        + "Lastname: {3} "
                        + "TotalDue: {4}",
            contact_order.ContactID,
            contact_order.SalesOrderID,
            contact_order.FirstName,
            contact_order.Lastname,
            contact_order.TotalDue);
    }
}

See Also

Concepts

Loading Data Into a DataSet

Standard Query Operators Overview

Other Resources

LINQ to DataSet Examples