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
- Write a value which contain comma to a CSV file in c#?
- Reading CSV File with cells containing commas c#
- Split CSV with columns may contain ‘,’ Comma C#
- [Simple Way]-Cascading DropDownList in Asp.Net Mvc Using Jquery Ajax
- [Simple Way]-How to get data from database using JQuery Ajax in asp net MVC
- [Simple Way]-ASP.NET Core Upload Multiple File Web API model
- [Simple Way]- Image Upload in .NET Core Web API
- [Easy Way]-Receive File and other form data together in ASP.NET Core Web API
- Replace image in word document using C#
- How to add new rows to an existing word document table in C#
- Entity Framework - slow query after adding group by
- Entity Framework Joining Three Tables and Using Group Concat
- Entity Framework : Group by and Join
- Entity Framework Group By Then Order By
- Using Entity Framework to retrieve a group with subgroups each with their own subgroups
- Group by Date part only in LINQ to MySql in entity framework
- Entity Framework Core Owned Type Value Object throws required Primary Key to be defined error when used with IdentityUser. Why?
- How to connect XAMPP MySQL to ADO.NET Entity Framework using Visual Studio?
- How to create model without using Entity Framework in C#?
- How to parameterize concatenated string queries in Entity Framework Core
- Enforce Entity Framework to follow constraints defined in database
- Entity Framework and MSSQL json_value nested array in different row
- How to fetch records from more than 10 tables and shows the report out of them using Entity Framework 6?
- Repository pattern in C# without Entity Framework
- How to limit data only to logged in user in Entity Framework Core Identity Easily
- Entity Framework Oracle 00932 error "expected - got NCLOB"
- Groupby evaulating locally for Entity Framework Core 2.1.4 inside Azure Function version 2
- Entity Framework 6.20, keep having to change System.Data.Objects to System.Data.Entity.Core.Objects;
- Entity framework performance after query
- Entity Framework migration from MySQL to SQL Server and the Table names missing "dbo"
- How to improve the Update operation performance in Entity Framework 6
- CRUD Operations on Identity and custom table relationship using entity framework core
- C# WebApi Authorize access functions from dependency injection classes and entity framework
- Serialization of Entity Framework proxy classes with Newtonsoft.Json fails with error that the object is disposed
- Problem with entity framework 6 and mysql
- Entity Framework code first database not synced with models
- Entity Framework Core take one and skip other duplicate item in a table
- Access MySql with Entity Framework Core
- Entity Framework. Your Project References the latest version of entity framework error VS2017 MYSQL 8.0.12
- Entity Framework inserts only one record
- storing dataset in datagridview to a new access db file c#
- Get ParseUser by username
- Bind HashSet to DataGridView DataSource C#
- What is a good way of storing a lot of labels?
- List in class is null even though I made an instance of the class
- Accessing Validator block ErrorMessage
- How to marshal a double pointer?
- The calling thread cannot access this object error
- Type issue passing `this` as an argument to an overloaded method
- Implement inheritence in ASP.Net MVC6 Identity framework
- Communicating with IdentityServer4 with Owin Startup class in Asp.Net WebForms
- Do I need to use a property ClassId to represent a relationship in the Code First (Entity Framework)?
- Write Xaml Textblock in C#
- binding an asp.net checkbox on serverside
- Get items from DataTemplate for ArtOfTest controls
- Better alternative to Invoke() when changing state of other forms?
- Is there a way to get notified when an object expires?
- Yahoo "White Screen" with WebBrowser object
- Subscribing to new mail event of multiple impersonated user through one ExchangeService object
- ASP.NET Cannot perform runtime binding on a null reference - after I'm try to login in my website