Contoh Sintaks Kueri Berbasis Metode: Pengelompokan

Contoh dalam topik ini menunjukkan cara menggunakan metode GroupBy untuk mengkueri Model Penjualan AdventureWorks menggunakan sintaks kueri berbasis metode. Model Penjualan AdventureWorks yang digunakan dalam contoh ini dibuat dari tabel Kontak, Alamat, Produk, SalesOrderHeader, dan SalesOrderDetail dalam database sampel AdventureWorks.

Contoh dalam topik ini menggunakan pernyataan using/Imports berikut:

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

Contoh 1

Contoh berikut menggunakan metode GroupBy untuk menampilkan objek Address yang dikelompokkan berdasarkan kode pos. Hasilnya diproyeksikan ke dalam jenis anonim.

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

    foreach (IGrouping<string, Address> addressGroup in query)
    {
        Console.WriteLine("Postal Code: {0}", 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

Contoh 2

Contoh berikut menggunakan metode GroupBy untuk menampilkan objek Contact yang dikelompokkan berdasarkan huruf pertama dari nama belakang kontak. Hasilnya juga diurutkan berdasarkan huruf pertama dari nama belakang dan diproyeksikan menjadi jenis anonim.

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 '{0}':",
            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

Contoh 3

Contoh berikut menggunakan metode GroupBy untuk menampilkan objek SalesOrderHeader yang dikelompokkan berdasarkan ID pelanggan. Jumlah penjualan untuk setiap pelanggan juga ditampilkan.

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

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

        foreach (SalesOrderHeader sale in group)
        {
            Console.WriteLine("   Sale ID: {0}", 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

Lihat juga

• Kueri dalam LINQ ke Entitas