LINQ Traffic # 3 – Aggregate Query Operators

RamiVemula on May 25, 2010 at 2:04 pm

The present article demonstrates – “How to use LINQ Aggregate Query Operators”.

Aggregate Operators –

Max
Min
Sum
Count
LongCount
Average
Aggregate

NOTE –

Most of the code I have written for demonstrate purpose is self-explanatory, So intentionally I have not written whole bunch of comments explaining each and every line of the code ( as I feel that those comments make readability more cumbersome). Kindly bear with my writing style.
There might be some problems with following the naming conventions, I regret for any inconvenience caused by it.
The Queries are designed and implement to show various possibilities of implementations. Some queries (like finding the Product ID’s which are odd numbers etc) are may be meaningless in real world, But my request is to see the underlying implementation, means the essence of the query but not the result.

========================================================================================================

Product Class

Note – This is the class on which I use LINQ Query to get required results.

========================================================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Collections;

public class Product
{
    /* The four Properties of Product Class */
    public string ProductName
    {
        set;
        get;
    }
    public int ProductId
    {
        set;
        get;
    }
    public string Manufacturer
    {
        set;
        get;
    }
    public string Drivetype
    {
        set;
        get;
    }

    /* Method to create a sample Product List, it returns the created Product list. */

    public List<Product> CreateProducts()
    {
        List<Product> ListInitialize = new List<Product>();

        ListInitialize.Add(new Product { ProductName = "Civic", ProductId = 5, Manufacturer = "Honda", Drivetype = "Auto" });
        ListInitialize.Add(new Product { ProductName = "Mustang", ProductId = 11, Manufacturer = "Ford", Drivetype = "Manual" });
        ListInitialize.Add(new Product { ProductName = "350z", ProductId = 2, Manufacturer = "Nissan", Drivetype = "Auto" });
        ListInitialize.Add(new Product { ProductName = "Tavera", ProductId = 24, Manufacturer = "Chevrolet", Drivetype = "Auto" });
        ListInitialize.Add(new Product { ProductName = "Terrain", ProductId = 1, Manufacturer = "GMC", Drivetype = "Auto" });
        ListInitialize.Add(new Product { ProductName = "X6 M", ProductId = 17, Manufacturer = "BMW", Drivetype = "Manual" });
        ListInitialize.Add(new Product { ProductName = "CL63 AMG Coupe", ProductId = 10, Manufacturer = "Mercedes", Drivetype = "Manual" });

        return ListInitialize;
    }

    /* RepeaterBind(IEnumerable,Repeater) is used to Bind the Repeater control with the Output of 
     * Linq Query. It takes two arguments, first one is of IEnumerable compatible (the result of Linq Query)
     * and second one is of Repeater Type.*/
    public void RepeaterBind(IEnumerable ListPrint, Repeater repeater)
    {
        repeater.DataSource = ListPrint;
        repeater.DataBind();
    }
}

========================================================================================================

Max Operator

Note – This Demo uses the above mentioned Product Class, and displays the results in a Repeater.

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the Max operator of LINQ, the query gets the 
         *              product which got the maximum ID.
         */
        var LinqQueryProducts = from product in ListProduct
                                where product.ProductId == ListProduct.Max(p => p.ProductId)
                                select product;

        // Sends the Query and Repeater control for binding process 
        productObject.RepeaterBind(LinqQueryProducts, Repeater1);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server">
            <ItemTemplate>
                Product Name :-
                    <asp:Label ID="Label1" runat="server" Text='<%#Eval("ProductName")%>'></asp:Label><br/>
                Product Id :-
                    <asp:Label ID="Label2" runat="server" Text='<%#Eval("ProductId")%>'></asp:Label><br/>
                Manufacutrer :-
                    <asp:Label ID="Label3" runat="server" Text='<%#Eval("Manufacturer")%>'></asp:Label><br/>
                Drivetype :-
                    <asp:Label ID="Label4" runat="server" Text='<%#Eval("Drivetype")%>'></asp:Label><br/>
            </ItemTemplate>
            <SeparatorTemplate>
                <hr></hr>
            </SeparatorTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

OUTPUT –

Max

========================================================================================================

Min Operator

Note – This Demo uses the above mentioned Product Class, and displays the results in a Repeater.

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the Min operator of LINQ, First we group the products
         *              based on their drivetypes, and then we select individual product
         *              from each group which got the minimum id.
         */
        var LinqQueryProducts = from product in ListProduct
                                group product by product.Drivetype into grp
                                select new
                                {
                                    DriveType = grp.Key,
                                    LeastIDProduct = grp.Where(leastProduct => leastProduct.ProductId == grp.Min(p => p.ProductId)).First()
                                };

        /* We Create a new list of Products and bind the Least ID product from different
         * groups as shown below.
         */
        List<Product> pro = new List<Product>();
        foreach (var group in LinqQueryProducts)
        {
            pro.Add(group.LeastIDProduct);
        }

        // Sends the List and Repeater control for binding process 
        productObject.RepeaterBind(pro, Repeater1);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Repeater ID="Repeater1" runat="server">
            <ItemTemplate>
                Product Name :-
                    <asp:Label ID="Label1" runat="server" Text='<%#Eval("ProductName")%>'></asp:Label><br/>
                Product Id :-
                    <asp:Label ID="Label2" runat="server" Text='<%#Eval("ProductId")%>'></asp:Label><br/>
                Manufacutrer :-
                    <asp:Label ID="Label3" runat="server" Text='<%#Eval("Manufacturer")%>'></asp:Label><br/>
                Drivetype :-
                    <asp:Label ID="Label4" runat="server" Text='<%#Eval("Drivetype")%>'></asp:Label><br/>
            </ItemTemplate>
            <SeparatorTemplate>
                <hr></hr>
            </SeparatorTemplate>
        </asp:Repeater>
    </div>
    </form>
