查詢運算式語法範例：聯結運算子 (LINQ to DataSet)

發行項
02/21/2024

在以彼此沒有可瀏覽關聯性之資料來源 (例如關聯式資料庫資料表) 為目標的查詢中，聯結 (Join) 是一項重要的作業。兩個資料來源的聯結是指某個資料來源中的物件與另一個資料來源中共用相同屬性之物件的關聯。如需詳細資訊，請參閱標準查詢運算子概觀 (C#) 或標準查詢運算子概觀 (Visual Basic)。

此主題中的範例將示範如何使用 GroupJoin 和 Join 方法並搭配查詢運算式語法來查詢 DataSet。

將資料載入 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 專案。

GroupJoin

範例

這則範例會針對 GroupJoin 和 SalesOrderHeader 資料表執行 SalesOrderDetail，以便尋找每位客戶的訂單數目。群組聯結是左外部聯結的對等項目，它會傳回第一個 (左) 資料來源的每個項目，即使其他資料來源中沒有相互關聯的項目也一樣。

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

var orders = ds.Tables["SalesOrderHeader"].AsEnumerable();
var details = ds.Tables["SalesOrderDetail"].AsEnumerable();

var query =
    from order in orders
    join detail in details
    on order.Field<int>("SalesOrderID")
    equals detail.Field<int>("SalesOrderID") into ords
    select new
    {
        CustomerID =
            order.Field<int>("SalesOrderID"),
        ords = ords.Count()
    };

foreach (var order in query)
{
    Console.WriteLine("CustomerID: {0}  Orders Count: {1}",
        order.CustomerID,
        order.ords);
}

' 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 orders = ds.Tables("SalesOrderHeader").AsEnumerable()
Dim details = ds.Tables("SalesOrderDetail").AsEnumerable()

Dim query = _
        From order In orders _
        Group Join detail In details _
            On order.Field(Of Integer)("SalesOrderID") _
            Equals detail.Field(Of Integer)("SalesOrderID") Into ords = Group _
        Select New With _
            { _
                .CustomerID = order.Field(Of Integer)("SalesOrderID"), _
                .ords = ords.Count() _
            }

For Each order In query
    Console.WriteLine("CustomerID: {0}  Orders Count: {1}", _
        order.CustomerID, order.ords)
Next

範例

這則範例會針對 GroupJoin 和 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 =
    from contact in contacts.AsEnumerable()
    join order in orders.AsEnumerable()
    on contact.Field<Int32>("ContactID") equals
    order.Field<Int32>("ContactID")
    select 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 = _
    From contact In contacts.AsEnumerable(), order In orders.AsEnumerable() _
    Where (contact.Field(Of Integer)("ContactID") = _
        order.Field(Of Integer)("ContactID")) _
    Select New With _
        { _
            .ContactID = contact.Field(Of Integer)("ContactID"), _
            .SalesOrderID = order.Field(Of Integer)("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.Write("ContactID: " & contact_order.ContactID)
    Console.Write(" SalesOrderID: " & contact_order.SalesOrderID)
    Console.Write(" FirstName: " & contact_order.FirstName)
    Console.Write(" Lastname: " & contact_order.Lastname)
    Console.WriteLine(" TotalDue: " & contact_order.TotalDue)
Next

聯結

範例

這則範例會針對 SalesOrderHeader 和 SalesOrderDetail 資料表執行聯結，以便取得八月份的線上訂單。

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

DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable details = ds.Tables["SalesOrderDetail"];

var query =
    from order in orders.AsEnumerable()
    join detail in details.AsEnumerable()
    on order.Field<int>("SalesOrderID") equals
        detail.Field<int>("SalesOrderID")
    where order.Field<bool>("OnlineOrderFlag") == true
    && order.Field<DateTime>("OrderDate").Month == 8
    select new
    {
        SalesOrderID =
            order.Field<int>("SalesOrderID"),
        SalesOrderDetailID =
            detail.Field<int>("SalesOrderDetailID"),
        OrderDate =
            order.Field<DateTime>("OrderDate"),
        ProductID =
            detail.Field<int>("ProductID")
    };

foreach (var order in query)
{
    Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
        order.SalesOrderID,
        order.SalesOrderDetailID,
        order.OrderDate,
        order.ProductID);
}

' 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 orders As DataTable = ds.Tables("SalesOrderHeader")
Dim details As DataTable = ds.Tables("SalesOrderDetail")


Dim query = _
    From order In orders.AsEnumerable() _
    Join detail In details.AsEnumerable() _
    On order.Field(Of Integer)("SalesOrderID") Equals _
            detail.Field(Of Integer)("SalesOrderID") _
    Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
            order.Field(Of DateTime)("OrderDate").Month = 8 _
    Select New With _
    { _
        .SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
        .SalesOrderDetailID = detail.Field(Of Integer)("SalesOrderDetailID"), _
        .OrderDate = order.Field(Of DateTime)("OrderDate"), _
        .ProductID = detail.Field(Of Integer)("ProductID") _
    }

For Each order In query
    Console.WriteLine(order.SalesOrderID & vbTab & _
        order.SalesOrderDetailID & vbTab & _
        order.OrderDate & vbTab & _
        order.ProductID)
Next

共用方式為

查詢運算式語法範例：聯結運算子 (LINQ to DataSet)

GroupJoin

範例

範例

聯結

範例

另請參閱

其他資源