訓練
模組
Reduce complexity in your data model with Dataverse table relationships - Training
Learn how to use Microsoft Dataverse relationships in canvas apps from Microsoft Power Apps.
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;
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
下列範例會使用 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);
}
}
Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim ordersQuery = From contact In contacts _
Where contact.LastName = lastName _
Select New With _
{.ContactID = contact.ContactID, _
.Total = contact.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
下列範例會取得姓氏為 "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("");
}
}
Dim lastName = "Zhou"
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim ordersQuery = From contact In contacts _
Where contact.LastName = lastName _
Select New With _
{.LastName = contact.LastName, _
.Orders = contact.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
下列範例會使用 SalesOrderHeader.Address
和 SalesOrderHeader.Contact
導覽屬性來取得與每筆訂單相關聯之 Address
和 Contact
物件的集合。 到西雅圖城市之每一筆訂單的連絡人姓氏、街道地址、銷售訂單編號及應付總額會以匿名型別的形式傳回。
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("");
}
}
Dim city = "Seattle"
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim ordersQuery = From order In orders _
Where order.Address.City = city _
Select New With { _
.ContactLastName = order.Contact.LastName, _
.ContactFirstName = order.Contact.FirstName, _
.StreetAddress = order.Address.AddressLine1, _
.OrderNumber = order.SalesOrderNumber, _
.TotalDue = order.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
下列範例會使用 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);
}
}
}
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
訓練
模組
Reduce complexity in your data model with Dataverse table relationships - Training
Learn how to use Microsoft Dataverse relationships in canvas apps from Microsoft Power Apps.