How to: Return the Average Value from a Numeric Sequence (LINQ to SQL)
The Average operator computes the average of a sequence of numeric values.
Note
The LINQ to SQL translation of Average of integer values is computed as an integer, not as a double.
Example
The following example returns the average of Freight values in the Orders table.
Results from the sample Northwind database would be 78.2442.
Dim averageFreight = Aggregate ord In db.Orders _
Into Average(ord.Freight)
Console.WriteLine(averageFreight)
System.Nullable<Decimal> averageFreight =
(from ord in db.Orders
select ord.Freight)
.Average();
Console.WriteLine(averageFreight);
The following example returns the average of the unit price of all Products in the Products table.
Results from the sample Northwind database would be 28.8663.
Dim averageUnitPrice = Aggregate prod In db.Products _
Into Average(prod.UnitPrice)
Console.WriteLine(averageUnitPrice)
System.Nullable<Decimal> averageUnitPrice =
(from prod in db.Products
select prod.UnitPrice)
.Average();
Console.WriteLine(averageUnitPrice);
The following example uses the Average operator to find those Products whose unit price is higher than the average unit price of the category it belongs to. The example then displays the results in groups.
Note that this example requires the use of the var keyword in C#, because the return type is anonymous.
Dim priceQuery = From prod In db.Products() _
Group prod By prod.CategoryID Into grouping = Group _
Select CategoryID, _
ExpensiveProducts = _
(From prod2 In grouping _
Where prod2.UnitPrice > _
grouping.Average(Function(prod3) _
prod3.UnitPrice) _
Select prod2)
For Each grp In priceQuery
Console.WriteLine(grp.CategoryID)
For Each listing In grp.ExpensiveProducts
Console.WriteLine(listing.ProductName)
Next
Next
var priceQuery =
from prod in db.Products
group prod by prod.CategoryID into grouping
select new
{
grouping.Key,
ExpensiveProducts =
from prod2 in grouping
where prod2.UnitPrice > grouping.Average(prod3 =>
prod3.UnitPrice)
select prod2
};
foreach (var grp in priceQuery)
{
Console.WriteLine(grp.Key);
foreach (var listing in grp.ExpensiveProducts)
{
Console.WriteLine(listing.ProductName);
}
}
If you run this query against the Northwind sample database, the results should resemble of the following:
1
Côte de Blaye
Ipoh Coffee
2
Grandma's Boysenberry Spread
Northwoods Cranberry Sauce
Sirop d'érable
Vegie-spread
3
Sir Rodney's Marmalade
Gumbär Gummibärchen
Schoggi Schokolade
Tarte au sucre
4
Queso Manchego La Pastora
Mascarpone Fabioli
Raclette Courdavault
Camembert Pierrot
Gudbrandsdalsost
Mozzarella di Giovanni
5
Gustaf's Knäckebröd
Gnocchi di nonna Alice
Wimmers gute Semmelknödel
6
Mishi Kobe Niku
Thüringer Rostbratwurst
7
Rössle Sauerkraut
Manjimup Dried Apples
8
Ikura
Carnarvon Tigers
Nord-Ost Matjeshering
Gravad lax