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 to calculate the total sales amount generated by ProductCategory. We can achieve this by using the 'group by' clause. To do it manually, we would take each Electronics record and add up their sale amount, then do the same for Books records. Essentially, we first group the records by ProductCategory and then sum the saleamount column within each group, which is why we use the 'group by' clause.

So, the query groups the ProductSales collection by ProductCategory and calculates the total sales amount for each product category. The result is stored in the sales variable as an enumerable collection of anonymous objects with ProductCategory and TotalSale properties.

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)
 };

Output:

Entity framework group by multiple columns

Let's consider another example where we use 'group by' with multiple columns. Now, we're not only grouping by ProductCategory but also by the store's Country. For instance, we want to know the total sales generated for each product category—Electronics, Books, Clothing, Computers, and Sports—in the USA. This means we're grouping by both the ProductCategory and Country columns. Essentially, we're first grouping the total sales by country and then further by ProductCategory.

The query groups the ProductSales list by a combination of StoreCountry and ProductCategory, and calculates the total sales amount for each unique combination. The result is stored in the sales variable as an enumerable collection of anonymous objects with StoreCountry, ProductCategory, and TotalSale properties.

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)
};

Output:

Entity framework group by with Order BY Clause and count

Let's illustrate with an example. We're displaying the total sales by country and ProductCategory, and we also want to include the total number of orders. To find the total number of orders, we can use the Count() function. Looking at the ProductSales table, we have 14 records. Now, we'll determine the total number of orders within the ProductSales table, and we'll use the 'order by' clause along with 'group by'.

The below query groups the ProductSales list by a combination of StoreCountry and ProductCategory, calculates the total sales amount and the total number of orders for each group, and then sorts the result by the TotalSales property in ascending order. 

The result is stored in the sales variable as an enumerable collection of anonymous objects with StoreCountry, ProductCategory, TotalSales, and TotalOrder properties.

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);

Output:

Entity framework group by example with where clause

Now, suppose we want to filter the output result to only include the ProductSales for the Books ProductCategory. To filter the records, we can use the 'where' clause, specifying that the ProductCategory is equal to 'Books'.

So, this query is similar to the above one, but it includes an additional filter to only consider records where the ProductCategory is "Books". The result is stored in the sales variable as an enumerable collection of anonymous objects with StoreCountry, ProductCategory, TotalSales, and TotalOrder properties, sorted by TotalSales in ascending order.

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);

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),
 });

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),
});

Output:

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

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.