Esempi di sintassi di query basate sul metodo: operatori di aggregazione (LINQ to DataSet)
Negli esempi di questo argomento viene illustrato l'uso degli operatori Aggregate, Average, Count, LongCount, Max, Min e Sum per eseguire una query su un oggetto DataSet e aggregare i dati usando la sintassi delle query basate su metodo.
Il metodo FillDataSet usato in questi esempi è specificato in Caricamento di dati in un DataSet.
Negli esempi di questo argomento vengono usate le tabelle Contact, Address, Product, SalesOrderHeader e SalesOrderDetail del database di esempio AdventureWorks.
Gli esempi in questo argomento usano le istruzioni using/Imports seguenti:
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
Per altre informazioni, vedere Procedura: Creare un progetto LINQ to DataSet in Visual Studio.
Aggregato
Esempio
In questo esempio viene usato il metodo Aggregate per ottenere i primi 5 contatti dalla tabella Contact e compilare un elenco delimitato da virgole degli ultimi nomi.
// 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)
Media
Esempio
In questo esempio viene usato il metodo Average per individuare il prezzo medio di listino dei prodotti.
// 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)
Esempio
In questo esempio viene usato il metodo Average per individuare il prezzo medio di listino dei prodotti dei singoli stili.
// 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
Esempio
In questo esempio viene usato il metodo Average per individuare il totale medio dovuto.
// 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)
Esempio
In questo esempio viene usato il metodo Average per ottenere il totale medio dovuto per ogni ID contatto.
// 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
Esempio
In questo esempio viene usato il metodo Average per ottenere gli ordini con il valore medio di TotalDue per ogni ID contatto.
// 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
Conteggio
Esempio
In questo esempio viene usato il metodo Count per restituire i numero di prodotti presenti nella tabella Product.
// 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.")
Esempio
In questo esempio viene usato il metodo Count per restituire un elenco di ID contatto e un elenco degli ordini relativi a ognuno di essi.
// 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
Esempio
In questo esempio i prodotti vengono raggruppati in base al colore e viene usato il metodo Count per restituire il numero di prodotti inclusi in ciascun gruppo.
// 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
Esempio
In questo esempio si ottiene il conteggio dei contatti come valore 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
Esempio
In questo esempio viene usato il metodo Max per ottenere il totale massimo dovuto.
// 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)
Esempio
In questo esempio viene usato il metodo Max per ottenere il totale massimo dovuto per ogni ID contatto.
// 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
Esempio
In questo esempio viene usato il metodo Max per ottenere gli ordini con il valore massimo di TotalDue per ogni ID contatto.
// 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
Esempio
In questo esempio viene usato il metodo Min per ottenere il totale minimo dovuto.
// 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)
Esempio
In questo esempio viene usato il metodo Min per ottenere il totale minimo dovuto per ogni ID contatto.
// 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
Esempio
In questo esempio viene usato il metodo Min per ottenere gli ordini con il totale minimo dovuto per ogni contatto.
// 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
Esempio
In questo esempio viene usato il metodo Sum per ottenere il numero totale di quantitativi ordinati presenti nella tabella 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);
Esempio
In questo esempio viene usato il metodo Sum per ottenere il totale dovuto per ogni ID contatto.
// 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