Exemples de syntaxe d'expression de requête : regroupement (LINQ to Entities)

Les exemples de cette rubrique montrent comment utiliser la méthode GroupBy pour interroger le modèle de vente AdventureWorks Sales Model à l'aide de la syntaxe d'expression de requête. 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 :

Option Explicit On
Option Strict On
Imports L2EExamplesVB.AdventureWorksModel
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 AdventureWorksModel;
using System.Globalization;

Pour plus d'informations, voir Procédure : créer un projet LINQ to Entities dans Visual Studio.

Exemple

L'exemple ci-dessous retourne des objets Address regroupés selon le code postal. Les résultats sont projetés dans un type anonyme.

Using AWEntities As New AdventureWorksEntities
    Dim addresses As ObjectQuery(Of Address) = AWEntities.Address

    Dim query = _
        From adrs In addresses _
        Group adrs By adrs.PostalCode Into g = Group _
        Select New With {.PostalCode = PostalCode, .AddressLine = g}

    For Each addressGroup In query
        Console.WriteLine("Postal Code: {0}", addressGroup.PostalCode)
        For Each adrs In addressGroup.AddressLine
            Console.WriteLine(vbTab & adrs.AddressLine1 & _
                adrs.AddressLine2)
        Next
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Address> addresses = AWEntities.Address;

    var query =
        from address in addresses
        group address by address.PostalCode into addressGroup
        select new { PostalCode = addressGroup.Key, 
                     AddressLine = addressGroup };

    foreach (var addressGroup in query)
    {
        Console.WriteLine("Postal Code: {0}", addressGroup.PostalCode);
        foreach (var address in addressGroup.AddressLine)
        {
            Console.WriteLine("\t" + address.AddressLine1 +
                address.AddressLine2);
        }
    }
}

Exemple

L'exemple ci-dessous retourne des objets Contact regroupés selon la première lettre du nom de famille des contacts. Les résultats sont également triés selon la première lettre du nom de famille et projetés dans un type anonyme.

Using AWEntities As New AdventureWorksEntities
    Dim contacts As ObjectQuery(Of Contact) = AWEntities.Contact

    Dim query = ( _
        From contact In contacts _
        Group By firstLetter = contact.LastName.Substring(0, 1) _
        Into contactGroup = Group _
        Select New With {.FirstLetter = firstLetter, .Names = contactGroup}) _
        .OrderBy(Function(letter) letter.FirstLetter)

    For Each n In query
        Console.WriteLine("Last names that start with the letter '{0}':", _
            n.FirstLetter)
        For Each name In n.Names
            Console.WriteLine(name.LastName)
        Next
    Next
End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Contact> contacts = AWEntities.Contact;

    var query = (
        from contact in contacts
        group contact by contact.LastName.Substring(0, 1) into contactGroup
        select new { FirstLetter = contactGroup.Key, Names = contactGroup }).
            OrderBy(letter => letter.FirstLetter);

    foreach (var contact in query)
    {
        Console.WriteLine("Last names that start with the letter '{0}':",
            contact.FirstLetter);
        foreach (var name in contact.Names)
        {
            Console.WriteLine(name.LastName);
        }
    }
}

Exemple

L'exemple ci-dessous retourne des objets SalesOrderHeader regroupés par code client (Customer ID). Le nombre de ventes réalisées pour chaque client est également retourné.

Using AWEntities As New AdventureWorksEntities
    Dim salesOrders As ObjectQuery(Of SalesOrderHeader) = AWEntities.SalesOrderHeader

    Dim query = From order In salesOrders _
                    Group order By order.CustomerID Into idGroup = Group, Count()

    For Each group In query
        Console.WriteLine("Customer ID: {0}", group.CustomerID)
        Console.WriteLine("Order Count: {0}", group.Count)

        For Each sale In group.idGroup
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID)
        Next

        Console.WriteLine("")
    Next


End Using

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> salesOrders = AWEntities.SalesOrderHeader;

    var query = from order in salesOrders
                group order by order.CustomerID into idGroup 
                select new {CustomerID = idGroup.Key, 
                    OrderCount = idGroup.Count(),
                    Sales = idGroup};

    foreach (var orderGroup in query)
    {
        Console.WriteLine("Customer ID: {0}", orderGroup.CustomerID);
        Console.WriteLine("Order Count: {0}", orderGroup.OrderCount);

        foreach (SalesOrderHeader sale in orderGroup.Sales)
        {
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID);
        }

        Console.WriteLine("");
    }
}

Voir aussi

Concepts

Exemples de syntaxe d'expression de requête (LINQ to Entities)