閱讀英文

共用方式為


查詢運算式語法範例:導覽關聯性

Entity Framework 中的巡覽屬性是用來尋找相關聯兩端實體的捷徑屬性。 導覽屬性可讓使用者在不同實體之間巡覽,或是透過關聯集從某個實體巡覽至相關的實體。 本主題提供的查詢運算式語法範例,會說明如何透過 LINQ to Entities 查詢中的巡覽屬性來巡覽關聯性。

這些範例中使用的 AdventureWorks Sales Model 是從 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;

範例 1

下列範例會使用 Select 方法來取得所有連絡人識別碼,以及姓氏為 "Zhou" 之每位連絡人總金額的總和。 Contact.SalesOrderHeader 導覽屬性是用來取得每一個連絡人之 SalesOrderHeader 物件的集合。 Sum 方法會使用 Contact.SalesOrderHeader 導覽屬性來加總每位連絡人之所有訂單的應付總額。

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;

    var ordersQuery = from contact in contacts
                      where contact.LastName == lastName
                      select new
                      {
                          ContactID = contact.ContactID,
                          Total = contact.SalesOrderHeaders.Sum(o => o.TotalDue)
                      };

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

範例 2

下列範例會取得姓氏為 "Zhou" 之連絡人的所有訂單。 Contact.SalesOrderHeader 導覽屬性是用來取得每一個連絡人之 SalesOrderHeader 物件的集合。 該連絡人的名稱和訂單會以匿名型別的形式傳回。

string lastName = "Zhou";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;

    var ordersQuery = from contact in contacts
                      where contact.LastName == lastName
                      select new { LastName = contact.LastName, Orders = contact.SalesOrderHeaders };

    foreach (var order in ordersQuery)
    {
        Console.WriteLine("Name: {0}", 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("");
    }
}

範例 3

下列範例會使用 SalesOrderHeader.AddressSalesOrderHeader.Contact 導覽屬性來取得與每筆訂單相關聯之 AddressContact 物件的集合。 到西雅圖城市之每一筆訂單的連絡人姓氏、街道地址、銷售訂單編號及應付總額會以匿名型別的形式傳回。

string city = "Seattle";
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var ordersQuery = from order in orders
                      where order.Address.City == city
                      select new
                      {
                          ContactLastName = order.Contact.LastName,
                          ContactFirstName = order.Contact.FirstName,
                          StreetAddress = order.Address.AddressLine1,
                          OrderNumber = order.SalesOrderNumber,
                          TotalDue = order.TotalDue
                      };

    foreach (var 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("");
    }
}

範例 4

下列範例會使用 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 {0} Order date: {1:d} ",
            order.SalesOrderID, order.OrderDate);
        foreach (SalesOrderDetail orderDetail in order.SalesOrderDetails)
        {
            Console.WriteLine("  Product ID: {0} Unit Price {1}",
                orderDetail.ProductID, orderDetail.UnitPrice);
        }
    }
}

另請參閱