Contoh Sintaks Ekspresi Kueri: Menavigasi Hubungan
Properti navigasi dalam Kerangka Kerja Entitas adalah properti pintasan yang digunakan untuk menemukan entitas di akhir asosiasi. Properti navigasi memungkinkan pengguna untuk menavigasi dari satu entitas ke entitas lain, atau dari satu entitas ke entitas terkait melalui kumpulan asosiasi. Topik ini menyediakan contoh dalam sintaks ekspresi kueri tentang cara menavigasi hubungan melalui properti navigasi di LINQ ke kueri Entitas.
Model Penjualan AdventureWorks yang digunakan dalam contoh ini dibuat dari tabel Kontak, Alamat, Produk, SalesOrderHeader, dan SalesOrderDetail dalam database sampel AdventureWorks.
Contoh dalam topik ini menggunakan pernyataan using/Imports berikut:
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
Contoh 1
Contoh berikut menggunakan metode Select untuk mendapatkan semua ID kontak dan jumlah total yang harus dibayar untuk setiap kontak yang nama belakangnya adalah "Zhou". Properti navigasi Contact.SalesOrderHeader digunakan untuk mendapatkan kumpulan objek SalesOrderHeader untuk setiap kontak. Metode Sum menggunakan properti navigasi Contact.SalesOrderHeader untuk menjumlahkan total semua pesanan untuk setiap kontak.
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
Contoh 2
Contoh berikut mendapatkan semua pesanan kontak yang nama belakangnya adalah "Zhou". Properti navigasi Contact.SalesOrderHeader digunakan untuk mendapatkan kumpulan objek SalesOrderHeader untuk setiap kontak. Nama kontak dan pesanan ditampilkan dalam jenis anonim.
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
Contoh 3
Contoh berikut menggunakan properti navigasi SalesOrderHeader.Address dan SalesOrderHeader.Contact untuk mendapatkan kumpulan objek Address dan Contact yang terkait dengan setiap pesanan. Nama belakang kontak, alamat jalan, nomor pesanan penjualan, dan jumlah total yang harus dibayar untuk setiap pesanan ke kota Seattle ditampilkan dalam jenis anonim.
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
Contoh 4
Contoh berikut menggunakan metode Where untuk menemukan pesanan yang dibuat setelah 1 Desember 2003, lalu menggunakan properti navigasi order.SalesOrderDetail untuk mendapatkan detail setiap pesanan.
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