Exemples de syntaxe de requête fondée sur une méthode : opérateurs d'agrégation
Les exemples de cette rubrique montrent comment utiliser les méthodes Aggregate, Average, Count, LongCount, Max, Min et Sum pour interroger le modèle de ventes AdventureWorks à 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
Moyenne
Exemple
L'exemple ci-dessous utilise la méthode Average pour trouver le prix moyen courant des produits.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
Decimal averageListPrice =
products.Average(product => product.ListPrice);
Console.WriteLine("The average list price of all the products is ${0}",
averageListPrice);
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim averageListPrice As Decimal = _
products.Average(Function(prod) prod.ListPrice)
Console.WriteLine("The average list price of all the products is ${0}", _
averageListPrice)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Average pour trouver le prix moyen courant des produits de chaque style.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
var query = from product in products
group product by product.Style into g
select new
{
Style = g.Key,
AverageListPrice =
g.Average(product => product.ListPrice)
};
foreach (var product in query)
{
Console.WriteLine("Product style: {0} Average list price: {1}",
product.Style, product.AverageListPrice);
}
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim query = _
From prod In products _
Let styl = prod.Style _
Group prod By styl Into g = Group _
Select New With _
{ _
.Style = styl, _
.AverageListPrice = g.Average(Function(p) p.ListPrice) _
}
For Each prod In query
Console.WriteLine("Product style: {0} Average list price: {1}", _
prod.Style, prod.AverageListPrice)
Next
End Using
Exemple
L'exemple ci-dessous utilise la méthode Average pour trouver le montant total moyen dû.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
Decimal averageTotalDue = orders.Average(order => order.TotalDue);
Console.WriteLine("The average TotalDue is {0}.", averageTotalDue);
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim averageTotalDue As Decimal = _
orders.Average(Function(ord) ord.TotalDue)
Console.WriteLine("The average TotalDue is {0}.", averageTotalDue)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Average pour obtenir le montant total moyen dû pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
averageTotalDue = g.Average(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
order.Category, order.averageTotalDue);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Select New With _
{ _
.Category = contID, _
.averageTotalDue = _
g.Average(Function(ord) ord.TotalDue) _
}
For Each ord In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Average TotalDue = {1}", _
ord.Category, ord.averageTotalDue)
Next
End Using
Exemple
L'exemple ci-dessous utilise la méthode Average pour obtenir les commandes présentant le montant total moyen dû pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
let averageTotalDue = g.Average(order => order.TotalDue)
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.TotalDue == averageTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
order.TotalDue, order.SalesOrderID);
}
Console.Write("\n");
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Let averageTotalDue = g.Average(Function(ord) ord.TotalDue) _
Select New With _
{ _
.Category = contID, _
.CheapestProducts = _
g.Where(Function(ord) ord.TotalDue = averageTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: {0}", orderGroup.Category)
For Each ord In orderGroup.CheapestProducts
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
ord.TotalDue, ord.SalesOrderID)
Next
Console.Write(vbNewLine)
Next
End Using
Count
Exemple
L'exemple ci-dessous utilise la méthode Count pour retourner le nombre de produits dans la table Product.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
int numProducts = products.Count();
Console.WriteLine("There are {0} products.", numProducts);
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim numProducts As Integer = products.Count()
Console.WriteLine("There are {0} products.", numProducts)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Count pour retourner une liste des ID de contact et du nombre de commandes de chacun d'eux.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
//Can't find field SalesOrderContact
var query =
from contact in contacts
select new
{
CustomerID = contact.ContactID,
OrderCount = contact.SalesOrderHeaders.Count()
};
foreach (var contact in query)
{
Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
contact.CustomerID,
contact.OrderCount);
}
}
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim query = _
From cont In contacts _
Select New With _
{ _
.CustomerID = cont.ContactID, _
.OrderCount = cont.SalesOrderHeaders.Count() _
}
For Each cont In query
Console.WriteLine("CustomerID = {0} OrderCount = {1}", _
cont.CustomerID, cont.OrderCount)
Next
End Using
Exemple
L'exemple ci-dessous regroupe les produits par couleur et utilise la méthode Count pour retourner le nombre de produits dans chaque groupe de couleur.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Product> products = context.Products;
var query =
from product in products
group product by product.Color into g
select new { Color = g.Key, ProductCount = g.Count() };
foreach (var product in query)
{
Console.WriteLine("Color = {0} \t ProductCount = {1}",
product.Color,
product.ProductCount);
}
}
Using context As New AdventureWorksEntities
Dim products As ObjectSet(Of Product) = context.Products
Dim query = _
From prod In products _
Let pc = prod.Color _
Group prod By pc Into g = Group _
Select New With {.Color = pc, .ProductCount = g.Count()}
For Each prod In query
Console.WriteLine("Color = {0} " & vbTab & " ProductCount = {1}", _
prod.Color, prod.ProductCount)
Next
End Using
LongCount
Exemple
L'exemple ci-dessous obtient le nombre de contacts sous la forme d'un entier long.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<Contact> contacts = context.Contacts;
long numberOfContacts = contacts.LongCount();
Console.WriteLine("There are {0} Contacts", numberOfContacts);
}
Using context As New AdventureWorksEntities
Dim contacts As ObjectSet(Of Contact) = context.Contacts
Dim numberOfContacts As Long = contacts.LongCount()
Console.WriteLine("There are {0} Contacts", numberOfContacts)
End Using
Max
Exemple
L'exemple ci-dessous utilise la méthode Max pour obtenir le montant total dû le plus élevé.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
Decimal maxTotalDue = orders.Max(w => w.TotalDue);
Console.WriteLine("The maximum TotalDue is {0}.",
maxTotalDue);
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim maxTotalDue As Decimal = _
orders.Max(Function(ord) ord.TotalDue)
Console.WriteLine("The maximum TotalDue is {0}.", maxTotalDue)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Max pour obtenir le montant total dû le plus élevé pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
maxTotalDue =
g.Max(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
order.Category, order.maxTotalDue);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Select New With _
{ _
.Category = contID, _
.MaxTotalDue = _
g.Max(Function(ord) ord.TotalDue) _
}
For Each ord In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Maximum TotalDue = {1}", _
ord.Category, ord.MaxTotalDue)
Next
End Using
Exemple
L'exemple ci-dessous utilise la méthode Max pour obtenir les commandes présentant le montant total dû le plus élevé pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
let maxTotalDue = g.Max(order => order.TotalDue)
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.TotalDue == maxTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
order.TotalDue,
order.SalesOrderID);
}
Console.Write("\n");
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Let maxTotalDue = g.Max(Function(ord) ord.TotalDue) _
Select New With _
{ _
.Category = contID, _
.CheapestProducts = _
g.Where(Function(ord) ord.TotalDue = maxTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: {0}", orderGroup.Category)
For Each ord In orderGroup.CheapestProducts
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ", _
ord.TotalDue, ord.SalesOrderID)
Next
Console.Write(vbNewLine)
Next
End Using
Min
Exemple
L'exemple ci-dessous utilise la méthode Min pour obtenir le montant total dû le plus bas.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
Decimal smallestTotalDue = orders.Min(totalDue => totalDue.TotalDue);
Console.WriteLine("The smallest TotalDue is {0}.",
smallestTotalDue);
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim smallestTotalDue As Decimal = _
orders.Min(Function(totDue) totDue.TotalDue)
Console.WriteLine("The smallest TotalDue is {0}.", _
smallestTotalDue)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Min pour obtenir le montant total dû le plus bas pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
smallestTotalDue =
g.Min(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
order.Category, order.smallestTotalDue);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Select New With _
{ _
.Category = contID, _
.smallestTotalDue = _
g.Min(Function(o) o.TotalDue) _
}
For Each ord In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Minimum TotalDue = {1}", ord.Category, ord.smallestTotalDue)
Next
End Using
Exemple
L'exemple ci-dessous utilise la méthode Min pour obtenir les commandes présentant le montant total dû le plus bas pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
let minTotalDue = g.Min(order => order.TotalDue)
select new
{
Category = g.Key,
smallestTotalDue =
g.Where(order => order.TotalDue == minTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.smallestTotalDue)
{
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ",
order.TotalDue,
order.SalesOrderID);
}
Console.Write("\n");
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Let minTotalDue = g.Min(Function(o) o.TotalDue) _
Select New With _
{ _
.Category = contID, _
.smallestTotalDue = _
g.Where(Function(o) o.TotalDue = minTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: {0}", orderGroup.Category)
For Each ord In orderGroup.smallestTotalDue
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ", _
ord.TotalDue, ord.SalesOrderID)
Next
Console.Write(vbNewLine)
Next
End Using
Sum
Exemple
L'exemple ci-dessous utilise la méthode Sum pour obtenir le total des quantités commandées de la table SalesOrderDetail.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderDetail> orders = context.SalesOrderDetails;
double totalOrderQty = orders.Sum(o => o.OrderQty);
Console.WriteLine("There are a total of {0} OrderQty.",
totalOrderQty);
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails
Dim totalOrderQty As Double = orders.Sum(Function(o) o.OrderQty)
Console.WriteLine("There are a total of {0} OrderQty.", _
totalOrderQty)
End Using
Exemple
L'exemple ci-dessous utilise la méthode Sum pour obtenir le montant total dû pour chaque ID de contact.
using (AdventureWorksEntities context = new AdventureWorksEntities())
{
ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;
var query =
from order in orders
group order by order.Contact.ContactID into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.TotalDue)
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
}
Using context As New AdventureWorksEntities
Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders
Dim query = _
From ord In orders _
Let contID = ord.Contact.ContactID _
Group ord By contID Into g = Group _
Select New With _
{ _
.Category = contID, _
.TotalDue = g.Sum(Function(o) o.TotalDue) _
}
For Each ord In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" TotalDue sum = {1}", ord.Category, ord.TotalDue)
Next
End Using