GroupBy in LINQ Query in Entity Framework Core

Use GroupBy allows to group elements from a database using Entity Framework Core.

using EntityFrameworkCore_ConsoleApp.Models;

namespace EntityFrameworkCore_ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var databaseContext = new DatabaseContext())
            {
                var groups = (from product in databaseContext.Products
                              group product by product.CategoryId into gr
                              select new
                              {
                                  gr.Key,
                                  CountProduct = gr.Count(),
                                  SumQuantities = gr.Sum(productOfGroup => productOfGroup.Price),
                                  MinPrice = gr.Min(productOfGroup => productOfGroup.Price),
                                  MaxPrice = gr.Max(productOfGroup => productOfGroup.Price),
                                  AvgPrice = gr.Average(productOfGroup => productOfGroup.Price)
                              }).ToList();
                groups.ForEach(group =>
                {
                    Console.WriteLine("Category Id: " + group.Key);
                    Console.WriteLine("Count Product: " + group.CountProduct);
                    Console.WriteLine("Sum Quantities: " + group.SumQuantities);
                    Console.WriteLine("Min Price: " + group.MinPrice);
                    Console.WriteLine("Max Price: " + group.MaxPrice);
                    Console.WriteLine("Avg Price: " + group.AvgPrice);
                    Console.WriteLine("---------------------");
                });
            }
        }
    }
}

Output

Category Id: 1
Count Product: 3
Sum Quantities: 35
Min Price: 5
Max Price: 20
Avg Price: 11.666666666666666
---------------------
Category Id: 2
Count Product: 2
Sum Quantities: 19
Min Price: 4
Max Price: 15
Avg Price: 9.5
---------------------
Category Id: 3
Count Product: 3
Sum Quantities: 79
Min Price: 17
Max Price: 43
Avg Price: 26.333333333333332
---------------------

You can use combination of GroupBy and Where as below:

using EntityFrameworkCore_ConsoleApp.Models;

namespace EntityFrameworkCore_ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var databaseContext = new DatabaseContext())
            {
                var groups = (from product in databaseContext.Products
                              where product.Status == true 
                              group product by product.CategoryId into gr
                              select new
                              {
                                  gr.Key,
                                  CountProduct = gr.Count(),
                                  SumQuantities = gr.Sum(productOfGroup => productOfGroup.Price),
                                  MinPrice = gr.Min(productOfGroup => productOfGroup.Price),
                                  MaxPrice = gr.Max(productOfGroup => productOfGroup.Price),
                                  AvgPrice = gr.Average(productOfGroup => productOfGroup.Price)
                              }).ToList();
                groups.ForEach(group =>
                {
                    Console.WriteLine("Category Id: " + group.Key);
                    Console.WriteLine("Count Product: " + group.CountProduct);
                    Console.WriteLine("Sum Quantities: " + group.SumQuantities);
                    Console.WriteLine("Min Price: " + group.MinPrice);
                    Console.WriteLine("Max Price: " + group.MaxPrice);
                    Console.WriteLine("Avg Price: " + group.AvgPrice);
                    Console.WriteLine("---------------------");
                });
            }
        }
    }
}

Output

Category Id: 1
Count Product: 1
Sum Quantities: 10
Min Price: 10
Max Price: 10
Avg Price: 10
---------------------
Category Id: 2
Count Product: 1
Sum Quantities: 15
Min Price: 15
Max Price: 15
Avg Price: 15
---------------------
Category Id: 3
Count Product: 2
Sum Quantities: 36
Min Price: 17
Max Price: 19
Avg Price: 18
---------------------

You can use combination of GroupBy and Skip and Where as below:

using EntityFrameworkCore_ConsoleApp.Models;

