Entity framework Group By:Sum,Count,Min ,Max example

While working with Entity Framework SQL developers sometimes get struck on how to use group by clause with the sum, count min, and max, etc.We will use different types of an aggregate functions, and group by clause, to show the example.

Entity framework group by example

Now let’s understand with an example, that we are going to create a table, first for ProductSales includes ProductName,ProductCategory, StoreCountry, SaleAmount, and OrderDate.

ProductSales Table

Id	ProductName	StoreCountry	SaleAmount	ProductCategory	OrderDate
1	TVs	India	2000	Electronics	3/29/2022 13:47
2	Harry Potter	USA	1500	Books	3/30/2022 13:47
3	Tanis	UK	1200	Sports	4/15/2022 13:47
4	Jeans	India	3000	Clothing	4/15/2022 13:47
5	Ball	UK	2200	Sports	4/16/2022 13:47
6	Cap	USA	800	Clothing	4/17/2022 13:47
7	Keyboard	India	600	Computers	4/20/2022 13:47
8	Top	USA	1400	Clothing	4/22/2022 13:47
9	Bat	India	1600	Sports	5/11/2022 13:47
10	Laptop	UK	900	Computers	5/12/2022 13:47
11	Refrigerators	UK	700	Electronics	5/13/2022 13:47
12	Alice’s Adventures	India	600	Books	5/16/2022 13:47
13	T-shirt	USA	550	Clothing	5/18/2022 13:47
14	Refrigerators	India	780	Electronics	5/21/2022 13:47

Entity framework Group by sum

I want the total saleamount that we are generating by ProductCategory, let’s calculate Using group by. If we have to do it manually, we will take each Electronics record and then add the saleamount .
Similarly, we will take each Books record and add their saleamount . so basically we first group the records by ProductCategory and then sum the saleamount column across that group and that’s why we are going to use group by clause.

Sql Query:

Select ProductCategory, SUM(SaleAmount) as TotalSale
from ProductSales
Group by ProductCategory

Linq Query:

 var sales = from r in ProductSales
                          group r by r.ProductCategory into gp
                          select new
                          {
                              ProductCategory = gp.Key,
                              TotalSale = gp.Sum(a => a.SaleAmount)
                          };

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.ProductCategory}"" ""{r.TotalSale}"""));
            }

Output:

ProductCategory	SaleAmount
Books	2100
Clothing	5750
Computers	1500
Electronics	3480
Sports	5000

Entity framework group by multiple columns

Let’s take one more example i.e using group by multiple columns, now we are going not only by ProductCategory, but we want to include the store Country. For example, in the USA, how much is the total sales that we are generating for each product category i.e Electronics, Books, Clothing, Computers, and Sports. So we are grouping by multiple columns i .e by ProductCategory and Country columns. we are grouping total sales first by country and then by ProductCategory.

Sql Query:

Select StoreCountry, ProductCategory, SUM(SaleAmount) as TotalSale
from ProductSales
Group by StoreCountry, ProductCategory

Linq Query:

var sales = from r in ProductSales
                          group r by new { r.StoreCountry, r.ProductCategory} into gp
                          select new
                          {
                              StoreCountr=gp.Key.StoreCountry,
                              ProductCategory = gp.Key.ProductCategory,
                              TotalSale = gp.Sum(a => a.SaleAmount)
                          };

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.ProductCategory}"" ""{r.TotalSale}"""));
            }

Output:

StoreCountry	ProductCategory	TotalSale
India	Books	600
USA	Books	1500
India	Clothing	3000
USA	Clothing	2750
India	Computers	600
UK	Computers	900
India	Electronics	2780
UK	Electronics	700
India	Sports	1600
UK	Sports	3400

Entity framework group by with Order BY Clause and count

let’s understand with an example, we are showing the total sales by country and ProductCategory, we want the total order as well.
Now, if we want to find out the total Order, we can use the Count() function if you look at the ProductSales table, we’ve got 14 records in the table. Now we are going to find the total number of the order within my ProductSales table, we will also use order by clause with Group By.

Sql Query:

