Compartir a través de


Cómo agrupar datos (Entity Framework)

En este tema se describe cómo agrupar los resultados de una consulta. En el ejemplo se devuelve un conjunto de registros de datos anidados que contienen la columna Contact.LastName, agrupados y ordenados alfabéticamente por la primera letra de esta columna. Se muestra el mismo ejemplo con cada una de las siguientes tecnologías de consulta de Entity Framework:

  • LINQ to Entities

  • Entity SQL con ObjectQuery<T>

  • Métodos del generador de consultas de ObjectQuery<T>

Los ejemplos de este tema se basan en el modelo AdventureWorks Sales. Para ejecutar el código de este ejemplo, debe haber agregado ya el modelo AdventureWorks Sales al proyecto y haber configurado el proyecto para usar Entity Framework. Para ello, complete los procedimientos de Cómo configurar manualmente un proyecto de Entity Framework y Cómo definir manualmente un modelo Entity Data Model (Entity Framework). También puede utilizar el Asistente para Entity Data Model con el fin de definir el modelo AdventureWorks Sales. Para obtener más información, vea Cómo usar el Asistente para Entity Data Model (Entity Framework).

Ejemplo

A continuación se muestra el ejemplo de LINQ to Entities.

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);
        }
    }
}

A continuación se muestra el ejemplo de Entity SQL.

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    string esqlQuery = @"SELECT ln, 
        (SELECT c1.LastName FROM AdventureWorksEntities.Contact 
            AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln) 
        AS CONTACT 
        FROM AdventureWorksEntities.CONTACT AS c2 GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln
        ORDER BY ln";
    try
    {
        foreach (DbDataRecord rec in
            new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
        {
            Console.WriteLine("Last names that start with the letter '{0}':",
                        rec[0]);
            List<DbDataRecord> list = rec[1] as List<DbDataRecord>;
            foreach (DbDataRecord nestedRec in list)
            {
                for (int i = 0; i < nestedRec.FieldCount; i++)
                {
                    Console.WriteLine("   {0} ", nestedRec[i]);
                }
            }
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

A continuación se muestra el ejemplo del método del generador de consultas.

Using advWorksContext As New AdventureWorksEntities()
    Try
        ' Define the query with a GROUP BY clause that returns
        ' a set of nested LastName records grouped by first letter.
        Dim query As ObjectQuery(Of DbDataRecord) = _
        advWorksContext.Contact _
        .GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln") _
        .Select("it.ln AS ln, (SELECT c1.LastName " + _
        "FROM AdventureWorksEntities.Contact AS c1 " + _
        "WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT") _
        .OrderBy("it.ln")

        ' Execute the query and walk through the nested records.
        For Each rec As DbDataRecord In query.Execute(MergeOption.AppendOnly)
            Console.WriteLine("Last names that start with the letter '0':", _
                        rec(0))
            Dim list As List(Of DbDataRecord) = CType(rec(1), List(Of DbDataRecord))
            For Each r As DbDataRecord In list
                For i As Integer = 0 To r.FieldCount - 1
                    Console.WriteLine("  {0}", r(i))
                Next i
            Next
        Next
    Catch ex As EntitySqlException
        Console.WriteLine(ex.ToString())
    End Try
End Using
using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    try
    {
        // Define the query with a GROUP BY clause that returns
        // a set of nested LastName records grouped by first letter.
        ObjectQuery<DbDataRecord> query =
            advWorksContext.Contact
            .GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln")    
            .Select("it.ln AS ln, (SELECT c1.LastName " +
            "FROM AdventureWorksEntities.Contact AS c1 " +
            "WHERE SubString(c1.LastName, 1, 1) = it.ln) AS CONTACT")
            .OrderBy("it.ln");

        // Execute the query and walk through the nested records.
        foreach (DbDataRecord rec in
            query.Execute(MergeOption.AppendOnly))
        {
            Console.WriteLine("Last names that start with the letter '{0}':",
                        rec[0]);
            List<DbDataRecord> list = rec[1] as List<DbDataRecord>;
            foreach (DbDataRecord r in list)
            {
                for (int i = 0; i < r.FieldCount; i++)
                {
                    Console.WriteLine("   {0} ", r[i]);
                }
            }
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

Vea también

Otros recursos

Consultar un Entity Data Model (tareas de Entity Framework)