How to: Group Data (Entity Framework)

This topic shows how to group query results. The example returns a set of nested data records that contain the Contact.LastName column, grouped and sorted alphabetically by the first letter of Contact.LastName. The same example is shown using each of the following Entity Framework query technologies:

• LINQ to Entities

• Entity SQL with ObjectQuery<T>

• Query builder methods of ObjectQuery<T>

The examples in this topic are based on the Adventure Works Sales Model. To run the code in this example, you must have already added the AdventureWorks Sales Model to your project and configured your project to use the Entity Framework. To do this, complete the procedures in How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework). You can also use the Entity Data Model Wizard to define the AdventureWorks Sales Model. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework).

Example

The following is the LINQ to Entities example.

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

The following is the Entity SQL example.

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

The following is the query builder method example.

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

See Also

Other Resources

Querying an Entity Data Model (Entity Framework Tasks)