Method-Based Query Syntax Examples: Aggregate Operators (LINQ to DataSet)
The examples in this topic demonstrate how to use the Aggregate, Average, Count, LongCount, Max, Min, and Sum operators to query a DataSet and aggregate data using method query syntax.
The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.
The examples in this topic use the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.
The examples in this topic use the following using/Imports statements:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
Option Explicit On
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization
For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.
Aggregate
Example
This example uses the Aggregate method to get the first 5 contacts from the Contact table and build a comma-delimited list of the last names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
IEnumerable<DataRow> contacts = ds.Tables["Contact"].AsEnumerable();
string nameList =
contacts.Take(5).Select(contact => contact.Field<string>("LastName")).Aggregate((workingList, next) => workingList + "," + next);
Console.WriteLine(nameList);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As IEnumerable(Of DataRow) = _
ds.Tables("Contact").AsEnumerable()
Dim nameList As String = _
contacts.Take(5).Select(Function(contact) contact.Field(Of String)("LastName")). _
Aggregate(Function(workingList, next1) workingList + "," + next1)
Console.WriteLine(nameList)
Average
Example
This example uses the Average method to find the average list price of the products.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
Decimal averageListPrice =
products.Average(product => product.Field<Decimal>("ListPrice"));
Console.WriteLine("The average list price of all the products is ${0}",
averageListPrice);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As IEnumerable(Of DataRow) = _
ds.Tables("Product").AsEnumerable()
Dim averageListPrice As Decimal = _
products.Average(Function(product) product. _
Field(Of Decimal)("ListPrice"))
Console.WriteLine("The average list price of all the products is $" & _
averageListPrice)
Example
This example uses the Average method to find the average list price of the products of each style.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
var query = from product in products
group product by product.Field<string>("Style") into g
select new
{
Style = g.Key,
AverageListPrice =
g.Average(product => product.Field<Decimal>("ListPrice"))
};
foreach (var product in query)
{
Console.WriteLine("Product style: {0} Average list price: {1}",
product.Style, product.AverageListPrice);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As IEnumerable(Of DataRow) = _
ds.Tables("Product").AsEnumerable()
Dim query = _
From product In products _
Group product By style = product.Field(Of String)("Style") Into g = Group _
Select New With _
{ _
.Style = style, _
.AverageListPrice = g.Average(Function(product) _
product.Field(Of Decimal)("ListPrice")) _
}
For Each product In query
Console.WriteLine("Product style: {0} Average list price: {1}", _
product.Style, product.AverageListPrice)
Next
Example
This example uses the Average method to find the average total due.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal averageTotalDue = orders.AsEnumerable().
Average(order => order.Field<decimal>("TotalDue"));
Console.WriteLine("The average TotalDue is {0}.",
averageTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim averageTotalDue As Decimal = orders.AsEnumerable(). _
Average(Function(order) order.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The average TotalDue is {0}.", _
averageTotalDue)
Example
This example uses the Average method to get the average total due for each contact ID.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
averageTotalDue =
g.Average(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
order.Category,
order.averageTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.averageTotalDue = g.Average(Function(order) order. _
Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Average TotalDue = {1}", order.Category, _
order.averageTotalDue)
Next
Example
This example uses the Average method to get the orders with the average TotalDue for each contact.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let averageTotalDue = g.Average(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.Field<decimal>("TotalDue") ==
averageTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
Console.WriteLine("");
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let averageTotalDue = g.Average(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.CheapestProducts = g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = averageTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.CheapestProducts
Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Console.WriteLine("")
Next
Count
Example
This example uses the Count method to return the number of products in the Product table.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
var products = ds.Tables["Product"].AsEnumerable();
int numProducts = products.Count();
Console.WriteLine("There are {0} products.", numProducts);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim numProducts = products.AsEnumerable().Count()
Console.WriteLine("There are " & numProducts & " products.")
Example
This example uses the Count method to return a list of contact IDs and how many orders each has.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
var query = from contact in contacts.AsEnumerable()
select new
{
CustomerID = contact.Field<int>("ContactID"),
OrderCount =
contact.GetChildRows("SalesOrderContact").Count()
};
foreach (var contact in query)
{
Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
contact.CustomerID,
contact.OrderCount);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim query = _
From contact In contacts.AsEnumerable() _
Select New With _
{ _
.ContactID = contact.Field(Of Integer)("ContactID"), _
.OrderCount = contact.GetChildRows("SalesOrderContact").Count() _
}
For Each contact In query
Console.Write("CustomerID = " & contact.ContactID)
Console.WriteLine(vbTab & "OrderCount = " & contact.OrderCount)
Next
Example
This example groups products by color and uses the Count method to return the number of products in each color group.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query =
from product in products.AsEnumerable()
group product by product.Field<string>("Color") into g
select new { Color = g.Key, ProductCount = g.Count() };
foreach (var product in query)
{
Console.WriteLine("Color = {0} \t ProductCount = {1}",
product.Color,
product.ProductCount);
}
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = _
From product In products.AsEnumerable() _
Group product By color = product.Field(Of String)("Color") Into g = Group _
Select New With {.Color = color, .ProductCount = g.Count()}
For Each product In query
Console.WriteLine("Color = {0} " & vbTab & "ProductCount = {1}", _
product.Color, _
product.ProductCount)
Next
LongCount
Example
This example gets the contact count as a long integer.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable contacts = ds.Tables["Contact"];
long numberOfContacts = contacts.AsEnumerable().LongCount();
Console.WriteLine("There are {0} Contacts", numberOfContacts);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim contacts As DataTable = ds.Tables("Contact")
Dim numberOfContacts = contacts.AsEnumerable().LongCount()
Console.WriteLine("There are {0} Contacts", numberOfContacts)
Max
Example
This example uses the Max method to get the largest total due.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal maxTotalDue = orders.AsEnumerable().
Max(w => w.Field<decimal>("TotalDue"));
Console.WriteLine("The maximum TotalDue is {0}.",
maxTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim maxTotalDue As Decimal = orders.AsEnumerable(). _
Max(Function(w) w.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The maximum TotalDue is {0}.", maxTotalDue)
Example
This example uses the Max method to get the largest total due for each contact ID.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
maxTotalDue =
g.Max(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
order.Category, order.maxTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.maxTotalDue = _
g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
" Maximum TotalDue = {1}", _
order.Category, order.maxTotalDue)
Next
Example
This example uses the Max method to get the orders with the largest TotalDue for each contact ID.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let maxTotalDue = g.Max(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
CheapestProducts =
g.Where(order => order.Field<decimal>("TotalDue") ==
maxTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.CheapestProducts)
{
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let maxTotalDue = g.Max(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.CheapestProducts = _
g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = maxTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.CheapestProducts
Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1} ", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Next
Min
Example
This example uses the Min method to get the smallest total due.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
Decimal smallestTotalDue = orders.AsEnumerable().
Min(totalDue => totalDue.Field<decimal>("TotalDue"));
Console.WriteLine("The smallest TotalDue is {0}.",
smallestTotalDue);
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim smallestTotalDue As Decimal = orders.AsEnumerable(). _
Min(Function(totalDue) totalDue.Field(Of Decimal)("TotalDue"))
Console.WriteLine("The smallest TotalDue is {0}.", smallestTotalDue)
Example
This example uses the Min method to get the smallest total due for each contact ID.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
smallestTotalDue =
g.Min(order => order.Field<decimal>("TotalDue"))
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
order.Category, order.smallestTotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.smallestTotalDue = g.Min(Function(order) _
order.Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & _
"Minimum TotalDue = {1}", order.Category, order.smallestTotalDue)
Next
Example
This example uses the Min method to get the orders with the smallest total due for each contact.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
let minTotalDue = g.Min(order => order.Field<decimal>("TotalDue"))
select new
{
Category = g.Key,
smallestTotalDue =
g.Where(order => order.Field<decimal>("TotalDue") ==
minTotalDue)
};
foreach (var orderGroup in query)
{
Console.WriteLine("ContactID: {0}", orderGroup.Category);
foreach (var order in orderGroup.smallestTotalDue)
{
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
Console.WriteLine("");
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Let minTotalDue = g.Min(Function(order) order.Field(Of Decimal)("TotalDue")) _
Select New With _
{ _
.Category = contactID, _
.smallestTotalDue = g.Where(Function(order) order. _
Field(Of Decimal)("TotalDue") = minTotalDue) _
}
For Each orderGroup In query
Console.WriteLine("ContactID: " & orderGroup.Category)
For Each order In orderGroup.smallestTotalDue
Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1} ", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Console.WriteLine("")
Next
Sum
Example
This example uses the Sum method to get the total number of order quantities in the SalesOrderDetail table.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderDetail"];
double totalOrderQty = orders.AsEnumerable().
Sum(o => o.Field<Int16>("OrderQty"));
Console.WriteLine("There are a total of {0} OrderQty.",
totalOrderQty);
Example
This example uses the Sum method to get the total due for each contact ID.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
group order by order.Field<Int32>("ContactID") into g
select new
{
Category = g.Key,
TotalDue = g.Sum(order => order.Field<decimal>("TotalDue")),
};
foreach (var order in query)
{
Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
order.Category, order.TotalDue);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Group order By contactID = order.Field(Of Int32)("ContactID") Into g = Group _
Select New With _
{ _
.Category = contactID, _
.TotalDue = g.Sum(Function(order) order. _
Field(Of Decimal)("TotalDue")) _
}
For Each order In query
Console.WriteLine("ContactID = {0} " & vbTab & "TotalDue sum = {1}", _
order.Category, order.TotalDue)
Next