c# - 如何使用 Linq 连接两个表并计算一列匹配记录的行数

Question

您好，我有两个表:Teachers 和 Departments。

我的 Teacher 表包含这些列(Teacherid、Name、Department)。

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

我的 Department 表有 (id,department_name) 列。

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

现在我想显示部门列表，我还想显示拥有该部门的教师总数# 如何使用 linq 查询实现这一点。

Answer 1

假设您的模型如下所示:

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; }
}

让我们创建一些示例数据:

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 }
};

让我们加入他们并计算请求的输出:

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()
    };

最后打印出报告:

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

A: 2
B: 0
C: 1