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

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:

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:

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:

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:

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:

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:

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.