Select StoreCountry, ProductCategory, SUM(SaleAmount) as TotalSales,COUNT(Id) as TotalOrder
from ProductSales
group by StoreCountry, ProductCategory
order by TotalSales

Linq Query:

  var sales = (from r in ProductSales
                           group r by new { r.StoreCountry, r.ProductCategory} into gp
                           select new
                           {
                               StoreCountry = gp.Key.StoreCountry,
                               ProductCategory = gp.Key.ProductCategory,
                               TotalSales = gp.Sum(item => item.SaleAmount),
                               TotalOrder = gp.Count()
                           }).OrderBy(a => a.TotalSales);

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.StoreCountry}"" ""{r.ProductCategory}""  ""{r.TotalSales}"" ""{r.TotalOrder}"""));
            }

Output:

StoreCountry	ProductCategory	TotalSales	TotalOrder
India	Books	600	1
India	Computers	600	1
UK	Electronics	700	1
UK	Computers	900	1
USA	Books	1500	1
India	Sports	1600	1
USA	Clothing	2750	3
India	Electronics	2780	2
India	Clothing	3000	1
UK	Sports	3400	2

Entity framework group by example with where clause

Now let’s say, we want to filter the output result and we only want Books ProductCategory ProductSales, to filter the records, we can use the where clause, where you can say ProductCategory is equal to “Books “.

Sql Query:

Select StoreCountry, ProductCategory, SUM(SaleAmount) as TotalSales,COUNT(Id) as TotalOrder
from ProductSales
where ProductCategory='Books'
group by StoreCountry, ProductCategory

Linq Query:

var sales = (from r in ProductSales
                         where r.ProductCategory == "Books"
                         group r by new { r.StoreCountry, r.ProductCategory } into gp
                         select new
                         {
                             StoreCountry = gp.Key.StoreCountry,
                             ProductCategory = gp.Key.ProductCategory,
                             TotalSales = gp.Sum(item => item.SaleAmount),
                             TotalOrder = gp.Count()
                         }).OrderBy(a => a.TotalSales);

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.StoreCountry}"" ""{r.ProductCategory}""  ""{r.TotalSales}"" ""{r.TotalOrder}"""));
            }

Output:

StoreCountry	ProductCategory	TotalSales	TotalOrder
India	Books	600	1
USA	Books	1500	1

Entity framework group by max

Lets’ say, I want the max product sales that we are generating by StoreCountry.

Sql Query:

Select ProductCategory, Max(SaleAmount) as TotalSales
from ProductSales
Group by ProductCategory

