Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Aggregate Operators
Collapse the table of content
Expand the table of content

Query Expression Syntax Examples: Aggregate Operators (LINQ to DataSet)

The examples in this topic demonstrate how to use the Average, Count, Max, Min, and Sum methods to query a DataSet and aggregate data using query expression syntax.

The FillDataSet method used in these examples is specified in Loading Data Into a DataSet.

The examples in this topic use 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.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Globalization;

For more information, see How to: Create a LINQ to DataSet Project In Visual Studio.

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

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

This example uses Average to get the average total due for each contact 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 = {0} \t Average TotalDue = {1}",
        order.Category,
        order.averageTotalDue);
}

This example uses Average to get the orders with the average TotalDue for each contact.

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

This example uses Count to return a list of contact IDs and how many orders each has.

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

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

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

This example uses the Max method to get the largest total due for each contact 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 = {0} \t Maximum TotalDue = {1}",
        order.Category, order.maxTotalDue);
}

This example uses the Max method to get the orders with the largest TotalDue for each contact 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
    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"));
    }
}

This example uses the Min method to get the smallest total due for each contact 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 = {0} \t Minimum TotalDue = {1}",
        order.Category, order.smallestTotalDue);
}

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

// 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("Mininum TotalDue {0} for SalesOrderID {1}: ",
            order.Field<decimal>("TotalDue"),
            order.Field<Int32>("SalesOrderID"));
    }
    Console.WriteLine("");
}

This example uses the Sum method to get the total due for each contact 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 = {0} \t TotalDue sum = {1}",
        order.Category, order.TotalDue);
}
Show:
© 2015 Microsoft