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.

Peter Csala · Accepted Answer · 2020-10-28T09:13:09.340

4

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

edited Oct 28 '20 at 09:13

answered Oct 28 '20 at 09:06

Peter Csala

17,736
16
35
75

i am using the above approach and want to see what is in report variable but it says "Error = The function evaluation requires all threads to run." – Tabor Oct 28 '20 at 09:57
@Tabor I suppose you are experiencing it during debugging. You can fix it [like this](https://stackoverflow.com/questions/29985150/visual-studio-during-debugging-the-function-evaluation-requires-all-threads-to) or call the `ToList()` explicitly at the end of the query, like this: `var report = (from .... Count() }).ToList();` – Peter Csala Oct 28 '20 at 10:07
1

Thanks for assisting me, your Linq query works fine but tell me how can i use these values to my view as in my view i am calling model values by iterating to model object . Status=department.Status, CreationDate=department.Date_Created, DepartmentName = department.Department_Title, NumberOfTeachers = teachersInDepartment.Count() How to use these four variable in my view razor view. – Tabor Oct 28 '20 at 10:21
@Tabor Here I've created an anonymous type (`select new { ... }`) to include only the name of the department and the number of related teachers (as the original question stated). If you need to return something specific then you need to use your model class in the select clause `select new YourReportModel { Status .... }` – Peter Csala Oct 28 '20 at 10:37

score 2 · Answer 2 · answered Oct 28 '20 at 08:54

So there is a straightforward one-to-many relation between Teachers and Departments: every Department has zero or more Teachers, every Teacher works exactly at one Department namely the Department that the foreign key DepartmentId refers to.

Whenever you have a one-to-many relationship, and you want all "Customers with their Orders", "Authors with their Books", Departments with their Teachers", consider to use one of the overloads of Queryable.GroupJoin.

If you want more than just "Departments with their Teachers", use the overload that has a parameter resultSelector

IQueryable<Teacher> teachers = ...
IQueryable<Department> departments = ...

// GroupJoin: get the Departments with their Teachers
var result = departments.GroupJoin(teachers,

    department => department.Id,     // from every department take the primary key
    teacher => teacher.DepartmentId, // from every teacher take the foreign key

    // parameter resultSelector: from every Department, with its zero or more Teachers,
    // make one new:
    (department, teachersOfThisDepartment) => new
    {
        // Select only the department properties that you plan to use:
        Id = department.Id,
        Name = department.Name,
        ...

        // Get the total number of teachers that work on this department:
        TeacherCount = teachersOfThisDepartment.Count(),

        // If you want more information about the Teachers, use Select:
        Teachers = teachersOfThisDepartment.Select(teacher => new
        {
            // again: select only the teacher properties that you plan to use:
            Id = teacher.Id,
            Name = teacher.Name,
            ...

            // not needed, you already know the value
            // DepartmentId = teacher.DepartmentId,
        })
        .ToList(),
    });

In a one-to-many relation, if you want "items with their zero or more subitems" use GroupJoin, if you want "SubItem with its one-and-only ParentItem" use Join.

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

2 Answers2