Exemples de syntaxe de requête fondée sur une méthode : opérateurs de jointure
Les exemples de cette rubrique montrent comment utiliser les méthodes Join et GroupJoin pour interroger le modèle de vente AdventureWorks Sales Model à l’aide de la syntaxe de requête fondée sur une méthode. Le modèle de vente AdventureWorks Sales Model utilisé dans ces exemples est construit à partir des tables Contact, Address, Product, SalesOrderHeader et SalesOrderDetail de l'exemple de base de données AdventureWorks.
Les exemples de cette rubrique utilisent les instructions using/Imports suivantes :
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
Exemple
L'exemple ci-dessous effectue une jointure GroupJoin sur les tables SalesOrderHeader et SalesOrderDetail pour trouver le nombre de commandes par client. Une jointure de groupe est l'équivalent d'une jointure externe gauche qui retourne chaque élément de la première source de données (gauche), même s'il n'y a pas d'éléments corrélés dans l'autre source de données.
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
Exemple
L'exemple ci-dessous effectue une jointure GroupJoin sur les tables Contact et SalesOrderHeader pour trouver le nombre de commandes par contact. Le nombre de commandes et les ID de chaque contact sont affichés.
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
Exemple
L'exemple ci-dessous effectue une jointure sur les tables Contact et SalesOrderHeader.
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
Exemple
L'exemple suivant effectue une jointure sur les tables Contact et SalesOrderHeader, en regroupant les résultats par ID de contact.
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