If you are working on a .NET application project with Entity Framework and encountering issues with grouping by multiple columns, you've come to the right place. In this post, we will discuss Entity Framework's ability to group by multiple columns using various operators like Min, Max, Count, Sum, as well as utilizing OrderBy and Where clauses.
SQL Script for Professor Table Creation
CREATE TABLE Professor (
Id INT PRIMARY KEY,
Name VARCHAR(255),
Course VARCHAR(255),
Department VARCHAR(255),
Salary DECIMAL(10, 2),
CreatedAt TIMESTAMP
);
SQL Insert Script with 10 Records
INSERT INTO Professor (Id, Name, Course, Department, Salary, CreatedAt)
VALUES
(1, 'John Doe', 'Mathematics', 'Math Dept', 50000.00, NOW()),
(2, 'Jane Smith', 'Physics', 'Physics Dept', 55000.00, NOW()),
(3, 'Michael Johnson', 'Chemistry', 'Chemistry Dept', 60000.00, NOW()),
(4, 'Emily Brown', 'Biology', 'Biology Dept', 52000.00, NOW()),
(5, 'David Wilson', 'Computer Science', 'CS Dept', 58000.00, NOW()),
(6, 'Jennifer Lee', 'History', 'History Dept', 53000.00, NOW()),
(7, 'Robert Taylor', 'English', 'English Dept', 54000.00, NOW()),
(8, 'Sarah Martinez', 'Economics', 'Economics Dept', 57000.00, NOW()),
(9, 'Daniel Garcia', 'Psychology', 'Psychology Dept', 56000.00, NOW()),
(10, 'Lisa Rodriguez', 'Sociology', 'Sociology Dept', 59000.00, NOW());
Entity Framework Group By Multiple Columns
Here we're utilizing Entity Framework to group the professors by their departments and courses. We then calculate the average salary within each group using the Average() function. Finally, we store the results into a new anonymous type containing the department, course, and average salary.
SQL Query:
SELECT Department, Course, AVG(Salary) AS AvgSalary
FROM Professor
GROUP BY Department, Course;
Lambda Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
AvgSalary = g.Average(p => p.Salary)
})
.ToList();
Linq Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
AvgSalary = g.Average(p => p.Salary)
})
.ToList();
Entity Framework Group By Multiple Columns with Sum
With this SQL query, we aim to retrieve the department, course, and the total salary of professors within each department and course group.
By executing below query, we efficiently aggregate the salary data and gain valuable insights into the distribution of salaries among different departments and courses within our database.
SQL Query:
SELECT Department, Course, SUM(Salary) AS TotalSalary
FROM Professor
GROUP BY Department, Course;
Lambda Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
TotalSalary = g.Sum(p => p.Salary)
})
.ToList();
Linq Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
TotalSalary = g.Sum(p => p.Salary)
})
.ToList();
Entity Framework Group By Multiple Columns with Count
Below linq query enables us to fetch the department, course, and the total count of professors within each department and course group.
Executing linq query allows us to efficiently determine the distribution of professors across different departments and courses within our database, providing valuable insights into the staffing levels in various academic areas.
SQL Query:
SELECT Department, Course, COUNT(*) AS TotalProfessors
FROM Professor
GROUP BY Department, Course;
Lambda Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
TotalCount = g.Count()
})
.ToList();
Linq Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
TotalCount = g.Count()
})
.ToList();
Entity Framework Group By Multiple Columns with Ordering
If we need to retrieve the average salary of professors grouped by their departments and courses in our database, sorted in ascending order by department and course, we rely on SQL to achieve this objective.
This query allows us to fetch the department, course, and the average salary of professors within each department and course group, arranged in ascending order by department and course.
SQL Query:
SELECT Department, Course, AVG(Salary) AS AvgSalary
FROM Professor
GROUP BY Department, Course
ORDER BY Department, Course ASC;
Lambda Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
AvgSalary = g.Average(p => p.Salary)
})
.OrderBy(g => g.Department)
.ThenBy(g => g.Course)
.ToList();
Linq Query:
var result = context.Professors
.GroupBy(p => new { p.Department, p.Course })
.Select(g => new {
Department = g.Key.Department,
Course = g.Key.Course,
AvgSalary = g.Average(p => p.Salary)
})
.OrderBy(g => g.Department)
.ThenBy(g => g.Course)
.ToList();
