Having in Lambda Expressions

Create Database

Create a database with the name is LearnEntityFramework. This database have 2 tables: Category table and Product table. Category table and Product table have a One to Many. One category can have many products and One product belongs to one and only one category.

/* Table structure for table 'category' */

CREATE TABLE Category(
	Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(50) NULL
)

/* Dumping data for table `category` */

GO
INSERT Category (Name) VALUES ('Mobile')
INSERT Category (Name) VALUES ('Laptop')
INSERT Category (Name) VALUES ('Tivi')

/* Table structure for table `product` */

GO
CREATE TABLE Product (
	Id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(50) NULL,
	Price money NULL,
	Quantity int NULL,
	CreationDate date NULL,
	Status bit NULL,
	CategoryId int NULL,
	FOREIGN KEY(CategoryId) REFERENCES Category(Id)
)

/* Dumping data for table `product` */

GO
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 1', 10.0000, 2, '2017-12-20', 1, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 2', 24.0000, 4, '2017-12-21', 0, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Mobile 3', 26.0000, 9, '2017-11-14', 1, 1)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Laptop 1', 15.0000, 7, '2011-06-10', 1, 2)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Laptop 2', 21.0000, 16, '2011-09-19', 0, 2)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Tivi 1', 18.0000, 11, '2016-11-20', 1, 3)
INSERT Product (Name, Price, Quantity, CreationDate, Status, CategoryId) VALUES ('Tivi 2', 25.0000, 17, '2016-12-05', 0, 3)

Database Diagram

Structure of Category Table

Data of Category Table

Structure of Product Table

Data of Product Table

ADO.NET Entity Data Model

Use the Entity Wizard to create an Entity Data Model From Database in Visual Studio.

Structure of Product

Having in Lambda Expressions

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace LearnEntityFramework
{
    class Program
    {
        static void Main(string[] args)
        {
            LearnEntityFrameworkDB db = new LearnEntityFrameworkDB();

            var groups = db.Products
                            .GroupBy(p => p.CategoryId)
                            .Select(g => new
                            {
                                g.Key,
                                CountProduct = g.Count(),
                                SumQuantities = g.Sum(p => p.Quantity),
                                MinPrice = g.Min(p => p.Price),
                                MaxPrice = g.Max(p => p.Price),
                                AvgPrice = g.Average(p => p.Price)
                            })
                            .Where(g => g.SumQuantities > 20) // Having
                            .ToList();
            foreach (var group in groups)
            {
                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("==========================");

            }

            Console.ReadLine();
        }
    }
}

Output

Category Id: 2
Count Product: 2
Sum Quantities: 23
Min Price: 15.0000
Max Price: 21.0000
Avg Price: 18.0000
==========================
Category Id: 3
Count Product: 2
Sum Quantities: 28
Min Price: 18.0000
Max Price: 25.0000
Avg Price: 21.5000
==========================