0 out of 2 rated this helpful - Rate this topic

Query Expression Syntax Examples: Grouping

.NET Framework 4.5

The examples in this topic demonstrate how to use the GroupBy method to query the AdventureWorks Sales Model using query expression syntax. The AdventureWorks Sales model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

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;

Example

The following example returns Address objects grouped by postal code. 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);
}
}
}

Example

The following example returns Contact objects grouped by the first letter of the contact's last name. 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);
}
}
}

Example

The following example returns SalesOrderHeader objects grouped by customer ID. 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("");
}
}

Query Expression Syntax Examples: Grouping

Example

Example

Example

See Also

Concepts