Export (0) Print
Expand All
Expand Minimize
1 out of 2 rated this helpful - Rate this topic

How to: Group Data

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:

The example in this topic is based on the Adventure Works Sales Model. To run the code in this topic, you must have already added the Adventure Works Sales Model to your project and configured your project to use the Entity Framework. For more information, see How to: Use the Entity Data Model Wizard (Entity Framework) or How to: Manually Configure an Entity Framework Project and How to: Manually Define an Entity Data Model (Entity Framework).

Example

The following is the LINQ to Entities example.

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

The following is the Entity SQL example.

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    string esqlQuery = @"SELECT ln, 
        (SELECT c1.LastName FROM AdventureWorksEntities.Contacts 
            AS c1 WHERE SUBSTRING(c1.LastName ,1,1) = ln) 
        AS CONTACT 
        FROM AdventureWorksEntities.Contacts AS c2 GROUP BY SUBSTRING(c2.LastName ,1,1) AS ln
        ORDER BY ln";

    foreach (DbDataRecord rec in
        new ObjectQuery<DbDataRecord>(esqlQuery, context))
    {
        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]);
            }
        }
    }
}

The following is the query builder method example.

using (AdventureWorksEntities context =
    new AdventureWorksEntities())
{
    // Define the query with a GROUP BY clause that returns
    // a set of nested LastName records grouped by first letter.
    ObjectQuery<DbDataRecord> query =
        context.Contacts
        .GroupBy("SUBSTRING(it.LastName, 1, 1) AS ln", "ln")
        .Select("it.ln AS ln, (SELECT c1.LastName " +
        "FROM AdventureWorksEntities.Contacts 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]);
            }
        }
    }
}

See Also

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.