C#知识整理-LINQ

语言集成查询 (LINQ) 是一系列直接将查询功能集成到 C# 语言的技术统称。 此外，需要针对每种类型的数据源了解不同的查询语言：SQL 数据库、XML 文档、各种 Web 服务等。 借助 LINQ，查询成为了最高级的语言构造，就像类、方法和事件一样。

使用查询语法，可以用最少的代码对数据源执行筛选、排序和分组操作。 可使用相同的基本查询表达式模式来查询和转换 SQL 数据库、ADO .NET 数据集、XML 文档和流以及 .NET 集合中的数据。

LINQ 查询操作都由以下三个不同的操作组成：

1. 获取数据源。
2. 创建查询。
3. 执行查询。

下面用一个演示表示查询操作的三个部分

// 1. Data source.
List<int> numbers = new List<int>() { 0, 1, 2, 3, 4, 5, 6 };
//int[] numbers = [0, 1, 2, 3, 4, 5, 6];
// 2. Query creation.
// numQuery is an IEnumerable<int>
var numQuery = from num in numbers 
               where (num % 2) == 0 
               select num;
// 3. Query execution.
foreach (int num in numQuery)
{
    Console.Write("{0,1} ", num);
}
// output 0 2 4 6

为什么要使用LINQ，用上述的案例，如果不用LINQ如何来写

List<int> numbers = new List<int>() { 0, 1, 2, 3, 4, 5, 6 };
var numResults = new List<int>();
foreach (var num in numbers) {
    if (num % 2 == 0) {
        numResults.Add(num);
    }
}
foreach (int num in numResults)
{
    Console.Write("{0,1} ", num);
}
// output 0 2 4 6

如果不使用LINQ，筛选数据只能通过定义一个空的结果集，然后通过for循环和if语句把符合条件的结果插入到结果集里，

然后打印出结果

除此之外，我们还可以使用Enumerable自带的where子句达到相同的效果，可以使用lambda表达式

List<int> numbers = new List<int>() { 0, 1, 2, 3, 4, 5, 6 };
var numQuery1 = numbers.Where(p => p % 2 == 0);
foreach (int num in numQuery1)
{
    Console.Write("{0,1} ", num);
}
// output 0 2 4 6

下面用一个例子展示一下常用的查询

首先定义一些数据集，这些数据可以是从数据库或调用API得到的，我们就定义一下静态数据

    internal class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public int ClassId { get; set; }

        public override string ToString()
        {
            return $"Id:{Id},Name:{Name},Age:{Age},ClassId:{ClassId}";
        }
    }

    internal class Lesson
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }


    internal class StudentLesson
    {
        public int StudentId { get; set; }
        public int LessonId { get; set; }
        public decimal Score { get; set; }
    }

    internal static class StudentData {
        public static IEnumerable<Student> students;
        public static IEnumerable<Lesson> lessons;
        public static IEnumerable<StudentLesson> studentLessons;

        public static void init() {
            List<Lesson> lstLessons = new List<Lesson>
            {
                new Lesson() {
                    Id=1,
                    Name="语文"
                },
                new Lesson() {
                    Id=2,
                    Name="数学"
                },
                new Lesson() {
                    Id=3,
                    Name="英语"
                },
                new Lesson() {
                    Id=4,
                    Name="物理"
                },
            };
            List<Student> lstStudents = new List<Student>() {
                new Student(){
                    Id = 1,
                    Age = 12,
                    ClassId = 1,
                    Name = "张一"
                },
                new Student(){
                    Id = 2,
                    Age = 12,
                    ClassId = 1,
                    Name = "张二"
                },
                new Student(){
                    Id = 3,
                    Age = 12,
                    ClassId = 1,
                    Name = "张三"
                },
                new Student(){
                    Id = 4,
                    Age = 12,
                    ClassId = 1,
                    Name = "张四"
                },
                new Student(){
                    Id = 5,
                    Age = 14,
                    ClassId = 2,
                    Name = "王一"
                },
                new Student(){
                    Id = 6,
                    Age = 14,
                    ClassId = 2,
                    Name = "王二"
                },
                new Student(){
                    Id = 7,
                    Age = 14,
                    ClassId = 2,
                    Name = "王三"
                },
                new Student(){
                    Id = 8,
                    Age = 14,
                    ClassId = 2,
                    Name = "王四"
                },
                new Student(){
                    Id = 9,
                    Age = 13,
                    ClassId = 3,
                    Name = "李一"
                },
                new Student(){
                    Id = 10,
                    Age = 13,
                    ClassId = 3,
                    Name = "李二"
                },
                new Student(){
                    Id = 11,
                    Age = 13,
                    ClassId = 3,
                    Name = "李三"
                },
                new Student(){
                    Id = 12,
                    Age = 13,
                    ClassId = 3,
                    Name = "李四"
                },
            };
            List<StudentLesson> lstStudentLessons = new List<StudentLesson>();
            foreach (var lesson in lstLessons) {
                foreach (var student in lstStudents) {
                    StudentLesson studentLesson = new StudentLesson()
                    {
                        LessonId = lesson.Id,
                        StudentId = student.Id,
                    };
                    var score = 0.0m;
                    switch (student.Id)
                    {
                        case 1:
                            score = 80.3m; break;
                        case 2:
                            score = 90.5m; break;
                        case 3:
                            score = 70.2m; break;
                        case 4:
                            score = 83.7m; break;
                    }
                    studentLesson.Score = score;

                    lstStudentLessons.Add(studentLesson);
                }
            }

            students = lstStudents;
            lessons = lstLessons;
            studentLessons = lstStudentLessons;
        }
    }

