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.



Read More Articles