</body>
</html>

OUTPUT –

Min

========================================================================================================

Sum Operator

Note – This Demo uses the above mentioned Product Class, and displays the results using Response.Write()

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the Sum operator of LINQ, First we group the products
         *              based on their drivetypes, and then we sum the id's of all products
         *              from each group which is stored in SumID.
         */
        var LinqQueryProducts = from product in ListProduct
                                group product by product.Drivetype into grp
                                select new
                                {
                                    DriveType = grp.Key,
                                    SumID = grp.Sum(p => p.ProductId)
                                };

        // Loop through IGrouping of the query to get the sum of ID's
        foreach (var group in LinqQueryProducts)
        {
            Response.Write("The DriveType - " + group.DriveType + " has the ID - Sum = " + group.SumID + "<br/>");
        }

    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

OUTPUT –

sum

========================================================================================================

Count Operator

Note – This Demo uses the above mentioned Product Class, and displays the results using Response.Write()

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the Count operator of LINQ, we get the products which
         *              got odd numbers as Product ID's and then we count the number of
         *              products.
         */
        var LinqQueryProducts = (from product in ListProduct
                                 where product.ProductId % 2 == 1
                                 select product).Count();

        // print the result to the page.
        Response.Write("Count of Products with odd numbers as Product ID's - " + LinqQueryProducts);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

OUTPUT –

Count

========================================================================================================

LongCount Operator

Note – This Demo uses the above mentioned Product Class, and displays the results using Response.Write()

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the LongCount operator of LINQ, we get the products which
         *              got Product ID's less than 15. We use LongCount for extremely large
         *              sequesnce (instead of count).
         */
        var LinqQueryProducts =  (from product in ListProduct
                                 select product).LongCount(p => p.ProductId < 15);

        // print the result to the page.
        Response.Write("Count of Products whose producr ID is less than 15 - " + LinqQueryProducts);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

OUTPUT –

Longcount

========================================================================================================

Average Operator

Note – This Demo uses the above mentioned Product Class, and displays the results using Response.Write()

========================================================================================================

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        // Create Product Class Object
        Product productObject = new Product();

        // Get List of Prducts
        List<Product> ListProduct = productObject.CreateProducts();

        /* LINQ Query - This uses the Average operator, first we group the products
         *              based on Drivetype, then we calculate the average of ID's
         *              in each group and store it it AverageID.
         */
        var LinqQueryProducts = from product in ListProduct
                                group product by product.Drivetype into grp
                                select new
                                    {
                                        DriveType = grp.Key,
                                        AverageID = grp.Average(p => p.ProductId)
                                    };

        // Loop through IGrouping of the query to get the Average of ID's of Products in each group
        foreach (var group in LinqQueryProducts)
        {
            Response.Write("The DriveType - " + group.DriveType + " has the ID - Average = " + group.AverageID + "<br/>");
        }
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

OUTPUT –

Average

========================================================================================================

Aggregate Operator

Note – This Demo goes out of the Product Class. It takes a int array as withdrawals and cumulatively find the ending balance. I took this example for better demonstration.

========================================================================================================

NOTE – Demo is taken from MSDN.

<%@ Page Language="C#"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
        double startBalance = 100;

        int[] attemptedWithdrawals = { 20, 10, 40, 50, 10, 70, 30 };

        /*Mode of Operation - 
         * 20 <= 100 => 100 - 20 = 80
         * 10 <= 80 => 80 - 10 = 70
         * 40 <= 70 => 70 - 40 = 30
         * 50 <= 30 => 30  -------------------> Failed Winthdrawal
         * 10 <= 30 => 30 - 10 = 20
         * 70 <= 20 => 20  -------------------> Failed Winthdrawal
         * 30 <= 20 => 20 --> final Result
         */
        double endBalance =
            attemptedWithdrawals.Aggregate(startBalance,
                (balance, nextWithdrawal) =>
                    ((nextWithdrawal <= balance) ? (balance - nextWithdrawal) : balance));

        Response.Write("Ending balance: " + endBalance);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

OUTPUT –

Filed under:ASP.Net, C#, LINQ

Comments for this item Comments (0)

Add Your Comments

Use the below form to express your comments, views and suggestions about the article. I request everyone who leaves a comment to be more specific to this article.

Please note that all irrelevant comments are moderated. And in case of any comments having abusive content and matter from other blogs will not be published.

For most generalized comments and for any other non-related article queries, please use Contact page to reach me. I will get back to you as soon as possible.

For complete Disclaimer, go through About page.

*Provide a valid email so that I can reach (if required) you personally at any time.

Rami Vemula

Have a possible topic for an ARTICLE or got a QUESTION??? - Write to Me - CLICK HERE

Interesting Website??? - Do write a Testimonial - CLICK HERE

I am :-

Tags

Search Content

@Forums.Asp.Net

Standards

LINQ Traffic

Entity Framework Tutorials

JQuery Quickies

Intstrings News