基于方法的查询语法示例：聚合运算符

项目
06/08/2023

本主题中的示例演示如何使用 Aggregate、Average、Count、LongCount、Max、Min 和 Sum 方法，利用基于方法的查询语法来查询 AdventureWorks 销售模型。这些示例中使用的 AdventureWorks 销售模型从 AdventureWorks 示例数据库中的 Contact、Address、Product、SalesOrderHeader 和 SalesOrderDetail 等表生成。

本主题中的示例使用下面的 using/Imports 语句：

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Globalization;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.Data.Common;


Option Explicit On
Option Strict On
Imports System.Data.Objects
Imports System.Globalization

平均值

示例

以下示例使用 Average 方法以查找产品的平均标价。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

    Decimal averageListPrice =
        products.Average(product => product.ListPrice);

    Console.WriteLine("The average list price of all the products is ${0}",
        averageListPrice);
}

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim averageListPrice As Decimal = _
        products.Average(Function(prod) prod.ListPrice)

    Console.WriteLine("The average list price of all the products is ${0}", _
            averageListPrice)
End Using

示例

以下示例使用 Average 方法以查找每种样式的产品的平均标价。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

    var query = from product in products
                group product by product.Style into g
                select new
                {
                    Style = g.Key,
                    AverageListPrice =
                        g.Average(product => product.ListPrice)
                };

    foreach (var product in query)
    {
        Console.WriteLine("Product style: {0} Average list price: {1}",
            product.Style, product.AverageListPrice);
    }
}

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim query = _
        From prod In products _
        Let styl = prod.Style _
        Group prod By styl Into g = Group _
        Select New With _
        { _
            .Style = styl, _
            .AverageListPrice = g.Average(Function(p) p.ListPrice) _
        }

    For Each prod In query
        Console.WriteLine("Product style: {0} Average list price: {1}", _
            prod.Style, prod.AverageListPrice)
    Next
End Using

示例

以下示例使用 Average 方法以查找平均应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    Decimal averageTotalDue = orders.Average(order => order.TotalDue);
    Console.WriteLine("The average TotalDue is {0}.", averageTotalDue);
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim averageTotalDue As Decimal = _
        orders.Average(Function(ord) ord.TotalDue)

    Console.WriteLine("The average TotalDue is {0}.", averageTotalDue)
End Using

示例

以下示例使用 Average 方法以获取每个联系人 ID 的平均应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            averageTotalDue = g.Average(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
            order.Category, order.averageTotalDue);
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .averageTotalDue = _
                g.Average(Function(ord) ord.TotalDue) _
         }

    For Each ord In query
        Console.WriteLine("ContactID = {0} " & vbTab & _
            " Average TotalDue = {1}", _
            ord.Category, ord.averageTotalDue)
    Next
End Using

示例

以下示例使用 Average 方法以针对每个联系人获取具有平均应付款总计的订单。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let averageTotalDue = g.Average(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            CheapestProducts =
                g.Where(order => order.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.TotalDue, order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let averageTotalDue = g.Average(Function(ord) ord.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .CheapestProducts = _
                g.Where(Function(ord) ord.TotalDue = averageTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.CheapestProducts
            Console.WriteLine("Average total due for SalesOrderID {1} is: {0}", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

计数

示例

以下示例使用 Count 方法以返回 Product 表中的产品数量。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

    int numProducts = products.Count();

    Console.WriteLine("There are {0} products.", numProducts);
}

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim numProducts As Integer = products.Count()

    Console.WriteLine("There are {0} products.", numProducts)
End Using

示例

以下示例使用 Count 方法以返回联系人 ID 的列表和每个联系人 ID 所具有的订单数。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;

    //Can't find field SalesOrderContact
    var query =
        from contact in contacts
        select new
        {
            CustomerID = contact.ContactID,
            OrderCount = contact.SalesOrderHeaders.Count()
        };

    foreach (var contact in query)
    {
        Console.WriteLine("CustomerID = {0} \t OrderCount = {1}",
            contact.CustomerID,
            contact.OrderCount);
    }
}

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts

    Dim query = _
        From cont In contacts _
        Select New With _
        { _
            .CustomerID = cont.ContactID, _
            .OrderCount = cont.SalesOrderHeaders.Count() _
         }

    For Each cont In query
        Console.WriteLine("CustomerID = {0}   OrderCount = {1}", _
               cont.CustomerID, cont.OrderCount)
    Next
End Using

示例

以下示例按颜色对产品进行分组，并使用 Count 方法以返回每个颜色组中的产品数量。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Product> products = context.Products;

    var query =
        from product in products
        group product by product.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);
    }
}

Using context As New AdventureWorksEntities
    Dim products As ObjectSet(Of Product) = context.Products

    Dim query = _
        From prod In products _
        Let pc = prod.Color _
        Group prod By pc Into g = Group _
        Select New With {.Color = pc, .ProductCount = g.Count()}

    For Each prod In query
        Console.WriteLine("Color = {0} " & vbTab & " ProductCount = {1}", _
            prod.Color, prod.ProductCount)
    Next
End Using

LongCount

示例

以下示例以长整型获取联系人计数。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<Contact> contacts = context.Contacts;

    long numberOfContacts = contacts.LongCount();
    Console.WriteLine("There are {0} Contacts", numberOfContacts);
}

