Method-Based Query Syntax Examples: Aggregate Operators

  • Article

The examples in this topic demonstrate how to use the Aggregate, Average, Count, LongCount, Max, Min, and Sum methods to query the AdventureWorks Sales Model using method-based query syntax. The AdventureWorks Sales Model used in these examples is built from the Contact, Address, Product, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks sample database.

The examples in this topic use the following using/Imports statements:

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

Example

The following example uses the Average method to find the average list price of the products.

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

Example

The following example uses the Average method to find the average list price of the products of each style.

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

Example

The following example uses the Average method to find the average total due.

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

Example

The following example uses the Average method to get the average total due for each contact 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

Example

The following example uses the Average method to get the orders with the average total due for each contact.

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

Example

The following example uses the Count method to return the number of products in the Product table.

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

Example

The following example uses the Count method to return a list of contact IDs and how many orders each has.

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

Example

The following example groups products by color and uses the Count method to return the number of products in each color group.

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

Example

The following example gets the contact count as a long integer.

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

Example

The following example uses the Max method to get the largest total due.

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

Example

The following example uses the Max method to get the largest total due for each contact 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

Example

The following example uses the Max method to get the orders with the largest total due for each contact 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

Example

The following example uses the Min method to get the smallest total due.

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

Example

The following example uses the Min method to get the smallest total due for each contact 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

Example

The following example uses the Min method to get the orders with the smallest total due for each contact.

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

Example

The following example uses the Sum method to get the total number of order quantities in the SalesOrderDetail table.

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

Example

The following example uses the Sum method to get the total due for each contact 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

See also