Entity Framework : Multiple Join with group by dates and count

If you've just started working with Entity Framework and you're unsure how to perform joins and groupings using Entity Framework queries, then you're in the right place. In this article, we'll discuss performing multiple joins with grouping by dates and counts.

We'll write a query for this scenario using three tables: Customer table with fields Id, Name, Email, Address;

Order Table with fields Id, ProductId, Quantity, Price, UserId, SoldAt; and Product table with fields Id, ProductName, Category, Price, CreatedAt.

Below are SQL create and sample insert scripts for the three tables: Customer, Order, and Product.

Customer Table:

CREATE TABLE Customer (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    Address VARCHAR(255)
);

INSERT INTO Customer (Id, Name, Email, Address)
VALUES
    (1, 'John Doe', 'john@yahoo.com', '123 Main St'),
    (2, 'Jane Smith', 'jane@yahoo.com', '456 Elm St');


Order Table:

CREATE TABLE [Order] (
    Id INT PRIMARY KEY,
    ProductId INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    UserId INT,
    SoldAt DATETIME,
    FOREIGN KEY (ProductId) REFERENCES Product(Id),
    FOREIGN KEY (UserId) REFERENCES Customer(Id)
);

INSERT INTO [Order] (Id, ProductId, Quantity, Price, UserId, SoldAt)
VALUES
    (1, 1, 2, 10.99, 1, '2024-04-08 09:30:00'),
    (2, 2, 1, 5.99, 2, '2024-04-08 10:15:00');


Product Table:

CREATE TABLE Product (
    Id INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    Price DECIMAL(10, 2),
    CreatedAt DATETIME
);

INSERT INTO Product (Id, ProductName, Category, Price, CreatedAt)
VALUES
    (1, 'Smartphone', 'Electronics', 499.99, '2024-01-15 08:00:00'),
    (2, 'Laptop', 'Electronics', 899.99, '2024-02-20 10:00:00');




Entity Framework Query:


var result = from c in context.Customer
             join o in context.Order on c.Id equals o.UserId
             join p in context.Product on o.ProductId equals p.Id
             group new { o, p } by new { SaleDate = EntityFunctions.TruncateTime(o.SoldAt), p.Category } into g
             select new
             {
                 SaleDate = g.Key.SaleDate,
                 Category = g.Key.Category,
                 TotalOrders = g.Count(),
                 TotalQuantity = g.Sum(x => x.o.Quantity)
             };

Lambda Query:

var result = context.Customer
    .Join(context.Order, c => c.Id, o => o.UserId, (c, o) => new { Customer = c, Order = o })
    .Join(context.Product, co => co.Order.ProductId, p => p.Id, (co, p) => new { CustomerOrder = co, Product = p })
    .GroupBy(
        x => new { SaleDate = EntityFunctions.TruncateTime(x.CustomerOrder.Order.SoldAt), x.Product.Category },
        (key, group) => new
        {
            SaleDate = key.SaleDate,
            Category = key.Category,
            TotalOrders = group.Count(),
            TotalQuantity = group.Sum(x => x.CustomerOrder.Order.Quantity)
        });

SQL query:

SELECT 
    CONVERT(date, o.SoldAt) AS SaleDate, 
    p.Category, 
    COUNT(*) AS TotalOrders, 
    SUM(o.Quantity) AS TotalQuantity
FROM 
    Customer c
JOIN 
    [Order] o ON c.Id = o.UserId
JOIN 
    Product p ON o.ProductId = p.Id
GROUP BY 
    CONVERT(date, o.SoldAt), p.Category;

Firstly, we're accessing our database context to query data from the `Customer`, `Order`, and `Product` tables and then start by selecting data from the `Customer` table and then proceed to join it with the `Order` table based on the `Id` of the customer matching the `UserId` in the order. Then, we further join the `Order` table with the `Product` table, linking them through the `ProductId` in the order and the `Id` in the product.

Next, we group the joined data. We're grouping it by two factors: the truncated date of when the order was sold (`SoldAt` field in the `Order` table) and the category of the product. This means that all orders sold on the same day with products from the same category will be grouped together.

We're extracting the grouped key information, which includes the sale date and the product category and we're calculating two aggregate values within each group: the total number of orders (`TotalOrders`) and the total quantity of products ordered (`TotalQuantity`). We achieve this using the `Count()` method to count the number of elements in each group and the `Sum()` method to sum up the quantities of products ordered in each group.

Above query retrieves data from our database, performs joins and groupings, and then calculates aggregate values based on the grouped data, giving us insights into total orders and quantities sold for each date and product category combination.