Ejemplos de sintaxis de expresiones de consulta: AgruparQuery Expression Syntax Examples: Grouping

Los ejemplos de este tema muestran cómo usar el GroupBy método para consultar el modelo AdventureWorks Sales con la sintaxis de expresión de consulta.The examples in this topic demonstrate how to use the GroupBy method to query the AdventureWorks Sales Model using query expression syntax. El modelo AdventureWorks Sales que se usa en estos ejemplos se crea a partir de las tablas Contact, Address, Product, SalesOrderHeader y SalesOrderDetail en la base de datos de ejemplo AdventureWorks.The AdventureWorks Sales model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

Los ejemplos de este tema usan los siguientes using / Imports instrucciones:The examples in this topic use the following using/Imports statements:

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

EjemploExample

El ejemplo siguiente devuelve los objetos Address agrupados por código postal.The following example returns Address objects grouped by postal code. Los resultados se proyectan en un tipo anónimo.The results are projected into an anonymous type.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query =
        from address in context.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);
        }
    }
}
Using context As New AdventureWorksEntities
    Dim addresses As ObjectSet(Of Address) = context.Addresses

    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

EjemploExample

El ejemplo siguiente devuelve los objetos Contact agrupados por la primera letra del apellido del contacto.The following example returns Contact objects grouped by the first letter of the contact's last name. Los resultados se ordenan también por la primera letra del apellido y se proyectan en un tipo anónimo.The results are also sorted by the first letter of last name and projected into an anonymous type.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = (
        from contact in context.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);
        }
    }
}
Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts

    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

EjemploExample

En el ejemplo siguiente se devuelven objetos SalesOrderHeader agrupados por identificador de cliente.The following example returns SalesOrderHeader objects grouped by customer ID. También se devuelve el número de ventas para cada cliente.The number of sales for each customer is also returned.

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = from order in context.SalesOrderHeaders
                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("");
    }
}
Using context As New AdventureWorksEntities
    Dim salesOrders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    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

Vea tambiénSee also