How to join two tables using Linq and count the rows of one column matched records

Question

Hi I have two tables: Teachers and Departments.

My Teacher table is having these columns(Teacherid,Name,Department).

| TeacherId | Name | Department |
|-----------|------|------------|
|           |      |            |

My Department table is having (id,department_name) columns.

| Id | Department_Name |
|----|-----------------|
|    |                 |

Now I want to show the listing of departments and I also want to show the total No# of teachers having this department how to achieve that using linq query.

Answer 1

Let's suppose your models look like this:

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int DepartmentId { get; set; }
}

public class Department
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Let's create some sample data:

var departments = new List<Department>
{
    new Department { Id = 1, Name = "A" },
    new Department { Id = 2, Name = "B" },
    new Department { Id = 3, Name = "C" }
};

var teachers = new List<Teacher>
{
    new Teacher { Id = 1, Name = "AA", DepartmentId = 1 },
    new Teacher { Id = 2, Name = "AB", DepartmentId = 1 },
    new Teacher { Id = 3, Name = "CA", DepartmentId = 3 }
};

Let's join them and calculate the requested output:

var report =from department in departments
    join teacher in teachers on department.Id equals teacher.DepartmentId into teachersInDepartment
    select new
    {
        DepartmentName = department.Name,
        NumberOfTeachers = teachersInDepartment.Count()
    };

And finally printing out the report:

foreach (var record in report)
{
    Console.WriteLine($"{record.DepartmentName}: {record.NumberOfTeachers}");
}

A: 2
B: 0
C: 1