通过

基于方法的查询语法示例：联接 (LINQ to DataSet)

  • 项目

联接是面向相互之间没有可导航关系的数据源（如关系数据库表）的查询中的一项重要操作。 联接两个数据源就是将一个数据源中的对象与另一个数据源中具有相同公共属性的对象相关联。 有关详细信息，请参阅标准查询运算符概述 (C#) 或标准查询运算符概述 (Visual Basic)。

本主题中的示例演示如何使用 Join 方法以便使用该方法的查询语法来查询 DataSet。

这些示例中使用的 FillDataSet 方法在将数据加载到数据集中中指定。

本主题中的示例使用 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 表。

本主题中的示例使用下面的 using/Imports 语句：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;

Option Explicit On

Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization

有关详细信息，请参阅如何：在 Visual Studio 中创建 LINQ to DataSet 项目。

联接

示例

此示例对 Contact 和 SalesOrderHeader 表执行联接。

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

' 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

示例

此示例对 Contact 和 SalesOrderHeader 表执行联接，并对结果按联系人 ID 进行分组。

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

' 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

请参阅