Beispiele für die Abfrageausdruckssyntax: Aggregierungsoperatoren (LINQ to DataSet)
In den Beispielen in diesem Thema wird gezeigt, wie Sie mithilfe der Methoden Average, Count, Max, Min und Sum und der Abfrageausdruckssyntax ein DataSet abfragen und Daten aggregieren können.
Die in diesen Beispielen verwendete FillDataSet-Methode wird unter Laden von Daten in ein DataSet beschrieben.
In den Beispielen in diesem Thema wird auf die Tabellen <legacyBold>Contact</legacyBold>, <legacyBold>Address</legacyBold>, <legacyBold>Product</legacyBold>, <legacyBold>SalesOrderHeader</legacyBold> und <legacyBold>SalesOrderDetail</legacyBold> in der <legacyBold>AdventureWorks</legacyBold>-Beispieldatenbank zurückgegriffen.
Die Beispiele in diesem Thema beziehen sich auf die folgenden using/Imports-Anweisungen:
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
Weitere Informationen finden Sie unter Erstellen eines LINQ to DataSet-Projekts in Visual Studio.
Average
Beispiel
In diesem Beispiel wird die Average-Methode verwendet, um den durchschnittlichen Listenpreis der Produkte für die einzelnen Stile zu ermitteln.
// 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
Beispiel
In diesem Beispiel wird die Average-Methode verwendet, um für jede Kontakt-ID den durchschnittlichen fälligen Gesamtbetrag zu ermitteln.
// 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
Beispiel
In diesem Beispiel wird die Average-Methode verwendet, um für jede Kontakt-ID die Aufträge mit dem durchschnittlichen TotalDue-Wert zu ermitteln.
// 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
Anzahl
Beispiel
In diesem Beispiel wird die Count-Methode verwendet, um eine Liste der Kontakt-IDs und der jeweiligen Anzahl von Aufträgen zu erstellen.
// 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
Beispiel
In diesem Beispiel werden die Produkte nach ihrer Farbe gruppiert. Anschließend wird die Count-Methode verwendet, um die Anzahl der Produkte in jeder Farbgruppe abzurufen.
// 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
Max
Beispiel
In diesem Beispiel wird die Max-Methode verwendet, um für jede Kontakt-ID den größten fälligen Gesamtbetrag zu ermitteln.
// 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
Beispiel
In diesem Beispiel wird die Max-Methode verwendet, um für jede Kontakt-ID die Aufträge mit dem größten TotalDue-Wert abzurufen.
// 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
Beispiel
In diesem Beispiel wird die Min-Methode verwendet, um für jede Kontakt-ID den kleinsten fälligen Gesamtbetrag zu ermitteln.
// 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
Beispiel
In diesem Beispiel wird die Min-Methode verwendet, um für jeden Kontakt die Aufträge mit dem kleinsten fälligen Gesamtbetrag zu ermitteln.
// 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
Beispiel
In diesem Beispiel wird die Sum-Methode verwendet, um für jede Kontakt-ID den fälligen Gesamtbetrag zu ermitteln.
// 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