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

Question

Hi我有两个表：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

Answer 2

因此，Teachers和Departments之间存在一个简单的一对多关系：每个Department都有零个或多个Teachers，每个Teacher正好在一个Department上工作，即外键DepartmentId所指的部门。

每当你有一对多的关系，并且你想要所有的"客户与他们的订单"作者与他们的书"；，系及其教师"；，考虑使用Queryable.GroupJoin.的重载之一

如果你想要的不仅仅是"；系及其教师"；，使用具有参数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(),
});

在一对多关系中，如果您想要"具有其零个或多个子项的项"；使用GroupJoin，如果您想要"；具有其唯一ParentItem的子项"；使用Join。