Beispiele für die methodenbasierte Abfragesyntax: Joinoperatoren
In diesem Beispiel wird gezeigt, wie Sie mithilfe der Join- und der GroupJoin-Methode unter Verwendung der methodenbasierten Abfragesyntax das AdventureWorks Sales-Modell abfragen. Für das in den Beispielen verwendete "AdventureWorks Sales"-Modell wurde auf die Tabellen Contact, Address, Product, SalesOrderHeader und SalesOrderDetail der "AdventureWorks"-Beispieldatenbank zurückgegriffen.
Die Beispiele in diesem Thema beziehen sich auf die folgenden using/Imports-Anweisungen:
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
GroupJoin
Beispiel
Im folgenden Beispiel wird eine GroupJoin zwischen der SalesOrderHeader-Tabelle und der SalesOrderDetail-Tabelle durchgeführt, um nach der Anzahl der Aufträge pro Kunde zu suchen. Eine Gruppenverknüpfung ist das Äquivalent zu einer linken äußeren Verknüpfung, die jedes Element der ersten (linken) Datenquelle zurückgibt, selbst wenn die andere Datenquelle keine zugehörigen Elemente enthält.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
ObjectSet<SalesOrderDetail> details = context.SalesOrderDetails;
var query = orders.GroupJoin(details,
order => order.SalesOrderID,
detail => detail.SalesOrderID,
(order, orderGroup) => new
{
CustomerID = order.SalesOrderID,
OrderCount = orderGroup.Count()
});
foreach (var order in query)
{
Console.WriteLine("CustomerID: {0} Orders Count: {1}",
order.CustomerID,
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 = orders.GroupJoin(details, _
Function(order) order.SalesOrderID, _
Function(detail) detail.SalesOrderID, _
Function(order, orderGroup) 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
Beispiel
Im folgenden Beispiel wird ein GroupJoin zwischen der Contact-Tabelle und der SalesOrderHeader-Tabelle durchgeführt, um nach der Anzahl der Aufträge pro Kontakt zu suchen. Die Anzahl der Aufträge und die IDs für alle Kontakte werden angezeigt.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query = contacts.GroupJoin(orders,
contact => contact.ContactID,
order => order.Contact.ContactID,
(contact, contactGroup) => new
{
ContactID = contact.ContactID,
OrderCount = contactGroup.Count(),
Orders = contactGroup
});
foreach (var group in query)
{
Console.WriteLine("ContactID: {0}", group.ContactID);
Console.WriteLine("Order count: {0}", group.OrderCount);
foreach (var orderInfo in group.Orders)
{
Console.WriteLine(" Sale ID: {0}", 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 = contacts.GroupJoin(orders, _
Function(contact) contact.ContactID, _
Function(order) order.Contact.ContactID, _
Function(contact, contactGroup) 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
Join
Beispiel
Im folgenden Beispiel werden die Tabellen Contact und SalesOrderHeader miteinander verknüpft.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
contacts.Join(
orders,
order => order.ContactID,
contact => contact.Contact.ContactID,
(contact, order) => new
{
ContactID = contact.ContactID,
SalesOrderID = order.SalesOrderID,
FirstName = contact.FirstName,
Lastname = contact.LastName,
TotalDue = order.TotalDue
});
foreach (var contact_order in query)
{
Console.WriteLine("ContactID: {0} "
+ "SalesOrderID: {1} "
+ "FirstName: {2} "
+ "Lastname: {3} "
+ "TotalDue: {4}",
contact_order.ContactID,
contact_order.SalesOrderID,
contact_order.FirstName,
contact_order.Lastname,
contact_order.TotalDue);
}
}
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
contacts.Join( _
orders, _
Function(ord) ord.ContactID, _
Function(cont) cont.Contact.ContactID, _
Function(cont, ord) New With _
{ _
.ContactID = cont.ContactID, _
.SalesOrderID = ord.SalesOrderID, _
.FirstName = cont.FirstName, _
.Lastname = cont.LastName, _
.TotalDue = ord.TotalDue _
})
For Each contact_order In query
Console.WriteLine("ContactID: {0} " _
& "SalesOrderID: {1} " & "FirstName: {2} " _
& "Lastname: {3} " & "TotalDue: {4}", _
contact_order.ContactID, _
contact_order.SalesOrderID, _
contact_order.FirstName, _
contact_order.Lastname, _
contact_order.TotalDue)
Next
End Using
Beispiel
Im folgenden Beispiel werden die Tabellen Contact und SalesOrderHeader miteinander verknüpft, und die Ergebnisse werden nach der Kontakt-ID gruppiert.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query = contacts.Join(
orders,
order => order.ContactID,
contact => contact.Contact.ContactID,
(contact, order) => new
{
ContactID = contact.ContactID,
SalesOrderID = order.SalesOrderID,
FirstName = contact.FirstName,
Lastname = contact.LastName,
TotalDue = order.TotalDue
})
.GroupBy(record => record.ContactID);
foreach (var group in query)
{
foreach (var contact_order in group)
{
Console.WriteLine("ContactID: {0} "
+ "SalesOrderID: {1} "
+ "FirstName: {2} "
+ "Lastname: {3} "
+ "TotalDue: {4}",
contact_order.ContactID,
contact_order.SalesOrderID,
contact_order.FirstName,
contact_order.Lastname,
contact_order.TotalDue);
}
}
}
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
contacts.Join( _
orders, _
Function(ord) ord.ContactID, _
Function(cont) cont.Contact.ContactID, _
Function(cont, ord) New With _
{ _
.ContactID = cont.ContactID, _
.SalesOrderID = ord.SalesOrderID, _
.FirstName = cont.FirstName, _
.Lastname = cont.LastName, _
.TotalDue = ord.TotalDue _
}) _
.GroupBy(Function(record) record.ContactID)
For Each group In query
For Each contact_order In group
Console.WriteLine("ContactID: {0} " _
& "SalesOrderID: {1} " & "FirstName: {2} " _
& "Lastname: {3} " & "TotalDue: {4}", _
contact_order.ContactID, _
contact_order.SalesOrderID, _
contact_order.FirstName, _
contact_order.Lastname, _
contact_order.TotalDue)
Next
Next
End Using