C# Linq多表查询

在C#中处理数据时，语言集成查询（LINQ）是一个强大的工具，它允许我们以类似于数据库查询的方式查询集合。本文将介绍如何使用LINQ进行多表查询，包括内连接、组连接、左连接和交叉连接，并提供详细的代码示例和解释。

基础数据模型

为了演示多表查询，我们首先定义几个基础的数据模型类：

C#
//学生
public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
}

//班级
public class Classroom
{
    public int ClassroomId { get; set; }
    public string Name { get; set; }
}

//选课
public class Enrollment
{
    public int StudentId { get; set; }
    public int ClassroomId { get; set; }
}

内连接（Inner Join）

内连接返回两个表中匹配的记录。如果一条记录在一边没有对应的匹配项，则该记录不会出现在结果中。

C#
static void Main(string[] args)
{
    // 创建学生和班级示例数据
    var students = new List<Student>
    {
        new Student { StudentId = 1, Name = "Alice" },
        new Student { StudentId = 2, Name = "Bob" }
    };

    var classrooms = new List<Classroom>
    {
        new Classroom { ClassroomId = 101, Name = "Math" },
        new Classroom { ClassroomId = 102, Name = "Science" }
    };

    var enrollments = new List<Enrollment>
    {
        new Enrollment { StudentId = 1, ClassroomId = 101 },
        new Enrollment { StudentId = 2, ClassroomId = 102 }
    };


    // 使用 LINQ 查询进行内连接
    var innerJoinQuery = from student in students
                         join enrollment in enrollments on student.StudentId equals enrollment.StudentId
                         join classroom in classrooms on enrollment.ClassroomId equals classroom.ClassroomId
                         select new
                         {
                             StudentName = student.Name,
                             ClassroomName = classroom.Name
                         };

    // 输出查询结果
    foreach (var item in innerJoinQuery)
    {
        Console.WriteLine($"Student: {item.StudentName}, Classroom: {item.ClassroomName}");
    }
}

组连接（Group Join）

组连接是内连接的一种变体，它可以返回匹配的一组记录。

C#
// 使用 LINQ 查询进行组连接
var groupJoinQuery = from classroom in classrooms
                     join enrollment in enrollments on classroom.ClassroomId 
                     equals enrollment.ClassroomId into studentGroup
                     select new
                     {
                         ClassroomName = classroom.Name,
                         Students = from student in students
                                    join enrollment in studentGroup on student.StudentId equals enrollment.StudentId
                                    select student.Name
                     };

// 输出查询结果
foreach (var classroom in groupJoinQuery)
{
    Console.WriteLine($"Classroom: {classroom.ClassroomName}");
    foreach (var studentName in classroom.Students)
    {
        Console.WriteLine($"  Student: {studentName}");
    }
}

左连接（Left Join）

左连接返回左边表的所有记录，即使在右边表中没有匹配的记录也会返回，对于没有匹配的记录，右边表的相关字段将返回空值。

C#
// 添加一个没有选课的学生
students.Add(new Student { StudentId = 3, Name = "Charlie" });

// 使用 LINQ 查询进行左连接
var leftJoinQuery = from student in students
                    join enrollment in enrollments on student.StudentId equals enrollment.StudentId into enrollmentGroup
                    from enrollment in enrollmentGroup.DefaultIfEmpty()
                    join classroom in classrooms on enrollment?.ClassroomId equals classroom.ClassroomId into classroomGroup
                    from classroom in classroomGroup.DefaultIfEmpty()
                    select new
                    {
                        StudentName = student.Name,
                        ClassroomName = classroom?.Name ?? "No Classroom"
                    };

// 输出查询结果
foreach (var item in leftJoinQuery)
{
    Console.WriteLine($"Student: {item.StudentName}, Classroom: {item.ClassroomName}");
}

交叉连接（Cross Join）

交叉连接返回左边表和右边表的笛卡尔积，即每个左边表的记录与右边表的每条记录组合。

C#
// 使用 LINQ 查询进行交叉连接
var crossJoinQuery = from student in students
                     from classroom in classrooms
                     select new
                     {
                         StudentName = student.Name,
                         ClassroomName = classroom.Name
                     };

// 输出查询结果
foreach (var item in crossJoinQuery)
{
    Console.WriteLine($"Student: {item.StudentName}, Classroom: {item.ClassroomName}");
}

使用GroupJoin 和 SelectMany 进行左连接

C#
static void Main(string[] args)
{
    // 假设我们有以下学生和选课数据
    var students = new List<Student>
    {
        new Student { StudentId = 1, Name = "Alice" },
        new Student { StudentId = 2, Name = "Bob" },
        new Student { StudentId = 3, Name = "Charlie" } // Charlie 没有选课
    };

    var enrollments = new List<Enrollment>
    {
        new Enrollment { StudentId = 1, ClassroomId = 101 },
        new Enrollment { StudentId = 2, ClassroomId = 102 }
    };

    // 使用 GroupJoin 创建一个临时组合，其中每个学生都与一个可能为空的选课集合关联
    var tempGroupJoin = students.GroupJoin(
        enrollments,
        student => student.StudentId,
        enrollment => enrollment.StudentId,
        (student, enrollmentGroup) => new { student, enrollmentGroup }
    );

    // 使用 SelectMany 展平结果集，确保即使学生没有选课信息，也能在结果中显示
    var leftJoinQuery = tempGroupJoin.SelectMany(
        temp => temp.enrollmentGroup.DefaultIfEmpty(), // DefaultIfEmpty 确保没有选课信息的学生也被包含
        (temp, enrollment) => new
        {
            StudentName = temp.student.Name,
            // 如果学生没有选课信息，则 ClassroomId 设置为 null
            ClassroomId = enrollment != null ? enrollment.ClassroomId : (int?)null
        }
    );

    // 输出查询结果
    foreach (var item in leftJoinQuery)
    {
        Console.WriteLine($"Student: {item.StudentName}, ClassroomId: {item.ClassroomId}");
    }

}