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

Voir aussi

• Requêtes dans LINQ to Entities