在C#中处理数据时，语言集成查询（LINQ）是一个强大的工具，它允许我们以类似于数据库查询的方式查询集合。LINQ进行多表查询，包括内连接、组连接、左连接和交叉连接。
基础数据模型
首先定义几个基础的数据模型类：
//学生
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）
内连接返回两个表中匹配的记录。如果一条记录在一边没有对应的匹配项，则该记录不会出现在结果中。
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）
组连接是内连接的一种变体，它可以返回匹配的一组记录。
// 使用 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）
左连接返回左边表的所有记录，即使在右边表中没有匹配的记录也会返回，对于没有匹配的记录，右边表的相关字段将返回空值。
// 添加一个没有选课的学生
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）
交叉连接返回左边表和右边表的笛卡尔积，即每个左边表的记录与右边表的每条记录组合。
// 使用 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 进行左连接
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}");
}
}首先使用GroupJoin将学生和选课数据分组连接起来，然后通过SelectMany和DefaultIfEmpty来处理没有选课记录的情况。这样，即使某些学生没有选课记录，他们的信息也会出现在结果集中，ClassroomId将被设置为null。
Lambda 和 GroupBy 表达式
C# 的 Lambda 表达式提供了一种简洁的方式来编写匿名方法，极大地增强了代码的可读性和灵活性。结合 LINQ (Language Integrated Query) 的 GroupBy 方法，我们可以轻松地对集合进行分组处理，这在处理复杂数据结构时尤其有用。
按属性分组
假设我们有一个学生列表，每个学生有班级和成绩属性。我们想要按照班级将学生分组，并计算每个班级的平均成绩。
public class Student {
public string Class { get; set; }
public int Score { get; set; }
}
var students = new List<Student> {
new Student { Class = "1A", Score = 85 },
new Student { Class = "1B", Score = 90 },
new Student { Class = "1A", Score = 78 },
new Student { Class = "1B", Score = 82 },
};
var groupedByClass = students.GroupBy(s => s.Class).Select(g => new { Class = g.Key, AverageScore = g.Average(s => s.Score) });
foreach (var group in groupedByClass){
Console.WriteLine($"Class: {group.Class}, Average Score: {group.AverageScore}");
}按条件分组
假设我们需要根据学生的成绩将学生分为“及格”和“不及格”两组。
var passFailGroups = students.GroupBy(s => s.Score >= 80 ? "Pass" : "Fail").Select(g => new { Category = g.Key, Students = g.ToList() });
foreach (var group in passFailGroups){
Console.WriteLine($"Category: {group.Category}");
foreach (var student in group.Students) {
Console.WriteLine($" - Class: {student.Class}, Score: {student.Score}");
}
}按多个属性分组
我们可以使用元组或匿名类型将元素按多个键分组。假设我们要按班级和是否及格两个条件分组。
var multiKeyGroup = students.GroupBy(s => new { s.Class, Category = s.Score >= 80 ? "Pass" : "Fail" }).Select(g => new { g.Key.Class, g.Key.Category, Students = g.ToList() });
foreach (var group in multiKeyGroup){
Console.WriteLine($"Class: {group.Class}, Category: {group.Category}");
foreach (var student in group.Students) {
Console.WriteLine($" - Score: {student.Score}");
}
}