Entity Framework With Linq, inner Join, Group By, Order By Example

        
            -- Creating SQL script for Professor table
            CREATE TABLE Professor (
                Id INT PRIMARY KEY,
                Name VARCHAR(255),
                UniversityName VARCHAR(255),
                CourseId INT,
                Department VARCHAR(255),
                Salary DECIMAL(10, 2),
                CreatedAt DATETIME
            );

            -- Creating SQL script for Course table
            CREATE TABLE Course (
                Id INT PRIMARY KEY,
                CourseName VARCHAR(255)
            );

            -- Inserting records into Course table
            INSERT INTO Course (Id, CourseName) VALUES
                (1, 'Mathematics'),
                (2, 'Physics'),
                (3, 'Computer Science'),
                (4, 'Literature'),
                (5, 'Biology');

            -- Inserting records into Professor table
            INSERT INTO Professor (Id, Name, UniversityName, CourseId, Department, Salary, CreatedAt) VALUES
                (1, 'John Doe', 'Harvard University', 1, 'Mathematics Department', 60000.00, NOW()),
                (2, 'Jane Smith', 'Stanford University', 2, 'Physics Department', 65000.00, NOW()),
                (3, 'Michael Johnson', 'Massachusetts Institute of Technology', 3, 'Computer Science Department', 70000.00, NOW()),
                (4, 'Emily Brown', 'Yale University', 4, 'Literature Department', 55000.00, NOW()),
                (5, 'David Lee', 'University of California, Berkeley', 5, 'Biology Department', 62000.00, NOW()),
                (6, 'Sarah Wilson', 'California Institute of Technology', 1, 'Mathematics Department', 63000.00, NOW()),
                (7, 'Robert Martinez', 'Princeton University', 2, 'Physics Department', 58000.00, NOW()),
                (8, 'Jessica Taylor', 'University of Chicago', 3, 'Computer Science Department', 69000.00, NOW()),
                (9, 'Christopher Anderson', 'Columbia University', 4, 'Literature Department', 71000.00, NOW()),
                (10, 'Amanda Garcia', 'University of Michigan', 5, 'Biology Department', 60000.00, NOW()),
                (11, 'James Wilson', 'Harvard University', 1, 'Mathematics Department', 61000.00, NOW()),
                (12, 'Emma Johnson', 'Stanford University', 2, 'Physics Department', 64000.00, NOW()),
                (13, 'Daniel Smith', 'Massachusetts Institute of Technology', 3, 'Computer Science Department', 72000.00, NOW()),
                (14, 'Olivia Brown', 'Yale University', 4, 'Literature Department', 54000.00, NOW()),
                (15, 'William Lee', 'University of California, Berkeley', 5, 'Biology Department', 63000.00, NOW()),
                (16, 'Sophia Wilson', 'California Institute of Technology', 1, 'Mathematics Department', 65000.00, NOW()),
                (17, 'Liam Martinez', 'Princeton University', 2, 'Physics Department', 59000.00, NOW()),
                (18, 'Isabella Taylor', 'University of Chicago', 3, 'Computer Science Department', 70000.00, NOW()),
                (19, 'Mason Anderson', 'Columbia University', 4, 'Literature Department', 72000.00, NOW()),
                (20, 'Charlotte Garcia', 'University of Michigan', 5, 'Biology Department', 61000.00, NOW());
        
    

1

Linq expression for Join with GroupBy in EF Core

Let's say we have two tables: `Professor` and `Course`, and we want to join them based on a common property, then group the results by the `Department` property of the `Professor` entity. Here's how we can write the LINQ query:

        
            var result = from prof in dbContext.Professor
                         join course in dbContext.Course on prof.CourseId equals course.Id
                         group new { prof, course } by prof.Department into g
                         select new
                         {
                             Department = g.Key,
                             TotalProfessors = g.Count(),
                             AverageSalary = g.Average(x => x.prof.Salary)
                         };
        
    

In this query we start by defining the join operation between the `Professor` and `Course` tables based on the `CourseId` property and then we use the `group by` clause to group the joined result by the `Department` property of the `Professor` entity. 

After that inside the `select` clause, we store the grouped result into a new anonymous type, where we extract the `Department`, count the total number of professors (`TotalProfessors`), and calculate the average salary (`AverageSalary`) by averaging the `Salary` property from the `Professor` entity.

Lambda Expression:

        
            var result = dbContext.Professor
                        .Join(dbContext.Course,
                            prof => prof.CourseId,
                            course => course.Id,
                            (prof, course) => new { Professor = prof, Course = course })
                        .GroupBy(x => x.Professor.Department)
                        .Select(g => new
                        {
                            Department = g.Key,
                            TotalProfessors = g.Count(),
                            AverageSalary = g.Average(x => x.Professor.Salary)
                        });
        
    

When write the same query using the lambda expression, we use the `Join` method to perform the inner join between the `Professor` and `Course` tables, then `GroupBy` and `Select` methods to achieve the grouping and projection, similar to the LINQ query.

SQL Query:

        
            SELECT p.Department, COUNT(*) AS TotalProfessors, AVG(p.Salary) AS AverageSalary
            FROM Professor p
            INNER JOIN Course c ON p.CourseId = c.Id
            GROUP BY p.Department;
        
    

