Method-Based 查詢語法範例：聯結運算符

本主題中的範例示範如何使用以方法為基礎的查詢語法，使用 Join 和 GroupJoin 方法來查詢 AdventureWorks Sales Model。 這些範例中使用的 AdventureWorks 銷售模型是從 AdventureWorks 範例資料庫中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 數據表所建置。

本主題中的範例會使用下列 using/Imports 語句：

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;

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

群組加入

範例

下列範例會針對 SalesOrderHeader 和 SalesOrderDetail 數據表執行 GroupJoin，以尋找每位客戶的訂單數目。 群組聯結相當於左外部聯結，它會傳回第一個 （left） 數據源的每個元素，即使其他數據源中沒有任何相互關聯的元素也一樣。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
    ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;

    var query = orders.GroupJoin(details,
        order => order.SalesOrderID,
        detail => detail.SalesOrderID,
        (order, orderGroup) => new
        {
            CustomerID = order.SalesOrderID,
            OrderCount = orderGroup.Count()
        });

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

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
    Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    Dim query = orders.GroupJoin(details, _
            Function(order) order.SalesOrderID, _
            Function(detail) detail.SalesOrderID, _
            Function(order, orderGroup) 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

範例

下列範例會針對 Contact 和 SalesOrderHeader 資料表執行 GroupJoin，以尋找每個聯絡人的訂單數目。 會顯示每個聯繫人的訂單計數和標識碼。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query = contacts.GroupJoin(orders,
        contact => contact.ContactID,
        order => order.Contact.ContactID,
        (contact, contactGroup) => new
        {
            ContactID = contact.ContactID,
            OrderCount = contactGroup.Count(),
            Orders = contactGroup
        });

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

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = contacts.GroupJoin(orders, _
            Function(contact) contact.ContactID, _
            Function(order) order.Contact.ContactID, _
            Function(contact, contactGroup) 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

加入

範例

下列範例會透過 Contact 和 SalesOrderHeader 資料表執行聯結。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        contacts.Join(
            orders,
            order => order.ContactID,
            contact => contact.Contact.ContactID,
            (contact, order) => new
            {
                ContactID = contact.ContactID,
                SalesOrderID = order.SalesOrderID,
                FirstName = contact.FirstName,
                Lastname = contact.LastName,
                TotalDue = order.TotalDue
            });

    foreach (var contact_order in query)
    {
        Console.WriteLine($"ContactID: {contact_order.ContactID} "
                        + "SalesOrderID: {contact_order.SalesOrderID} "
                        + "FirstName: {contact_order.FirstName} "
                        + "Lastname: {contact_order.Lastname} "
                        + "TotalDue: {contact_order.TotalDue}");
    }
}

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.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
End Using

範例

下列範例會透過 Contact 和 SalesOrderHeader 資料表執行聯結，並以聯繫人標識符分組結果。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query = contacts.Join(
        orders,
        order => order.ContactID,
        contact => contact.Contact.ContactID,
        (contact, order) => new
        {
            ContactID = contact.ContactID,
            SalesOrderID = order.SalesOrderID,
            FirstName = contact.FirstName,
            Lastname = contact.LastName,
            TotalDue = order.TotalDue
        })
            .GroupBy(record => record.ContactID);

    foreach (var group in query)
    {
        foreach (var contact_order in group)
        {
            Console.WriteLine($"ContactID: {contact_order.ContactID} "
                            + "SalesOrderID: {contact_order.SalesOrderID} "
                            + "FirstName: {contact_order.FirstName} "
                            + "Lastname: {contact_order.Lastname} "
                            + "TotalDue: {contact_order.TotalDue}");
        }
    }
}

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        contacts.Join( _
            orders, _
            Function(ord) ord.ContactID, _
            Function(cont) cont.Contact.ContactID, _
            Function(cont, ord) New With _
                { _
                    .ContactID = cont.ContactID, _
                    .SalesOrderID = ord.SalesOrderID, _
                    .FirstName = cont.FirstName, _
                    .Lastname = cont.LastName, _
                    .TotalDue = ord.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
End Using

另請參閱

• LINQ 到實體的查詢