Method-Based 查詢語法範例：瀏覽關聯性

Entity Framework 中的導覽屬性是用來定位關聯末端實體的快捷屬性。流覽屬性可讓使用者從某個實體巡覽到另一個實體，或透過關聯集從某個實體巡覽至相關實體。本主題提供方法型查詢語法中的範例，說明如何透過 LINQ to Entities 查詢中的導覽屬性巡覽關聯性。

這些範例中使用的 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

範例 1

下列以方法為基礎的查詢語法範例會使用 SelectMany 方法來取得姓氏為 “周” 之聯繫人的所有訂單。 Contact.SalesOrderHeader 導覽屬性可用來取得每個聯繫人 SalesOrderHeader 物件的集合。

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .SelectMany(c => c.SalesOrderHeaders);

    foreach (var order in ordersQuery)
    {
        Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
            order.SalesOrderID, order.OrderDate, order.TotalDue);
    }
}

Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .SelectMany(Function(o) o.SalesOrderHeaders)

    For Each order In ordersQuery
        Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}", _
                order.SalesOrderID, order.OrderDate, order.TotalDue)
    Next
End Using

範例 2

下列以方法為基礎的查詢語法範例會使用 Select 方法來取得所有聯繫人標識碼，以及姓氏為 “周” 的每個聯繫人應得的總和。 Contact.SalesOrderHeader 導覽屬性可用來取得每個聯繫人 SalesOrderHeader 物件的集合。 Sum 方法會使用 Contact.SalesOrderHeader 導覽屬性來加總每個聯繫人的所有訂單的應付款總額。

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .Select(c => new
        {
            ContactID = c.ContactID,
            Total = c.SalesOrderHeaders.Sum(o => o.TotalDue)
        });

    foreach (var contact in ordersQuery)
    {
        Console.WriteLine($"Contact ID: {contact.ContactID} Orders total: {contact.Total}");
    }
}

Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .Select(Function(c) New With _
                {.ContactID = c.ContactID, _
                .Total = c.SalesOrderHeaders.Sum(Function(o) o.TotalDue)})

    For Each order In ordersQuery
        Console.WriteLine("Contact ID: {0} Orders total: {1}", order.ContactID, order.Total)
    Next
End Using

範例 3

下列以方法為基礎的查詢語法範例會取得姓氏為「周」之聯繫人的所有順序。 Contact.SalesOrderHeader 導覽屬性可用來取得每個聯繫人 SalesOrderHeader 物件的集合。聯繫人的名稱和訂單會以匿名類型傳回。

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.Contacts
        .Where(c => c.LastName == lastName)
        .Select(c => new { LastName = c.LastName, Orders = c.SalesOrderHeaders });

    foreach (var order in ordersQuery)
    {
        Console.WriteLine($"Name: {order.LastName}");
        foreach (SalesOrderHeader orderInfo in order.Orders)
        {
            Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}",
                orderInfo.SalesOrderID, orderInfo.OrderDate, orderInfo.TotalDue);
        }
        Console.WriteLine("");
    }
}

Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.Contacts _
    .Where(Function(c) c.LastName = lastName) _
    .Select(Function(o) New With _
                {.LastName = o.LastName, _
                 .Orders = o.SalesOrderHeaders})

    For Each order In ordersQuery
        Console.WriteLine("Name: {0}", order.LastName)
        For Each orderInfo In order.Orders

            Console.WriteLine("Order ID: {0}, Order date: {1}, Total Due: {2}", _
                    orderInfo.SalesOrderID, orderInfo.OrderDate, orderInfo.TotalDue)
        Next

        Console.WriteLine("")
    Next
End Using

範例 4

下列範例會使用 SalesOrderHeader.Address 和 SalesOrderHeader.Contact 導覽屬性來取得與每個訂單相關聯的 Address 和 Contact 物件集合。聯繫人的姓氏、街道地址、銷售訂單號碼，以及每筆訂單的到期總金額，都會以匿名類型的格式傳回至西雅圖市。

string city = "Seattle";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var ordersQuery = context.SalesOrderHeaders
        .Where(o => o.Address.City == city)
        .Select(o => new
        {
            ContactLastName = o.Contact.LastName,
            ContactFirstName = o.Contact.FirstName,
            StreetAddress = o.Address.AddressLine1,
            OrderNumber = o.SalesOrderNumber,
            TotalDue = o.TotalDue
        });

    foreach (var orderInfo in ordersQuery)
    {
        Console.WriteLine("Name: {0}, {1}", orderInfo.ContactLastName, orderInfo.ContactFirstName);
        Console.WriteLine($"Street address: {orderInfo.StreetAddress}");
        Console.WriteLine($"Order number: {orderInfo.OrderNumber}");
        Console.WriteLine($"Total Due: {orderInfo.TotalDue}");
        Console.WriteLine("");
    }
}

Dim city = "Seattle"
Using context As New AdventureWorksEntities
    Dim ordersQuery = context.SalesOrderHeaders _
             .Where(Function(o) o.Address.City = city) _
             .Select(Function(o) New With { _
                            .ContactLastName = o.Contact.LastName, _
                            .ContactFirstName = o.Contact.FirstName, _
                            .StreetAddress = o.Address.AddressLine1, _
                            .OrderNumber = o.SalesOrderNumber, _
                            .TotalDue = o.TotalDue _
             })

    For Each orderInfo In ordersQuery
        Console.WriteLine("Name: {0}, {1}", orderInfo.ContactLastName, orderInfo.ContactFirstName)
        Console.WriteLine("Street address: {0}", orderInfo.StreetAddress)
        Console.WriteLine("Order number: {0}", orderInfo.OrderNumber)
        Console.WriteLine("Total Due: {0}", orderInfo.TotalDue)
        Console.WriteLine("")
    Next

End Using

範例 5

下列範例會使用 Where 方法來尋找在 2003 年 12 月 1 日之後所做的訂單，然後使用 order.SalesOrderDetail 導覽屬性來取得每個訂單的詳細數據。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    IQueryable<SalesOrderHeader> query =
        from order in context.SalesOrderHeaders
        where order.OrderDate >= new DateTime(2003, 12, 1)
        select order;

    Console.WriteLine("Orders that were made after December 1, 2003:");
    foreach (SalesOrderHeader order in query)
    {
        Console.WriteLine($"OrderID {order.SalesOrderID} Order date: {order.OrderDate:d} ");
        foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
        {
            Console.WriteLine($"  Product ID: {orderDetail.ProductID} Unit Price {orderDetail.UnitPrice}");
        }
    }
}

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

    Dim query = _
        From order In orders _
        Where order.OrderDate >= New DateTime(2003, 12, 1) _
        Select order

    Console.WriteLine("Orders that were made after December 1, 2003:")
    For Each order In query
        Console.WriteLine("OrderID {0} Order date: {1:d} ", _
                order.SalesOrderID, order.OrderDate)
        For Each orderDetail In order.SalesOrderDetails
            Console.WriteLine("  Product ID: {0} Unit Price {1}", _
                orderDetail.ProductID, orderDetail.UnitPrice)
        Next
    Next
End Using

另請參閱

Last updated on 2025-03-30

共用方式為