Exemples de syntaxe de requête fondée sur une méthode : opérateurs de jointure (LINQ to Entities)
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 fournis dans cette rubrique utilisent les instructions using/Imports suivantes :
Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
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;
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 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
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);
}
}
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 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
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("");
}
}
Join
Exemple
L'exemple ci-dessous effectue une jointure sur les tables Contact et SalesOrderHeader.
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
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);
}
}
Exemple
L'exemple suivant effectue une jointure sur les tables Contact et SalesOrderHeader, en regroupant les résultats par ID de contact.
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
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);
}
}
}