Linq Query:

 var sales = (from r in ProductSales
                           group r by r.ProductCategory into gp
                           select new
                           {
                               ProductCategory = gp.Key,
                               TotalSales = gp.Max(item => item.SaleAmount),
                           });

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.ProductCategory}""  ""{r.TotalSales}"" ""{r.TotalSales}"""));
            }

Output:

ProductCategory	TotalSales
Books	1500
Clothing	3000
Computers	900
Electronics	2000
Sports	2200

Entity framework group by min

Lets’ say, I want the min product sales that we are generating by StoreCountry.

Sql Query:

Select ProductCategory, MIN(SaleAmount) as TotalSales
from ProductSales
Group by ProductCategory

Linq Query:

var sales = (from r in ProductSales
                           group r by r.ProductCategory into gp
                           select new
                           {
                               ProductCategory = gp.Key,
                               TotalSales = gp.Min(item => item.SaleAmount),
                           });

            foreach (var r in sales)
            {
                Debug.Print(($@"""{r.ProductCategory}""  ""{r.TotalSales}"" ""{r.TotalSales}"""));
            }

Output:

ProductCategory	TotalSales
Books	600
Clothing	550
Computers	600
Electronics	700
Sports	1200

Below is the table script which has some dummy data and you can get the SQL table script for your testing.

CREATE TABLE [dbo].[ProductSales](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [nvarchar](max) NULL,
    [StoreCountry] [nvarchar](max) NULL,
    [SaleAmount] [decimal](18, 2) NULL,
    [ProductCategory] [nvarchar](max) NULL,
    [OrderDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductSales] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET IDENTITY_INSERT [dbo].[ProductSales] ON 
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (1, 'TVs', 'India', CAST(2000.00 AS Decimal(18, 2)), 'Electronics', CAST('2022-03-29T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (2, 'Harry Potter', 'USA', CAST(1500.00 AS Decimal(18, 2)), 'Books', CAST('2022-03-30T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (3, 'Tanis', 'UK', CAST(1200.00 AS Decimal(18, 2)), 'Sports', CAST('2022-04-15T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (4, 'Jeans', 'India', CAST(3000.00 AS Decimal(18, 2)), 'Clothing', CAST('2022-04-15T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (5, 'Ball', 'UK', CAST(2200.00 AS Decimal(18, 2)), 'Sports', CAST('2022-04-16T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (6, 'Cap', 'USA', CAST(800.00 AS Decimal(18, 2)), 'Clothing', CAST('2022-04-17T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (7, 'Keyboard', 'India', CAST(600.00 AS Decimal(18, 2)), 'Computers', CAST('2022-04-20T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (8, 'Top', 'USA', CAST(1400.00 AS Decimal(18, 2)), 'Clothing', CAST('2022-04-22T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (9, 'Bat', 'India', CAST(1600.00 AS Decimal(18, 2)), 'Sports', CAST('2022-05-11T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (10, 'Laptop', 'UK', CAST(900.00 AS Decimal(18, 2)), 'Computers', CAST('2022-05-12T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (11, 'Refrigerators', 'UK', CAST(700.00 AS Decimal(18, 2)), 'Electronics', CAST('2022-05-13T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (12, 'Alice''s Adventures', 'India', CAST(600.00 AS Decimal(18, 2)), 'Books', CAST('2022-05-16T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (13, 'T-shirt', 'USA', CAST(550.00 AS Decimal(18, 2)), 'Clothing', CAST('2022-05-18T13:47:39.110' AS DateTime))
GO
INSERT [dbo].[ProductSales] ([Id], [ProductName], [StoreCountry], [SaleAmount], [ProductCategory], [OrderDate]) VALUES (14, 'Refrigerators', 'India', CAST(780.00 AS Decimal(18, 2)), 'Electronics', CAST('2022-05-21T13:47:39.110' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[ProductSales] OFF

We used to open an association with the information base, make a DataSet to get or present the information to the data set, convert information from the DataSet to .NET items or the other way around to apply business rules. This was an unwieldy and mistake inclined process. Microsoft has given a system called Element Framework to robotize every one of these information base related exercises for your application.

Substance Framework is an open-source ORM system for .NET applications upheld by Microsoft. It empowers engineers to work with information utilizing objects of space explicit classes without zeroing in on the hidden data set tables and segments where this information is put away. With the Entity Framework, engineers can work at a more elevated level of deliberation when they manage information, and can make and keep up with information situated applications with less code contrasted and customary applications.

EF Core is a cross-stage system which can run on Windows, Linux and Mac.
EF (Entity Framework) makes an EDM (Entity Data Model) in view of POCO (Plain Old CLR Object) elements with get/set properties of various information types.
It utilizes this model while questioning or saving substance information to the fundamental data set.
EF permits us to utilize LINQ inquiries (C#/VB.NET) to recover information from the hidden data set. The information base supplier will make an interpretation of this LINQ questions to the data set explicit inquiry language (for example SQL for a social data set).
EF additionally permits us to execute crude SQL inquiries straightforwardly to the information base.
EF monitors changes happened to cases of your substances (Property estimations) which should be submitted to the information base.

The post [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax appeared first on Software Development | Programming Tutorials.

Ashok Patel

I'm a software engineer, having good experience in software programming web designing with great command on ASP.NET, React JS, Angular JS,.NET Core HTML5, JavaScript, T-SQL, JQuery. Also have great experience in Electronics and electrical engineers design.

Entity framework Group By:Sum,Count,Min ,Max example

Entity framework group by example

Entity framework Group by sum

Entity framework group by multiple columns

Entity framework group by with Order BY Clause and count

Entity framework group by example with where clause

Entity framework group by max

Entity framework group by min

Read more article

Read More Articles

Ashok Patel