If you're just starting to learn Entity Framework Core and you're searching for examples of using Entity Framework with LINQ, inner joins, group by clauses, and order by operations, you've come to the right place, if you are trying to move from writing SQL queries to LINQ queries in Entity Framework-based projects, you may encounter challenges with joins, group by clauses, and aggregate functions such as count, sum, min, and max. In this post, we'll explore an example of Entity Framework join and group by operations in C#.
I have created SQL scripts for the `Professor` table with properties such as Id, Name, UniversityName, Course, Department, Salary, and CreatedAt, along with the `Course` table with fields Id and CourseName. Additionally, I've provided insert scripts for your testing purposes.
Now, let's look into understanding Entity Framework join and group by examples, including operations such as order by, sum, count, min, and max.
-- 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());
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. 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.
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;
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';
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;
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;