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

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

本主題中的範例將示範如何使用 GroupJoin 和 Join 方法，透過查詢運算式語法來查詢 AdventureWorks Sales Model。這些範例中使用的 AdventureWorks Sales Model 是從 AdventureWorks 範例資料庫中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 資料表所建立。

本主題中的範例將使用下列 using/Imports 陳述式：

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
Imports System.Data.Objects
Imports System.Globalization

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using AdventureWorksModel;
using System.Globalization;

如需詳細資訊，請參閱 HOW TO：在 Visual Studio 中建立 LINQ to Entities 專案。

GroupJoin

範例

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

Using AWEntities As New AdventureWorksEntities
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
    Dim details As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail

    Dim query = _
        From order In orders _
        Group Join detail In details _
        On order.SalesOrderID _
        Equals detail.SalesOrderID Into orderGroup = Group _
        Select New With _
        { _
            .CustomerID = order.SalesOrderID, _
            .OrderCount = orderGroup.Count() _
        }

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

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
    ObjectQuery<SalesOrderDetail> details = AWEntities.SalesOrderDetail;

    var query =
        from order in orders
        join detail in details
        on order.SalesOrderID
        equals detail.SalesOrderID into orderGroup
        select new
        {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count()
        };

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

範例

下列範例會在 Contact 和 SalesOrderHeader 資料表上執行 GroupJoin 來尋找每一連絡人的定單數目。然後顯示每一連絡人的訂單數目和 ID。

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From contact In contacts _
            Group Join order In orders _
            On contact.ContactID _
            Equals order.Contact.ContactID Into contactGroup = Group _
            Select New With { _
                .ContactID = contact.ContactID, _
                .OrderCount = contactGroup.Count(), _
                .Orders = contactGroup.Select(Function(order) order)}

    For Each group In query
        Console.WriteLine("ContactID: {0}", group.ContactID)
        Console.WriteLine("Order count: {0}", group.OrderCount)

        For Each orderInfo In group.Orders
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID)
        Next

        Console.WriteLine("")
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from contact in contacts
        join order in orders
        on contact.ContactID
        equals order.Contact.ContactID into contactGroup
        select new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup.Select(order => order)
        };

    foreach (var group in query)
    {
        Console.WriteLine("ContactID: {0}", group.ContactID);
        Console.WriteLine("Order count: {0}", group.OrderCount);
        foreach (var orderInfo in group.Orders)
        {
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID);
        }
        Console.WriteLine("");
    }
}

範例

下列範例會針對 Contact 和 SalesOrderHeader 資料表執行 GroupJoin。群組聯結是左外部聯結的對等項目，它會傳回第一個 (左) 資料來源的每個項目，即使其他資料來源中沒有相互關聯的項目也一樣。

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = _
        From contact In contacts _
            Group Join order In orders _
            On contact.ContactID _
            Equals order.Contact.ContactID Into contactGroup = Group _
            Select New With { _
                .ContactID = contact.ContactID, _
                .OrderCount = contactGroup.Count(), _
                .Orders = contactGroup.Select(Function(order) order)}

    For Each group In query
        Console.WriteLine("ContactID: {0}", group.ContactID)
        Console.WriteLine("Order count: {0}", group.OrderCount)

        For Each orderInfo In group.Orders
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID)
        Next

        Console.WriteLine("")
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from contact in contacts
        join order in orders
        on contact.ContactID
        equals order.Contact.ContactID into contactGroup
        select new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup.Select(order => order)
        };

    foreach (var group in query)
    {
        Console.WriteLine("ContactID: {0}", group.ContactID);
        Console.WriteLine("Order count: {0}", group.OrderCount);
        foreach (var orderInfo in group.Orders)
        {
            Console.WriteLine("   Sale ID: {0}", orderInfo.SalesOrderID);
        }
        Console.WriteLine("");
    }
}

Join

範例

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

Using AWEntities As New AdventureWorksEntities
    Dim orders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader
    Dim details As ObjectQuery(Of SalesOrderDetail) = AWEntities.SalesOrderDetail

    Dim query = _
        From ord In orders _
        Join det In details _
        On ord.SalesOrderID Equals det.SalesOrderID _
        Where ord.OnlineOrderFlag = True _
                And ord.OrderDate.Month = 8 _
        Select New With _
        { _
            .SalesOrderID = ord.SalesOrderID, _
            .SalesOrderDetailID = det.SalesOrderDetailID, _
            .OrderDate = ord.OrderDate, _
            .ProductID = det.ProductID _
        }

    For Each ord In query
        Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2:d}" & vbTab & "{3}", _
            ord.SalesOrderID, _
            ord.SalesOrderDetailID, _
            ord.OrderDate, _
            ord.ProductID)
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;
    ObjectQuery<SalesOrderDetail> details = AWEntities.SalesOrderDetail;

    var query =
        from order in orders
        join detail in details
        on order.SalesOrderID equals detail.SalesOrderID
        where order.OnlineOrderFlag == true
        && order.OrderDate.Month == 8
        select new
        {
            SalesOrderID = order.SalesOrderID,
            SalesOrderDetailID = detail.SalesOrderDetailID,
            OrderDate = order.OrderDate,
            ProductID = detail.ProductID
        };

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

另請參閱

概念

查詢運算式語法範例 (LINQ to Entities)