シーケンスの要素のグループ化Group Elements in a Sequence

GroupBy 演算子はシーケンスの要素をグループ化します。The GroupBy operator groups the elements of a sequence. Northwind データベースを使用する例を次に示します。The following examples use the Northwind database.

注意

null 列値が GroupBy クエリに含まれると、InvalidOperationException がスローされることがあります。Null column values in GroupBy queries can sometimes throw an InvalidOperationException. 詳しくは、「トラブルシューティング」の「GroupBy InvalidOperationException」をご覧ください。For more information, see the "GroupBy InvalidOperationException" section of Troubleshooting.

Example

次の例では、Products を基準として CategoryID をグループ化しています。The following example partitions Products by 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 = {0}:", grp.Key);
    foreach (Product listing in grp)
    {
        Console.WriteLine("\t{0}", 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

Example

Max を使用して各 CategoryID の最も高い単価を調べる例を次に示します。The following example uses Max to find the maximum unit price for each 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)

Example

Average を使用して各 UnitPriceCategoryID の平均値を求める例を次に示します。The following example uses Average to find the average UnitPrice for each 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)

Example

Sum を使用して各 UnitPriceCategoryID の合計値を求める例を次に示します。The following example uses Sum to find the total UnitPrice for each 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

Example

Count を使用して各 Products に含まれる生産中止の CategoryID の数を調べる例を次に示します。The following example uses Count to find the number of discontinued Products in each 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

Example

where 句を使用して最低 10 種類の製品が含まれるすべてのカテゴリを調べる方法を次の例に示します。The following example uses a following where clause to find all categories that have at least 10 products.

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

Example

CategoryIDSupplierID を使用して製品をグループ化する例を次に示します。The following example groups products by CategoryID and 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{0}", 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

Example

次の例は、製品の 2 つのシーケンスを返します。The following example returns two sequences of products. 最初のシーケンスには、単価が 10 以下の製品が含まれます。The first sequence contains products with unit price less than or equal to 10. 2 番目のシーケンスには、単価が 10 よりも大きい製品が含まれます。The second sequence contains products with unit price greater than 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

Example

GroupBy 演算子は、単一キーの引数のみを受け取ります。The GroupBy operator can take only a single key argument. 複数のキーを使用してグループ化する場合は、次の例に示すように、匿名型を作成する必要があります。If you need to group by more than one key, you must create an anonymous type, as in the following example:

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: {0}", grp.Key);
    foreach (var listing in grp)
    {
        Console.WriteLine("\t{0}", 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

関連項目See also