Group Elements in a Sequence
The GroupBy operator groups the elements of a sequence. The following examples use the Northwind database.
Note
Null column values in GroupBy queries can sometimes throw an InvalidOperationException. For more information, see the "GroupBy InvalidOperationException" section of Troubleshooting.
Example 1
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 2
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 3
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 4
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 5
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 6
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 7
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 8
The following example returns two sequences of products. The first sequence contains products with unit price less than or equal to 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
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