Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Method-Based Query Syntax Examples: Aggregate Operators

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;
    

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);
}
    

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);
}
}
    

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);
}
    

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);
}
}
    

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");
}
}
    

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);
}
    

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);
}
}
    

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);
}
}
    

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);
}
    

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);
}
    

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);
}
}
    

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");
}
}
    

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);
}
    

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);
}
}
    

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("Mininum TotalDue {0} for SalesOrderID {1}: ",
order.TotalDue,
order.SalesOrderID);
}
Console.Write("\n");
}
}
    

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);
}
    

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);
}
}
    

See Also




Build Date:

2012-10-01
Show:
© 2015 Microsoft