方法 : シーケンスの要素をグループ化する (LINQ to SQL)

[アーティクル]
04/27/2011

GroupBy 演算子はシーケンスの要素をグループ化します。 Northwind データベースを使用する例を次に示します。

メモ
null 列値が GroupBy クエリに含まれると、InvalidOperationException がスローされることがあります。詳細については、「トラブルシューティング (LINQ to SQL)」の「GroupBy InvalidOperationException」を参照してください。

使用例

次の例では、CategoryID を基準として Products をグループ化しています。

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

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);
    }
}

Max を使用して各 CategoryID の最も高い単価を調べる例を次に示します。

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

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

Average を使用して各 CategoryID の 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)

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

Sum を使用して各 CategoryID の UnitPrice の合計値を求める例を次に示します。

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

            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);
            }

Count を使用して各 CategoryID に含まれる生産中止の Products の数を調べる例を次に示します。

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

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);
}

where 句を使用して最低 10 種類の製品が含まれるすべてのカテゴリを調べる方法を次の例に示します。

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

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);
}

CategoryID と SupplierID を使用して製品をグループ化する例を次に示します。

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

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);
    }
}

次の例は、製品の 2 つのシーケンスを返します。最初のシーケンスには、単価が 10 以下の製品が含まれます。 2 番目のシーケンスには、単価が 10 よりも大きい製品が含まれます。

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

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);
    }
}

GroupBy 演算子は、単一キーの引数のみを受け取ります。複数のキーを使用してグループ化する場合は、次の例に示すように、匿名型を作成する必要があります。

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

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);
    }
}

次の方法で共有

方法 : シーケンスの要素をグループ化する (LINQ to SQL)

使用例

参照

概念

その他の技術情報

その他のリソース