Mengelompokkan Elemen dalam Urutan

Operator GroupBy mengelompokkan elemen urutan. Contoh berikut menggunakan database Northwind.

Nota

Nilai kolom null dalam kueri GroupBy terkadang dapat menimbulkan InvalidOperationException. Untuk informasi selengkapnya, lihat bagian "GroupBy InvalidOperationException" pada Pemecahan Masalah.

Contoh 1

Contoh berikut mempartisi Products dengan menggunakan CategoryID.

IQueryable<IGrouping<Int32?, Product>> prodQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    select grouping;

foreach (IGrouping<Int32?, Product> grp in prodQuery)
{
    Console.WriteLine($"\nCategoryID Key = {grp.Key}:");
    foreach (Product listing in grp)
    {
        Console.WriteLine($"\t{listing.ProductName}");
    }
}

Dim prodQuery = From prod In db.Products _
                Group prod By prod.CategoryID Into grouping = Group

For Each grp In prodQuery
    Console.WriteLine(vbNewLine & "CategoryID Key = {0}:", _
        grp.CategoryID)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & listing.ProductName)
    Next
Next

Contoh 2

Contoh berikut menggunakan Max untuk menemukan harga satuan maksimum untuk setiap CategoryID.

var q =
    from p in db.Products
    group p by p.CategoryID into g
    select new
    {
        g.Key,
        MaxPrice = g.Max(p => p.UnitPrice)
    };

Dim query = From p In db.Products _
            Group p By p.CategoryID Into g = Group _
            Select CategoryID, MaxPrice = g.Max(Function(p) p.UnitPrice)

Contoh 3

Contoh berikut menggunakan rata-rata untuk menemukan rata-rata UnitPrice untuk setiap CategoryID.

var q2 =
    from p in db.Products
    group p by p.CategoryID into g
    select new
    {
        g.Key,
        AveragePrice = g.Average(p => p.UnitPrice)
    };

Dim q2 = From p In db.Products _
         Group p By p.CategoryID Into g = Group _
         Select CategoryID, AveragePrice = g.Average(Function(p) _
        p.UnitPrice)

Contoh 4

Contoh berikut menggunakan Sum untuk menemukan total UnitPrice untuk setiap CategoryID.

var priceQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    select new
    {
        grouping.Key,
        TotalPrice = grouping.Sum(p => p.UnitPrice)
    };

foreach (var grp in priceQuery)
{
    Console.WriteLine("Category = {0}, Total price = {1}",
        grp.Key, grp.TotalPrice);
}

Dim priceQuery = From prod In db.Products _
                 Group prod By prod.CategoryID Into grouping = Group _
                 Select CategoryID, TotalPrice = grouping.Sum(Function(p) _
        p.UnitPrice)

For Each grp In priceQuery
    Console.WriteLine("Category = {0}, Total price = {1}", _
        grp.CategoryID, grp.TotalPrice)
Next

Contoh 5

Contoh berikut ini menggunakan Count untuk menentukan jumlah Products yang dihentikan di setiap CategoryID.

var disconQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    select new
    {
        grouping.Key,
        NumProducts = grouping.Count(p => p.Discontinued)
    };

foreach (var prodObj in disconQuery)
{
    Console.WriteLine("CategoryID = {0}, Discontinued# = {1}",
        prodObj.Key, prodObj.NumProducts);
}

Dim disconQuery = From prod In db.Products _
                  Group prod By prod.CategoryID Into grouping = Group _
                  Select CategoryID, NumProducts = grouping.Count(Function(p) _
        p.Discontinued)

For Each prodObj In disconQuery
    Console.WriteLine("CategoryID = {0}, Discontinued# = {1}", _
        prodObj.CategoryID, prodObj.NumProducts)
Next

Contoh 6

Contoh berikut menggunakan klausa where berikut untuk menemukan semua kategori yang memiliki setidaknya 10 produk.

var prodCountQuery =
    from prod in db.Products
    group prod by prod.CategoryID into grouping
    where grouping.Count() >= 10
    select new
    {
        grouping.Key,
        ProductCount = grouping.Count()
    };

