在查詢過程中，聯結是一個重要的作業，特別是針對那些沒有直接關聯的數據來源，如關係資料庫的數據表。 將兩個數據源聯結起來，指的是將一個數據源中的物件與另一個數據源中具有共同屬性的物件進行關聯。 如需詳細資訊，請參閱 標準查詢運算元概觀。
本主題中的範例示範如何使用查詢表達式語法，使用 GroupJoin 和 Join 方法來查詢 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 =
from order in orders
join detail in details
on order.SalesOrderID
equals detail.SalesOrderID into orderGroup
select 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 = _
From order In orders _
Group Join detail In details _
On order.SalesOrderID _
Equals detail.SalesOrderID Into orderGroup = Group _
Select 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 =
from contact in contacts
join order in orders
on contact.ContactID
equals order.Contact.ContactID into contactGroup
select 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 = _
From contact In contacts _
Group Join order In orders _
On contact.ContactID _
Equals order.Contact.ContactID Into contactGroup = Group _
Select 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
加入
範例
下列範例會透過 SalesOrderHeader 和 SalesOrderDetail 數據表執行聯結，以從 8 月起取得在線訂單。
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;
var query =
from order in orders
join detail in details
on order.SalesOrderID equals detail.SalesOrderID
where order.OnlineOrderFlag == true
&& order.OrderDate.Month == 8
select new
{
SalesOrderID = order.SalesOrderID,
SalesOrderDetailID = detail.SalesOrderDetailID,
OrderDate = order.OrderDate,
ProductID = detail.ProductID
};
foreach (var order in query)
{
Console.WriteLine($"{order.SalesOrderID}\t{order.SalesOrderDetailID}\t{order.OrderDate:d}\t{order.ProductID}");
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim details As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails
Dim query = _
From ord In orders _
Join det In details _
On ord.SalesOrderID Equals det.SalesOrderID _
Where ord.OnlineOrderFlag = True _
And ord.OrderDate.Month = 8 _
Select New With _
{ _
.SalesOrderID = ord.SalesOrderID, _
.SalesOrderDetailID = det.SalesOrderDetailID, _
.OrderDate = ord.OrderDate, _
.ProductID = det.ProductID _
}
For Each ord In query
Console.WriteLine("{0}" & vbTab & "{1}" & vbTab & "{2:d}" & vbTab & "{3}", _
ord.SalesOrderID, _
ord.SalesOrderDetailID, _
ord.OrderDate, _
ord.ProductID)
Next
End Using