现在我们就根据定义好的数据进行查询

StudentData.init();
var students = StudentData.students;
var lessons = StudentData.lessons;
var studentLessons = StudentData.studentLessons;

//查询
var zhangStudents = students.Where(p => p.Name.Contains("张"));
foreach (var student in zhangStudents)
{
    Console.WriteLine(student.Name);
}
/*output:
    张一
    张二
    张三
    张四
 */
var zhangStudents1 = from student in students
                     where student.Name.Contains("张")
                     select student;
foreach (var student in zhangStudents1)
{
    Console.WriteLine(student.Name);
}
/*output:
    张一
    张二
    张三
    张四
 */
 
// group by
var groupResult = students.GroupBy(p => p.ClassId).ToList();
foreach (var group in groupResult)
{
    Console.WriteLine($"ClassId: {group.Key}");
    foreach (var student in group)
    {
        Console.WriteLine(student);
    }
}
/*
    ClassId: 1
    Id:1,Name:张一,Age:12,ClassId:1
    Id:2,Name:张二,Age:12,ClassId:1
    Id:3,Name:张三,Age:12,ClassId:1
    Id:4,Name:张四,Age:12,ClassId:1
    ClassId: 2
    Id:5,Name:王一,Age:14,ClassId:2
    Id:6,Name:王二,Age:14,ClassId:2
    Id:7,Name:王三,Age:14,ClassId:2
    Id:8,Name:王四,Age:14,ClassId:2
    ClassId: 3
    Id:9,Name:李一,Age:13,ClassId:3
    Id:10,Name:李二,Age:13,ClassId:3
    Id:11,Name:李三,Age:13,ClassId:3
    Id:12,Name:李四,Age:13,ClassId:3
 */
 
 // group by
var groupResult = students.GroupBy(p => p.ClassId).ToList();
                  
foreach (var group in groupResult)
{
    Console.WriteLine($"ClassId: {group.Key}");
    foreach (var student in group)
    {
        Console.WriteLine(student);
    }
}
/*
    ClassId: 1
    Id:1,Name:张一,Age:12,ClassId:1
    Id:2,Name:张二,Age:12,ClassId:1
    Id:3,Name:张三,Age:12,ClassId:1
    Id:4,Name:张四,Age:12,ClassId:1
    ClassId: 2
    Id:5,Name:王一,Age:14,ClassId:2
    Id:6,Name:王二,Age:14,ClassId:2
    Id:7,Name:王三,Age:14,ClassId:2
    Id:8,Name:王四,Age:14,ClassId:2
    ClassId: 3
    Id:9,Name:李一,Age:13,ClassId:3
    Id:10,Name:李二,Age:13,ClassId:3
    Id:11,Name:李三,Age:13,ClassId:3
    Id:12,Name:李四,Age:13,ClassId:3
 */

var groupResult1 = from student in students
                    group student by student.ClassId into groupString
                    orderby groupString.Key descending
                    select groupString;
foreach (var group in groupResult1)
{
    Console.WriteLine($"ClassId: {group.Key}");
    foreach (var student in group)
    {
        Console.WriteLine(student);
    }
}
/* output
    ClassId: 3
    Id: 9,Name: 李一,Age: 13,ClassId: 3
    Id: 10,Name: 李二,Age: 13,ClassId: 3
    Id: 11,Name: 李三,Age: 13,ClassId: 3
    Id: 12,Name: 李四,Age: 13,ClassId: 3
    ClassId: 2
    Id: 5,Name: 王一,Age: 14,ClassId: 2
    Id: 6,Name: 王二,Age: 14,ClassId: 2
    Id: 7,Name: 王三,Age: 14,ClassId: 2
    Id: 8,Name: 王四,Age: 14,ClassId: 2
    ClassId: 1
    Id: 1,Name: 张一,Age: 12,ClassId: 1
    Id: 2,Name: 张二,Age: 12,ClassId: 1
    Id: 3,Name: 张三,Age: 12,ClassId: 1
    Id: 4,Name: 张四,Age: 12,ClassId: 1
 */
 
 
// join
var joinresult = from student in students
             join studentLesson in studentLessons
             on student.Id equals studentLesson.StudentId
             join lesson in lessons
             on studentLesson.LessonId equals lesson.Id
             where lesson.Name.Equals("语文")
             select new
             {
                 student.Id,
                 student.Name,
                 student.ClassId,
                 student.Age,
                 LessonName = lesson.Name,
                 Score = studentLesson.Score
             };
foreach (var result in joinresult) {
    Console.WriteLine($"id:{result.Id},name:{result.Name},class id:{result.ClassId},age:{result.Age},lesson name:{result.LessonName},score:{result.Score}");
}
/*
* output 
id:1,name:张一,class id:1,age:12,lesson name:语文,score:80.3
id:2,name:张二,class id:1,age:12,lesson name:语文,score:90.5
id:3,name:张三,class id:1,age:12,lesson name:语文,score:70.2
id:4,name:张四,class id:1,age:12,lesson name:语文,score:83.7
id:5,name:王一,class id:2,age:14,lesson name:语文,score:0.0
id:6,name:王二,class id:2,age:14,lesson name:语文,score:0.0
id:7,name:王三,class id:2,age:14,lesson name:语文,score:0.0
id:8,name:王四,class id:2,age:14,lesson name:语文,score:0.0
id:9,name:李一,class id:3,age:13,lesson name:语文,score:0.0
id:10,name:李二,class id:3,age:13,lesson name:语文,score:0.0
id:11,name:李三,class id:3,age:13,lesson name:语文,score:0.0
id:12,name:李四,class id:3,age:13,lesson name:语文,score:0.0
*/