foreach (var prodCount in prodCountQuery)
{
    Console.WriteLine("CategoryID = {0}, Product count = {1}",
        prodCount.Key, prodCount.ProductCount);
}

Dim prodCountQuery = From prod In db.Products _
                     Group prod By prod.CategoryID Into grouping = Group _
                     Where grouping.Count >= 10 _
                     Select CategoryID, ProductCount = grouping.Count

For Each prodCount In prodCountQuery
    Console.WriteLine("CategoryID = {0}, Product count = {1}", _
        prodCount.CategoryID, prodCount.ProductCount)
Next

Contoh 7

Contoh berikut mengelompokkan produk menurut CategoryID dan SupplierID.

var prodQuery =
    from prod in db.Products
    group prod by new
    {
        prod.CategoryID,
        prod.SupplierID
    }
    into grouping
    select new { grouping.Key, grouping };

foreach (var grp in prodQuery)
{
    Console.WriteLine("\nCategoryID {0}, SupplierID {1}",
        grp.Key.CategoryID, grp.Key.SupplierID);
    foreach (var listing in grp.grouping)
    {
        Console.WriteLine($"\t{listing.ProductName}");
    }
}

Dim prodQuery = From prod In db.Products _
                Group prod By Key = New With {prod.CategoryID, prod.SupplierID} _
                    Into grouping = Group

For Each grp In prodQuery
    Console.WriteLine(vbNewLine & "CategoryID {0}, SupplierID {1}", _
        grp.Key.CategoryID, grp.Key.SupplierID)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & listing.ProductName)
    Next
Next

Contoh 8

Contoh berikut mengembalikan dua urutan produk. Urutan pertama berisi produk dengan harga satuan kurang dari atau sama dengan 10. Urutan kedua berisi produk dengan harga satuan lebih besar dari 10.

var priceQuery =
    from prod in db.Products
    group prod by new
    {
        Criterion = prod.UnitPrice > 10
    }
    into grouping
    select grouping;

foreach (var prodObj in priceQuery)
{
    if (prodObj.Key.Criterion == false)
        Console.WriteLine("Prices 10 or less:");
    else
        Console.WriteLine("\nPrices greater than 10");
    foreach (var listing in prodObj)
    {
        Console.WriteLine("{0}, {1}", listing.ProductName,
            listing.UnitPrice);
    }
}

Dim priceQuery = From prod In db.Products _
                 Group prod By Key = New With {.Criterion = prod.UnitPrice > 10} _
                     Into grouping = Group Select Key, grouping

For Each prodObj In priceQuery
    If prodObj.Key.Criterion = False Then
        Console.WriteLine("Prices 10 or less:")
    Else
        Console.WriteLine("\nPrices greater than 10")
        For Each listing In prodObj.grouping
            Console.WriteLine("{0}, {1}", listing.ProductName, _
                listing.UnitPrice)
        Next
    End If
Next

Contoh

Operator GroupBy hanya dapat mengambil satu argumen kunci. Jika Anda perlu mengelompokkan menurut lebih dari satu kunci, Anda harus membuat jenis anonim, seperti dalam contoh berikut:

var custRegionQuery =
    from cust in db.Customers
    group cust.ContactName by new { City = cust.City, Region = cust.Region };

foreach (var grp in custRegionQuery)
{
    Console.WriteLine($"\nLocation Key: {grp.Key}");
    foreach (var listing in grp)
    {
        Console.WriteLine($"\t{listing}");
    }
}

Dim custRegionQuery = From cust In db.Customers _
                      Group cust.ContactName By Key = New With _
                          {cust.City, cust.Region} Into grouping = Group

For Each grp In custRegionQuery
    Console.WriteLine(vbNewLine & "Location Key: {0}", grp.Key)
    For Each listing In grp.grouping
        Console.WriteLine(vbTab & "{0}", listing)
    Next
Next

Lihat juga

Last updated on 2025-03-29

Bagikan melalui