Abive SQL query performs an inner join between the `Professor` and `Course` tables, groups the result by the `Department` column of the `Professor` table, and calculates the total number of professors and the average salary for each department.

2

LINQ expression for a join with GroupBy in Entity Framework Core

Now ,let's say we want to write an Entity Framework LINQ query that joins the `Professor` and `Course` tables we created earlier, groups the results by the `Department` property of the `Professor` entity, and then calculates the total salary for each department. 

        
            var result = from prof in dbContext.Professor
                         join course in dbContext.Course on prof.CourseId equals course.Id
                         group prof by prof.Department into g
                         select new
                         {
                             Department = g.Key,
                             TotalSalary = g.Sum(x => x.Salary)
                         };
        
    

Lambda Expression:

        
            var result = dbContext.Professor
                        .Join(dbContext.Course,
                            prof => prof.CourseId,
                            course => course.Id,
                            (prof, course) => new { Professor = prof, Course = course })
                        .GroupBy(x => x.Professor.Department)
                        .Select(g => new
                        {
                            Department = g.Key,
                            TotalSalary = g.Sum(x => x.Professor.Salary)
                        });
        
    

SQL Query:

        
            SELECT p.Department, SUM(p.Salary) AS TotalSalary
            FROM Professor p
            INNER JOIN Course c ON p.CourseId = c.Id
            GROUP BY p.Department;
        
    

3

Join operation in Entity Framework with multiple

Now let's understand to perform a join operation in Entity Framework with multiple conditions using the `Professor` and `Course` tables we created earlier:

In below LINQ query, we are joining the `Professor` and `Course` tables based on two conditions: `CourseId` from `Professor` table and `Department` from `Professor` table, with `Id` from `Course` table and a specific department, let's say "Computer Science".

        
            var result = from prof in dbContext.Professor
                         join course in dbContext.Course on new { prof.CourseId, prof.Department } equals new { course.Id, Department = "Computer Science" }
                         select new
                         {
                             ProfessorName = prof.Name,
                             CourseName = course.CourseName
                         };
        

Lambda Expression:

        
            var result = dbContext.Professor
                        .Join(dbContext.Course.Where(c => c.Department == "Computer Science"),
                            prof => new { prof.CourseId, prof.Department },
                            course => new { Id = course.Id, Department = "Computer Science" },
                            (prof, course) => new { Professor = prof, Course = course })
                        .Select(x => new
                        {
                            ProfessorName = x.Professor.Name,
                            CourseName = x.Course.CourseName
                        });
            

SQL Query:

        
            SELECT p.Name AS ProfessorName, c.CourseName
            FROM Professor p
            INNER JOIN Course c ON p.CourseId = c.Id AND p.Department = 'Computer Science';
        
    

4

Entity Framework Inner Join, Group By, Order By

Let's say we want to perform an inner join between these tables, then group the results by the `Department` property of the `Professor` entity, and finally, order the groups by the total number of professors in each department in descending order. 

        
            var result = from prof in dbContext.Professor
                         join course in dbContext.Course on prof.CourseId equals course.Id
                         group prof by prof.Department into g
                         orderby g.Count() descending
                         select new
                         {
                             Department = g.Key,
                             TotalProfessors = g.Count()
                         };
        
    

Lambda Query:

        
            var result = dbContext.Professor
                        .Join(dbContext.Course,
                            prof => prof.CourseId,
                            course => course.Id,
                            (prof, course) => new { Professor = prof, Course = course })
                        .GroupBy(x => x.Professor.Department)
                        .OrderByDescending(g => g.Count())
                        .Select(g => new
                        {
                            Department = g.Key,
                            TotalProfessors = g.Count()
                        });
        
    

SQL Query :

        
            SELECT p.Department, COUNT(*) AS TotalProfessors
            FROM Professor p
            INNER JOIN Course c ON p.CourseId = c.Id
            GROUP BY p.Department
            ORDER BY TotalProfessors DESC;
        
    

5

Understanding entity framework join and group by example

Now let's understand this example with Orders and Customer table, if you need to perform operations like Inner Join, Group By, and Order By then read this section carefully. 

LINQ Query:

        
            var result = from order in dbContext.Orders
                         join customer in dbContext.Customers on order.CustomerId equals customer.Id
                         group order by customer.Name into g
                         orderby g.Sum(x => x.Amount) descending
                         select new
                         {
                             CustomerName = g.Key,
                             TotalOrderAmount = g.Sum(x => x.Amount)
                         };
        
    

Lambda Expression:

        
            var result = dbContext.Orders
                        .Join(dbContext.Customers,
                            order => order.CustomerId,
                            customer => customer.Id,
                            (order, customer) => new { Order = order, Customer = customer })
                        .GroupBy(x => x.Customer.Name)
                        .OrderByDescending(g => g.Sum(x => x.Order.Amount))
                        .Select(g => new
                        {
                            CustomerName = g.Key,
                            TotalOrderAmount = g.Sum(x => x.Order.Amount)
                        });
        
    

 SQL Query:

        
            SELECT c.Name AS CustomerName, SUM(o.Amount) AS TotalOrderAmount
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.Id
            GROUP BY c.Name
            ORDER BY TotalOrderAmount DESC;