Actualización: November 2007
Los ejemplos de este tema muestran cómo utilizar los operadores Aggregate, Average, Count, LongCount, Max, Min y Sum para consultar DataSet y agregar datos utilizando sintaxis de consulta basada en métodos.
El método FillDataSet utilizado en estos ejemplos se especifica en Cargar datos en DataSet.
Los ejemplos de este tema usan las tablas Contact, Address, Product, SalesOrderHeader y SalesOrderDetail en la base de datos de ejemplo de AdventureWorks.
Los ejemplos de este tema utilizan las siguientes instrucciones using/Imports:
Option Explicit On
Imports System
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
using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;
Para obtener más información, vea Cómo crear un proyecto LINQ to DataSet en Visual Studio.
Aggregate
Ejemplo
En este ejemplo se utiliza el método Aggregate para obtener los cinco primeros contactos de la tabla Contact y generar una lista de apellidos delimitada por comas.
' 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)
// 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);
Average
Ejemplo
En este ejemplo se utiliza el método Average para encontrar el precio de venta promedio de los productos.
' 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)
// 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);
Ejemplo
En este ejemplo se utiliza el método Average para encontrar el precio de venta promedio de cada estilo de productos.
' 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
// 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);
}
Ejemplo
En este ejemplo se utiliza el método Average para encontrar el importe total a pagar promedio.
' 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)
// 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);
Ejemplo
En este ejemplo se utiliza el método Average para obtener el importe total a pagar promedio de cada id. de contacto.
' 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
// 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);
}
Ejemplo
En este ejemplo se utiliza el método Average para obtener los pedidos con el TotalDue promedio de cada contacto.
' 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
// 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("");
}
Count
Ejemplo
En este ejemplo se utiliza el método Count para devolver el número de productos de la tabla Product.
' 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.")
// 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);
Ejemplo
En este ejemplo se utiliza el método Count para devolver una lista de id. de contactos y el número de pedidos que tiene cada uno de ellos.
' 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
// 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);
}
Ejemplo
En este ejemplo se agrupan los productos por colores y se utiliza el método Count para devolver el número de productos de cada grupo de color.
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
// 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);
}
LongCount
Ejemplo
En este ejemplo se obtiene el recuento de contactos como un entero largo.
' 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)
// 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);
Max
Ejemplo
En este ejemplo se utiliza el método Max para obtener el mayor importe total a pagar.
' 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)
// 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);
Ejemplo
En este ejemplo se utiliza el método Max para obtener el mayor importe total a pagar de cada id. de contacto.
' 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
// 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);
}
Ejemplo
En este ejemplo se utiliza el método Max para obtener los pedidos con el TotalDue mayor de cada contacto.
' 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
// 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"));
}
}
Min
Ejemplo
En este ejemplo se utiliza el método Min para obtener el menor importe total a pagar.
' 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)
// 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);
Ejemplo
En este ejemplo se utiliza el método Min para obtener el menor importe total a pagar de cada id. de contacto.
' 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
// 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);
}
Ejemplo
En este ejemplo se utiliza el método Min para obtener el pedido con el menor importe total a pagar de cada contacto.
' 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("Mininum TotalDue {0} for SalesOrderID {1} ", _
order.Field(Of Decimal)("TotalDue"), _
order.Field(Of Int32)("SalesOrderID"))
Next
Console.WriteLine("")
Next
// 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("Mininum TotalDue {0} for SalesOrderID {1}: ",
order.Field<decimal>("TotalDue"),
order.Field<Int32>("SalesOrderID"));
}
Console.WriteLine("");
}
Sum
Ejemplo
En este ejemplo se utiliza el método Sum para obtener el número total de cantidades de pedido de la tabla SalesOrderDetail.
// 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);
Ejemplo
En este ejemplo se utiliza el método Sum para obtener el importe total a pagar de cada id. de contacto.
' 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
// 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);
}
Vea también