Entity Framework Group By

Updated February 15, 2023

Introduction to Entity Framework Group By

Entity Framework Group By is an ORM (Object Relational Mapping) which proposes an automated mechanism to developers for retrieving and storing the data in the database. We use the aggregate function method to perform the Group-By operation on data in Entity Framework. The aggregate functions are Sum, Max, and Count. The group By function is mainly used to group the result of the query by using columns like one or more columns.

What is Entity Framework Group By?

The Group-By statement is essentially used for aggregating functions, which is used for grouping the result of queries with the help of columns. We use the aggregate function method to group the development to achieve the Group-By function on data in the Entity Framework.

Syntax:

SELECT [Name of Column]
FROM [Name of table-name]
WHERE [where-condition]
GROUP BY [specify columns]
ORDER BY [specify columns]

Using Entity Framework Group By

The latest version of Entity Framework releases with the group by statement, which will be executed at the database level. It makes the group by in LINQ to the database to set the query result containing the suitable group by statements.

Let’s see some examples as shown below:

using (var dbConn = new ApplicationDbContext())
{
var result = dbConn.Customers
  .GroupBy(cGrp => new { cGrp.IsDeleted })
  - - Use .Key and Count keywords
}

The above code is to get the list of customers grouped according to the value of the key field IsDeleted and to select the result into the key of grouping and the total number of customers in the group. At last, we are making the statement to execute using the ToList() function.

Code:

SELECT s.IsDeleted, COUNT(*) as[Count]
FROM [Customers] AS s
GROUP BY s.IsDeleted

We can also make use of having, which is used for filtering purposes and accords to aggregate functions’ results. For the collection of records, we are using aggregate functions.

Let’s see with one example:

Aggregate Function Count is used to counting the records used for counting purposes. For example, assume that we have to group the customers accords to their birth year; we have to take the groups with more people.

Code:

using (var dbConn = new ApplicationDbContext())
  {
   var result = dbConn.Customers
   .GroupBy(cGrp => new { cGrp.DOB.Year })
   .Where(cGrp => cGrp.Count() >= 2)
   .Select(cGrp => new { cGrp.Key, Count = cGrp.Count() })
   .ToList();
}

The SQL resulting query format for the same code is as follows:

Code:

SELECT DATEPART(year,c.DOB) AS[Birth-Year], COUNT (*) AS[Count]
FROM Customers AS c
GROUP BY DATEPART(year,c. DOB)
HAVING COUNT(*)>=2

Examples of Entity Framework Group By

Let’s see how to implement functionality in the application with one sample program. Firstly build a new project in VS; select File – New – Project and select Console Application; give the suitable name and click OK.

Next, create the Entity Model; right-click on the Project Name in the Solution Explorer Add – New Item – ADO.NET Entity Model and give the suitable name.

Choose Entity Framework Designer (EF Designer) from the database and click Next.

Establish a new Connection – Click OK.

Once connecting to the database, select the required tables to get the data back. Finally, click on the Finish button.

Once finished with the above process, next comes the coding part of using the group by function in the Entity Framework of the application; it retrieves the group of records in the tables by grouping it. To perform group by in Entity Framework, we need to use the group by the operator.

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleEF_GroupBy
{
 class Program
  {
   static void Main(string[] args)
    {
     using (conName db = new conName ())
      {
var getStores = (from cc in db.ProductMasters
group cc by cc.StoreId into cGrp
select new
{
  StoreID = cGrp.Key,
  Count = cGrp.Count()
});
Console.WriteLine("Entity Framework Group By");
Console.WriteLine("------------------------- \n ");
Console.WriteLine("Stores ID with corresponding number of product Counts\n");
Console.WriteLine("-----------------------------------------------\n");
Console.WriteLine("\tStore ID \tTotal No. of Products\n");
Console.WriteLine("-----------------------------------------------\n");
foreach (var resStores in getStores)
{
 Console.WriteLine("\t"+resStores.StoreID + "\t\t\t" + resStores.Count);
}
Console.WriteLine("-----------------------------------------------\n");
Console.ReadLine();
   }
  }
 }
}

In the above coding, we use the group by functionality to get the records according to the group. Here we use the Product Master table, which contains the product details like the product’s name, cost, unit measurement, etc. It also includes the details of the product from which store that particular product gets. So by using the group by function, we find out how many products we retrieved from the specific shop/ store based on the ids.

Code:

var getStores = (from cc in db.ProductMasters
  group cc by cc.StoreId into cGrp
  select new
{
  StoreID = cGrp.Key,
  Count = cGrp.Count()
});

Here the ProductMaster is the table’s name; it contains the product and store details by using a group by us finding out the count of products from the corresponding store/ shop. By using group-by, we retrieved the store ID and named it cGrp by using that variable; we retrieved the corresponding storied and counting of the store products; they are StoreID = cGrp.Key, count = cGrp.Count()

We retrieved the corresponding details from this code and looked at the below code; we displayed the above-retrieved data.

Code:

foreach (var resStores in getStores)
{
 Console.WriteLine("\t"+resStores.StoreID + "\t\t\t" + resStores.Count);
}

We store the previous get data to restore from displaying the StoresID and corresponding counting of that store’s products.

Output:

It can also display the name of the store with a corresponding number of products as follows.

Code:

var getStoresDetails= from p in db.ProductMasters
  join s in db.StoreMasters
  on p.StoreId equals s.StoreId
  select new { s.StoreName, p.StoreId } into x
  group x by new { x.StoreName } into grp
  select new
  {
   Stores = grp.Key.StoreName,
   Count = grp.Select(x => x.StoreId).Count()
  };
Console.WriteLine("Entity Framework Group By");
Console.WriteLine("------------------------- \n ");
Console.WriteLine("Stores Name with corresponding number of product Counts\n");
Console.WriteLine("-----------------------------------------------\n");
Console.WriteLine("\tStores Name \tTotal No. of Products\n");
Console.WriteLine("-----------------------------------------------\n");
foreach (var resStores in getStoresDetails)
{
 Console.WriteLine("\t" + resStores.Stores + "\t\t\t" + resStores.Count);
}
Console.WriteLine("-----------------------------------------------\n");
  Console.Read();

Output:

Conclusion

In this article, we have seen the Entity Framework Group By functionality which retrieves the records based on particular grouping columns.

Recommended Articles

This is a guide to Entity Framework Group By. Here we discuss the introduction, using entity framework group by and examples, respectively. You may also have a look at the following articles to learn more –

1. Entity Framework Core
2. ASP.NET Core Entity Framework
3. Entity Framework NuGet
4. Entity Framework Join