Method-Based 查詢語法範例：群組

本主題中的範例示範如何使用方法型查詢語法，以 GroupBy 方法查詢 AdventureWorks Sales Model。 這些範例中使用的 AdventureWorks 銷售模型是從 AdventureWorks 範例資料庫中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 數據表所建置。

本主題中的範例會使用下列 using/Imports 語句：

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;

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

範例 1

下列範例會使用 GroupBy 方法來傳回以郵遞區編碼分組的 Address 物件。 結果會映射到匿名類型。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Addresses
        .GroupBy( address => address.PostalCode);

    foreach (IGrouping<string, Address> addressGroup in query)
    {
        Console.WriteLine($"Postal Code: {addressGroup.Key}");
        foreach (Address address in addressGroup)
        {
            Console.WriteLine("\t" + address.AddressLine1 +
                address.AddressLine2);
        }
    }
}

Using context As New AdventureWorksEntities
    Dim query = context.Addresses _
        .GroupBy(Function(Address) Address.PostalCode) _
        .Select(Function(Address) Address)

    For Each addressGroup As IGrouping(Of String, Address) In query
        Console.WriteLine("Postal Code: {0}", addressGroup.Key)
        For Each address As Address In addressGroup

            Console.WriteLine("   " + address.AddressLine1 + address.AddressLine2)
        Next
    Next
End Using

範例 2

下列範例會使用 GroupBy 方法來傳回 Contact 對象，這些物件是由聯繫人姓氏的第一個字母分組。 結果也會依姓氏的第一個字母排序，並投影成匿名類型。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.Contacts
        .GroupBy(c => c.LastName.Substring(0,1))
        .OrderBy(c => c.Key);

    foreach (IGrouping<string, Contact> group in query)
    {
        Console.WriteLine($"Last names that start with the letter '{group.Key}':");
        foreach (Contact contact in group)
        {
            Console.WriteLine(contact.LastName);
        }
    }
}

Using context As New AdventureWorksEntities

    Dim query = context.Contacts _
    .GroupBy(Function(c) c.LastName.Substring(0, 1)) _
    .OrderBy(Function(c) c.Key) _
    .Select(Function(c) c)

    For Each group As IGrouping(Of String, Contact) In query
        Console.WriteLine("Last names that start with the letter '{0}':", group.Key)

        For Each contact As Contact In group

            Console.WriteLine(contact.LastName)
        Next
    Next

End Using

範例 3

下列範例會使用 GroupBy 方法來傳回由客戶標識符分組 SalesOrderHeader 物件。 也會傳回每個客戶的銷售額。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    var query = context.SalesOrderHeaders
        .GroupBy(order => order.CustomerID);

    foreach (IGrouping<int, SalesOrderHeader> group in query)
    {
        Console.WriteLine($"Customer ID: {group.Key}");
        Console.WriteLine($"Order count: {group.Count()}");

        foreach (SalesOrderHeader sale in group)
        {
            Console.WriteLine($"   Sale ID: {sale.SalesOrderID}");
        }
        Console.WriteLine("");
    }
}

Using context As New AdventureWorksEntities

    Dim query = context.SalesOrderHeaders _
        .GroupBy(Function(order) order.CustomerID)

    ' Iterate over each IGrouping
    For Each group In query

        Console.WriteLine("Customer ID: {0}", group.Key)
        Console.WriteLine("Order Count: {0}", group.Count)

        For Each sale In group
            Console.WriteLine("   Sale ID: {0}", sale.SalesOrderID)
        Next

        Console.WriteLine("")

    Next

End Using

另請參閱

• LINQ to Entities 查詢