共用方式為

Method-Based 查詢語法範例：匯總運算符（LINQ to DataSet）

  • 發行項

本主題中的範例示範如何使用 Aggregate、Average、Count、LongCount、Max、Min和 Sum 運算符，使用方法查詢語法來查詢 DataSet 和匯總數據。

這些範例中使用的 FillDataSet 方法在 將數據載入 DataSet中有指定。

本主題中的範例會使用 AdventureWorks 範例資料庫中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 數據表。

本主題中的範例會使用下列 using/Imports 語句：

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

如需詳細資訊，請參閱 如何：在 Visual Studio 中建立 LINQ to DataSet 專案。

聚合

範例

這個範例會使用 Aggregate 方法，從 Contact 數據表取得前 5 個聯繫人，並建置以逗號分隔的姓氏清單。

// 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 方法來尋找產品的平均清單價格。

// 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 ${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)

範例

此範例會使用 Average 方法來尋找每個樣式產品的平均清單價格。

// 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: {product.Style} Average list price: {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

範例

此範例會使用 Average 方法來尋找應付款的平均總額。

// 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 {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)

範例

此範例運用 Average 方法來取得每個聯絡人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 = {order.Category} \t Average TotalDue = {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

範例

此範例會使用 Average 方法來取得每個聯繫人的平均 TotalDue 訂單。

// 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: {orderGroup.Category}");
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine($"Average total due for SalesOrderID {order.Field<Int32>("SalesOrderID")} is: {order.Field<decimal>("TotalDue")}");
    }
    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 方法來傳回 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 {numProducts} products.");

' 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.")

範例

此範例使用 Count 方法來傳回聯絡人 ID 清單，以及每個聯絡人的訂單數量。

// 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 = {contact.CustomerID} \t OrderCount = {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

範例

本範例會依色彩將產品分組，並使用 Count 方法來傳回每個色彩群組中的產品數目。

// 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 = {product.Color} \t ProductCount = {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

範例

本範例會取得聯絡人計數作為長整數。

// 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 {numberOfContacts} Contacts");

' 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 方法來獲取最大到期總金額。

// 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 {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)

範例

本範例會使用 Max 方法來取得每個聯絡人 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 = {order.Category} \t Maximum TotalDue = {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

範例

此範例會使用 Max 方法來取得每個聯絡人 ID 擁有的最大 TotalDue 訂單。

// 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: {orderGroup.Category}");
    foreach (var order in orderGroup.CheapestProducts)
    {
        Console.WriteLine($"MaxTotalDue {order.Field<decimal>("TotalDue")} for SalesOrderID {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 方法來取得最小應付總額。

// 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 {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)

範例

此範例會使用 Min 方法來取得每個聯絡人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 = {order.Category} \t Minimum TotalDue = {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

範例

此範例會使用 Min 方法來取得每個聯繫人到期金額最小的訂單。

// 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: {orderGroup.Category}");
    foreach (var order in orderGroup.smallestTotalDue)
    {
        Console.WriteLine($"Minimum TotalDue {order.Field<decimal>("TotalDue")} for SalesOrderID {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 方法來取得 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 {totalOrderQty} OrderQty.");

範例

此範例會使用 Sum 方法來獲得每個聯絡人 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 = {order.Category} \t TotalDue sum = {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

另請參閱