GroupBy : Entity Framework Using Various Operators Such as `Sum`, `Min`, and `Max`.

Recently, I was working on an ecommerce project where I needed to create various reports on sold products. To achieve this, I utilized the `GroupBy` operation in my application. My project is based on ASP.NET Core and Entity Framework, so I decided to write an article on this topic to share my experience. 

In this article, we will learn how to perform Entity Framework Group By operations using various operators such as `Sum`, `Min`, and `Max`."

CREATE TABLE ProductOrder (
    Id INT PRIMARY KEY AUTO_INCREMENT,
    ProductName VARCHAR(255) NOT NULL,
    ProductCategory VARCHAR(100),
    ProductDescription TEXT,
    SoldPrice DECIMAL(10, 2) NOT NULL,
    SoldQuantity INT NOT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO ProductOrder (ProductName, ProductCategory, ProductDescription, SoldPrice, SoldQuantity, CreatedAt) 
VALUES 
('Laptop', 'Electronics', 'High-performance laptop with SSD', 1200.00, 2, '2024-04-08 10:00:00'),
('Smartphone', 'Electronics', '5G smartphone with OLED display', 800.00, 3, '2024-04-08 10:15:00'),
('Headphones', 'Electronics', 'Noise-cancelling headphones', 150.00, 5, '2024-04-08 10:30:00'),
('T-shirt', 'Clothing', 'Cotton t-shirt with graphic print', 25.00, 10, '2024-04-08 10:45:00'),
('Backpack', 'Accessories', 'Water-resistant backpack with laptop compartment', 50.00, 4, '2024-04-08 11:00:00');

Entity Framework Group By with Max Date and Count

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

public class ProductOrder
{
    public int Id { get; set; }
    public string ProductName { get; set; }
    public string ProductCategory { get; set; }
    public string ProductDescription { get; set; }
    public decimal SoldPrice { get; set; }
    public int SoldQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class ProductDbContext : DbContext
{
    public DbSet<ProductOrder> ProductOrders { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Configure your database connection here
        optionsBuilder.UseSqlServer("YourConnectionString");
    }
}

class Program
{
    static void Main(string[] args)
    {
        using (var dbContext = new ProductDbContext())
        {
            var maxDateAndCount = dbContext.ProductOrders
                .GroupBy(p => p.ProductName)
                .Select(g => new
                {
                    ProductName = g.Key,
                    MaxDate = g.Max(p => p.CreatedAt),
                    OrderCount = g.Count()
                })
                .ToList();

            foreach (var item in maxDateAndCount)
            {
                Console.WriteLine($"Product Name: {item.ProductName}, Max Date: {item.MaxDate}, Order Count: {item.OrderCount}");
            }
        }
    }
}

Group by and Sum column using Entity Framework

 using (var dbContext = new ProductDbContext())
        {
            var result = dbContext.ProductOrders
                .GroupBy(p => p.ProductName)
                .Select(g => new
                {
                    ProductName = g.Key,
                    TotalSoldPrice = g.Sum(p => p.SoldPrice),
                    TotalSoldQuantity = g.Sum(p => p.SoldQuantity)
                })
                .ToList();

            foreach (var item in result)
            {
                Console.WriteLine($"Product Name: {item.ProductName}, Total Sold Price: {item.TotalSoldPrice}, Total Sold Quantity: {item.TotalSoldQuantity}");
            }
        }

EntityFramework how to select Max(Column), Count(*) From ProductTable

using (var dbContext = new ProductDbContext())
        {
            var result = dbContext.ProductOrders
                .GroupBy(p => 1)
                .Select(g => new
                {
                    MaxSoldPrice = g.Max(p => p.SoldPrice),
                    TotalRowCount = g.Count()
                })
                .FirstOrDefault();

            Console.WriteLine($"Maximum Sold Price: {result.MaxSoldPrice}");
            Console.WriteLine($"Total Rows in ProductOrders Table: {result.TotalRowCount}");
        }

using (var dbContext = new ProductDbContext())
        {
            var result = dbContext.ProductOrders
                .GroupBy(p => p.ProductName)
                .Select(g => new
                {
                    ProductName = g.Key,
                    TotalSoldPrice = g.Sum(p => p.SoldPrice),
                    MinSoldPrice = g.Min(p => p.SoldPrice)
                })
                .ToList();

            foreach (var item in result)
            {
                Console.WriteLine($"Product Name: {item.ProductName}, Total Sold Price: {item.TotalSoldPrice}, Min Sold Price: {item.MinSoldPrice}");
            }
        }

using (var dbContext = new ProductDbContext())
        {
            var result = dbContext.ProductOrders
                .GroupBy(p => p.ProductName)
                .Select(g => new
                {
                    ProductName = g.Key,
                    TotalSoldPrice = g.Sum(p => p.SoldPrice),
                    MaxSoldPrice = g.Max(p => p.SoldPrice)
                })
                .ToList();

            foreach (var item in result)
            {
                Console.WriteLine($"Product Name: {item.ProductName}, Total Sold Price: {item.TotalSoldPrice}, Max Sold Price: {item.MaxSoldPrice}");
            }
        }