CODEWITHSUNDEEP
c#linqjoin
Tabor
Tabor

Reputation: 53

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

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.

Upvotes: 2

Views: 1408

Answers (2)

Peter Csala
Peter Csala

Reputation: 22714

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

Upvotes: 4

Harald Coppoolse
Harald Coppoolse

Reputation: 30464

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.

Upvotes: 2

Related Questions