Using context As New AdventureWorksEntities
    Dim contacts As ObjectSet(Of Contact) = context.Contacts

    Dim numberOfContacts As Long = contacts.LongCount()

    Console.WriteLine("There are {0} Contacts", numberOfContacts)
End Using

Max

示例

以下示例使用 Max 方法以获取最大应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    Decimal maxTotalDue = orders.Max(w => w.TotalDue);
    Console.WriteLine("The maximum TotalDue is {0}.",
        maxTotalDue);
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim maxTotalDue As Decimal = _
        orders.Max(Function(ord) ord.TotalDue)

    Console.WriteLine("The maximum TotalDue is {0}.", maxTotalDue)
End Using

示例

以下示例使用 Max 方法以获取每个联系人 ID 的最大应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            maxTotalDue =
                g.Max(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t Maximum TotalDue = {1}",
            order.Category, order.maxTotalDue);
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .MaxTotalDue = _
                g.Max(Function(ord) ord.TotalDue) _
         }

    For Each ord In query
        Console.WriteLine("ContactID = {0} " & vbTab & _
            " Maximum TotalDue = {1}", _
            ord.Category, ord.MaxTotalDue)
    Next
End Using

示例

以下示例使用 Max 方法以针对每个联系人 ID 获取具有最大应付款总计的订单。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let maxTotalDue = g.Max(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            CheapestProducts =
                g.Where(order => order.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.TotalDue,
                order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let maxTotalDue = g.Max(Function(ord) ord.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .CheapestProducts = _
                g.Where(Function(ord) ord.TotalDue = maxTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.CheapestProducts
            Console.WriteLine("MaxTotalDue {0} for SalesOrderID {1}: ", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

Min

示例

以下示例使用 Min 方法以获取最小应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    Decimal smallestTotalDue = orders.Min(totalDue => totalDue.TotalDue);
    Console.WriteLine("The smallest TotalDue is {0}.",
        smallestTotalDue);
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim smallestTotalDue As Decimal = _
        orders.Min(Function(totDue) totDue.TotalDue)

    Console.WriteLine("The smallest TotalDue is {0}.", _
        smallestTotalDue)
End Using

示例

以下示例使用 Min 方法以获取每个联系人 ID 的最小应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            smallestTotalDue =
                g.Min(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t Minimum TotalDue = {1}",
            order.Category, order.smallestTotalDue);
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .smallestTotalDue = _
                g.Min(Function(o) o.TotalDue) _
         }

    For Each ord In query
        Console.WriteLine("ContactID = {0} " & vbTab & _
            " Minimum TotalDue = {1}", ord.Category, ord.smallestTotalDue)
    Next
End Using

示例

以下示例使用 Min 方法以针对每个联系人获取具有最小应付款总计的订单。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        let minTotalDue = g.Min(order => order.TotalDue)
        select new
        {
            Category = g.Key,
            smallestTotalDue =
                g.Where(order => order.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.TotalDue,
                order.SalesOrderID);
        }
        Console.Write("\n");
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Let minTotalDue = g.Min(Function(o) o.TotalDue) _
        Select New With _
        { _
            .Category = contID, _
            .smallestTotalDue = _
                g.Where(Function(o) o.TotalDue = minTotalDue) _
         }

    For Each orderGroup In query
        Console.WriteLine("ContactID: {0}", orderGroup.Category)
        For Each ord In orderGroup.smallestTotalDue
            Console.WriteLine("Minimum TotalDue {0} for SalesOrderID {1}: ", _
                ord.TotalDue, ord.SalesOrderID)
        Next
        Console.Write(vbNewLine)
    Next
End Using

Sum

示例

以下示例使用 Sum 方法以获取 SalesOrderDetail 表中订单数量的总数。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderDetail> orders = context.SalesOrderDetails;

    double totalOrderQty = orders.Sum(o => o.OrderQty);
    Console.WriteLine("There are a total of {0} OrderQty.",
        totalOrderQty);
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderDetail) = context.SalesOrderDetails

    Dim totalOrderQty As Double = orders.Sum(Function(o) o.OrderQty)

    Console.WriteLine("There are a total of {0} OrderQty.", _
        totalOrderQty)
End Using

示例

以下示例使用 Sum 方法以获取每个联系人 ID 的应付款总计。

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    ObjectSet<SalesOrderHeader> orders = context.SalesOrderHeaders;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            TotalDue = g.Sum(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t TotalDue sum = {1}",
            order.Category, order.TotalDue);
    }
}

Using context As New AdventureWorksEntities
    Dim orders As ObjectSet(Of SalesOrderHeader) = context.SalesOrderHeaders

    Dim query = _
        From ord In orders _
        Let contID = ord.Contact.ContactID _
        Group ord By contID Into g = Group _
        Select New With _
        { _
            .Category = contID, _
            .TotalDue = g.Sum(Function(o) o.TotalDue) _
        }

    For Each ord In query
        Console.WriteLine("ContactID = {0} " & vbTab & _
            " TotalDue sum = {1}", ord.Category, ord.TotalDue)
    Next
End Using

请参阅

LINQ to Entities 中的查询

基于方法的查询语法示例：聚合运算符

平均值

示例

示例

示例

示例

示例

计数

示例

示例

示例

LongCount

示例

Max

示例

示例

示例

Min

示例

示例

示例

Sum

示例

示例

请参阅

其他资源