How To Use Aggregation Operations With Database Using LINQ

Introduction

In this article, I will demonstrate how we can use Sum, Min, Max Count, Average, LongCount, and Aggregate operator of Language-Integrated Query (LINQ). Sum, Min, Max Count, Average, LongCount, Aggregate operators are part of the aggregation operation in LINQ. An aggregation operation computes a single value from a collection of values. An example of an aggregation operation is - calculating the average daily temperature from a month's worth of daily temperature values.

 

• Sum - Calculates the sum of the values in a collection.
• Min - Determines the minimum value in a collection.
• Max - Determines the maximum value in a collection.
• Count - Counts the elements in a collection, optionally only those elements that satisfy a predicate function.
• Average - Calculates the average value of a collection of values.
• LongCount - Counts the elements in a large collection, optionally only those elements that satisfy a predicate function.
• Aggregate - Performs a custom aggregation operation on the values of a collection.

 

Step 1

Open SQL Server 2014 and create a table named Products. Insert some records in it.

CREATE TABLE [dbo].[Products](
    [Product_Id] [int] IDENTITY(1,1) NOT NULL,
    [Product_Name] [nvarchar](50) NULL,
    [Quantity] [int] NULL,
    [Price] [money] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
    [Product_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2

Open Visual Studio 2015 and create a new console application with a meaningful name.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

Screenshot for adding Entity framework

 

After clicking on the new item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory; you can give any name) and click on Add.

Screenshot for adding Entity framework 2

 

After you click on "Add a window", the wizard will open. Choose EF Designer from the database and click Next.

Screenshot for adding Entity framework 3

 

After clicking on Next, a window will appear. Choose New Connection. Another window will appear. Add your server name; if it is local then enter dot (.). Choose your database and click on OK.

Screenshot for adding Entity framework 4

 

The connection will be added. If you wish, you can save the connection as you want. You can change the name of your connection. It will save the connection in web config. Now, click on Next.

Screenshot for adding Entity framework 5

 

After clicking on NEXT, another window will appear. Choose database table name as shown in the below screenshot then click on Finish.

Screenshot for adding Entity framework 6

Screenshot for adding Entity framework 7

 

Entity framework will be added and a respective class gets generated under the Models folder.

Screenshot for adding Entity framework 8

The following class will be added.

namespace AggregationOperations_Demo
{
    using System;
    using System.Collections.Generic;

    public partial class Product
    {
        public int Product_Id { get; set; }
        public string Product_Name { get; set; }
        public Nullable<int> Quantity { get; set; }
        public Nullable<decimal> Price { get; set; }
    }
}

Step 4

Write a program to call the database class.

Example of Sum operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace AggregationOperations_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DBModel db=new DBModel ())
            {
                List<Product>listProduct=db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id+"\t"+product.Product_Name+ "\t\t" + product.Quantity+ "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("TOTAL PRODUCTS \n");

                int result = listProduct.Sum(q=>q.Quantity??0);

                Console.WriteLine("Total Product:\t"+ result);
   Console.ReadLine();

            }
        }
    }
}

Output

Example of Min Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace AggregationOperations_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DBModel db=new DBModel ())
            {
                List<Product>listProduct=db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id+"\t"+product.Product_Name+ "\t\t" + product.Quantity+ "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("PRODUCTS LIST\n");

                int result = listProduct.Min(q=>q.Quantity??0);

                Console.WriteLine("Lowest Product Quantity:\t"+ result);
     Console.ReadLine();
            }
        }
    }
}

Output

 

Example of Max Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace AggregationOperations_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DBModel db=new DBModel ())
            {
                List<Product>listProduct=db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id+"\t"+product.Product_Name+ "\t\t" + product.Quantity+ "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("PRODUCTS LIST \n");

                int result = listProduct.Max(q=>q.Quantity??0);

                Console.WriteLine("Highest Product Quantity:\t" + result);
     Console.ReadLine();

            }
        }
    }
}

Output 

 

Example of Average Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace AggregationOperations_Demo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (DBModel db=new DBModel ())
            {
                List<Product>listProduct=db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id+"\t"+product.Product_Name+ "\t\t" + product.Quantity+ "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("PRODUCTS LIST\n");

                decimal result = listProduct.Average(q => q.Price??0);

                Console.WriteLine("Average Product Price:\t"+ result);
     Console.ReadLine();

            }
        }
    }
}

Output 

Example of Count Operator

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AggregationOperations_Demo
{
    class Count
    {
        static void Main()
        {
            using (DBModel db = new DBModel())
            {
                List<Product> listProduct = db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id + "\t" + product.Product_Name + "\t\t" + product.Quantity + "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("TOTAL ENTRY \n");

                int result = listProduct.Count();

                Console.WriteLine("Total Entry:\t" + result);
            }
        }
    }
}

Output

Example of Aggregate Operator

using System;
using System.Collections.Generic;
using System.Linq;

namespace AggregationOperations_Demo
{
    class Aggregate
    {
        static void Main()
        {
            using (DBModel db = new DBModel())
            {
                List<Product> listProduct = db.Products.ToList<Product>();

                Console.WriteLine("LIST OF PRODUCTS \n");

                foreach (var product in listProduct)
                {
                    Console.WriteLine(product.Product_Id + "\t" + product.Product_Name + "\t\t" + product.Quantity + "\t" + product.Price);
                }

                Console.WriteLine();

                Console.WriteLine("LIST OF PRODCUT \n");

                string result = listProduct.Aggregate<Product, string, string>("Product Names:\t "+
                                            String.Empty,// seed value
                                            (str, p) => str += p.Product_Name + ",",
                                            str => str.Substring(0, str.Length - 1));

    f;            Console.WriteLine(result);
                Console.ReadLine();
            }
        }
    }
}

Output