Export (0) Print
Expand All

Query Expression Syntax Examples: Grouping

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:


Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization
    

Example

The following example returns Address objects grouped by postal code. The results are projected into an anonymous type.


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
    

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 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
    

Example

The following example returns SalesOrderHeader objects grouped by customer ID. The number of sales for each customer is also returned.


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
    

See Also




Build Date:

2012-10-01
Show:
© 2015 Microsoft