namespace EntityFrameworkCore_ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var databaseContext = new DatabaseContext())
            {
                var groups = (from product in databaseContext.Products
                              where product.Status == true 
                              group product by product.CategoryId into gr
                              select new
                              {
                                  gr.Key,
                                  CountProduct = gr.Count(),
                                  SumQuantities = gr.Sum(productOfGroup => productOfGroup.Price),
                                  MinPrice = gr.Min(productOfGroup => productOfGroup.Price),
                                  MaxPrice = gr.Max(productOfGroup => productOfGroup.Price),
                                  AvgPrice = gr.Average(productOfGroup => productOfGroup.Price)
                              }).Skip(1).ToList();
                groups.ForEach(group =>
                {
                    Console.WriteLine("Category Id: " + group.Key);
                    Console.WriteLine("Count Product: " + group.CountProduct);
                    Console.WriteLine("Sum Quantities: " + group.SumQuantities);
                    Console.WriteLine("Min Price: " + group.MinPrice);
                    Console.WriteLine("Max Price: " + group.MaxPrice);
                    Console.WriteLine("Avg Price: " + group.AvgPrice);
                    Console.WriteLine("---------------------");
                });
            }
        }
    }
}

Output

Category Id: 2
Count Product: 1
Sum Quantities: 15
Min Price: 15
Max Price: 15
Avg Price: 15
---------------------
Category Id: 3
Count Product: 2
Sum Quantities: 36
Min Price: 17
Max Price: 19
Avg Price: 18
---------------------

You can use combination of GroupBy and Skip and Take and Where as below:

using EntityFrameworkCore_ConsoleApp.Models;

namespace EntityFrameworkCore_ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var databaseContext = new DatabaseContext())
            {
                var groups = (from product in databaseContext.Products
                              where product.Status == true 
                              group product by product.CategoryId into gr
                              select new
                              {
                                  gr.Key,
                                  CountProduct = gr.Count(),
                                  SumQuantities = gr.Sum(productOfGroup => productOfGroup.Price),
                                  MinPrice = gr.Min(productOfGroup => productOfGroup.Price),
                                  MaxPrice = gr.Max(productOfGroup => productOfGroup.Price),
                                  AvgPrice = gr.Average(productOfGroup => productOfGroup.Price)
                              }).Skip(1).Take(1).ToList();
                groups.ForEach(group =>
                {
                    Console.WriteLine("Category Id: " + group.Key);
                    Console.WriteLine("Count Product: " + group.CountProduct);
                    Console.WriteLine("Sum Quantities: " + group.SumQuantities);
                    Console.WriteLine("Min Price: " + group.MinPrice);
                    Console.WriteLine("Max Price: " + group.MaxPrice);
                    Console.WriteLine("Avg Price: " + group.AvgPrice);
                    Console.WriteLine("---------------------");
                });
            }
        }
    }
}

Output

Category Id: 2
Count Product: 1
Sum Quantities: 15
Min Price: 15
Max Price: 15
Avg Price: 15
---------------------

You can use combination of GroupBy and Skip and Take and OrderBy and Where as below:

using EntityFrameworkCore_ConsoleApp.Models;

namespace EntityFrameworkCore_ConsoleApp
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (var databaseContext = new DatabaseContext())
            {
                var groups = (from product in databaseContext.Products
                              where product.Status == true
                              group product by product.CategoryId into gr
                              select new
                              {
                                  gr.Key,
                                  CountProduct = gr.Count(),
                                  SumQuantities = gr.Sum(productOfGroup => productOfGroup.Price),
                                  MinPrice = gr.Min(productOfGroup => productOfGroup.Price),
                                  MaxPrice = gr.Max(productOfGroup => productOfGroup.Price),
                                  AvgPrice = gr.Average(productOfGroup => productOfGroup.Price)
                              }).Skip(1).Take(2).OrderByDescending(group => group.SumQuantities).ToList();
                groups.ForEach(group =>
                {
                    Console.WriteLine("Category Id: " + group.Key);
                    Console.WriteLine("Count Product: " + group.CountProduct);
                    Console.WriteLine("Sum Quantities: " + group.SumQuantities);
                    Console.WriteLine("Min Price: " + group.MinPrice);
                    Console.WriteLine("Max Price: " + group.MaxPrice);
                    Console.WriteLine("Avg Price: " + group.AvgPrice);
                    Console.WriteLine("---------------------");
                });
            }
        }
    }
}

Output

Category Id: 3
Count Product: 2
Sum Quantities: 36
Min Price: 17
Max Price: 19
Avg Price: 18
---------------------
Category Id: 2
Count Product: 1
Sum Quantities: 15
Min Price: 15
Max Price: 15
Avg Price: 15
---------------------