语言集成查询LINQ

定义：语言集成查询 (LINQ) 是一系列直接将查询功能集成到 C# 语言的技术统称（用C#语言就能查询（数据库）等数据源的查询）

IEnumerable和List的区别：IEnumerable是一个接口，只能用来遍历里面的数据
ListList是一个类，它实现了IEnumerable接口，提供了丰富的成员方法，如Add、Remove、Clear、Sort等

LINQ表达式查询语法：以 from 子句开头，且必须以 select 或 group 子句结尾

List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };//查询变量
var evenNumbersSorted = from number in numberswhere number % 2 == 0orderby numberselect number;//select选择要返回的结果

方法语法：一般联合lamdba表达式一起使用：

List<int> numbers = new List<int> { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };var evenNumbersSorted = numbers.Where(number => number % 2 == 0).OrderBy(number => number).ToList();

查询语法和方法语法可以联合使用
.Max()、Count()为方法语法

//不是查询变量的变量（因为储存了值）
int maxValue = (from number in numbersselect number).Max();
int countAboveHalfMax = (from number in numberswhere number > maxValue / 2select number).Count();

查询时不会立即执行，必须使用 foreach 来返回结果，但是也可以通过方法，强制执行

List<int> numQuery2 = (from num in numberswhere (num % 2) == 0select num).ToList();

查询结束语句：group/select

group 子句

group 子句返回一个 IGrouping<TKey,TElement> 对象序列，，可以按照每个字符串中的第一个字母对字符串序列进行分组。在这种情况下，第一个字母就是键，类型为 char，要对每个组执行附加查询操作，可使用上下文关键字 into 指定一个临时标识符。并最终使用一个select 语句或另一个 group 子句结束该查询

var studentQuery2 =from student in studentsgroup student by student.Last[0] into gorderby g.Key//对分组进行排序select g;

对分组后的结果查询：嵌套循环遍历：第一层遍历出每个键值的组，第二层遍历每个组中的元素

foreach (IGrouping<char, Student> studentGroup in studentQuery2)
{Console.WriteLine(studentGroup.Key);foreach (var student in studentGroup){Console.WriteLine("   {0}, {1}", student.Last, student.First);}}

分组键可以是任意类型：
比如按照数值范围分组：

var studentQuery =from student in studentslet avg = (int)student.Scores.Average()group student by (avg / 10) into g  //按照每个学生的分/10取整分组，这样在0-9,10-19....按照每十个区间的方式分组orderby g.Keyselect g;// Execute the query.foreach (var studentGroup in studentQuery){int temp = studentGroup.Key * 10;Console.WriteLine("Students with an average between {0} and {1}", temp, temp + 10);foreach (var student in studentGroup){Console.WriteLine("   {0}, {1}:{2}", student.Last, student.First, student.Scores.Average());}}

复合建分组：

group person by new {name = person.surname, city = person.city};

直接返回分组后的结果

List<int> numbers = [35, 44, 200, 84, 3987, 4, 199, 329, 446, 208];IEnumerable<IGrouping<int, int>> query = from number in numbersgroup number by number % 2;
//方法语法
IEnumerable<IGrouping<int, int>> query = numbers.GroupBy(number => number % 2);

select 子句

可以选择返回查询出序列中的元素类型，
默认和数据源序列中元素类型一样，也可显示转换

//默认
IEnumerable<Country> sortedQuery =from country in countriesorderby country.Areaselect country;

//转换（映射）
var queryNameAndPop =from country in countriesselect new{Name = country.Name,Pop = country.Population};

使用“into”延续

如果select或group后还要继续进行查询，那么就可以通过into重命名查询结果，然后继续筛选查询

var percentileQuery =from country in countrieslet percentile = (int)country.Population / 10_000_000group country by percentile into countryGroupwhere countryGroup.Key >= 20//筛选出键值>=20的组orderby countryGroup.Key select countryGroup;

联表查询join

join 子句可用于将来自不同源序列并且在对象模型中没有直接关系的元素相关联。唯一的要求是每个源中的元素需要共享某个(主键、外键)可以进行比较以判断是否相等的值。join 子句的输出形式取决于执行的联接的具体类型

内部链接

//查询语句
var innerJoinQuery =from category in categoriesjoin prod in products on category.ID equals prod.CategoryID //两表中只有相等的才会连接上select new { ProductName = prod.Name, Category = category.Name }; 
//方法语句
var innerJoinQuery=category.join(products,category=>category.ID,prod=>prod.CategoryID, (category,prod)=>new{ProductName=prod.name,Category = category.Name });

分组联接

含有 into 表达式的 join 子句
将每组链接的结果，单独作为一个对象，继续之后的操作

var innerGroupJoinQuery2 =from category in categoriesjoin prod in products on category.ID equals prod.CategoryID into prodGroupfrom prod2 in prodGroupwhere prod2.UnitPrice > 2.50Mselect prod2;//方法语句
var innerGroupJoinQuery2=categories.groupJoin(products,
category=>category.ID,
prod=>prod.CategoryID, 
(category,prod)=>prodGroup,
where(prod2=>prod2.UnitPrice > 2.50M),
select(prod2=>prod2)
).SelectMany(group => group);//展开分组

复合键

IEnumerable<string> query =from teacher in teachersjoin student in students on new{FirstName = teacher.First,LastName = teacher.Last} equals new{student.FirstName,student.LastName}select teacher.First + " " + teacher.Last;
//方法语法
IEnumerable<string> query =teachers.join(students,teacher=>new{FirstName = teacher.First,LastName = teacher.Last},student=>new{student.FirstName,student.LastName},(teacher,student)=> $"{teacher.First} {teacher.Last}");

多个join

var query = from student in studentsjoin department in departments on student.DepartmentID equals department.IDjoin teacher in teachers on department.TeacherID equals teacher.IDselect new {StudentName = $"{student.FirstName} {student.LastName}",DepartmentName = department.Name,TeacherName = $"{teacher.First} {teacher.Last}"};
//方法语句
var query = students.Join(departments, student => student.DepartmentID, department => department.ID,(student, department) => new { student, department }).Join(teachers, commonDepartment => commonDepartment.department.TeacherID, teacher => teacher.ID,(commonDepartment, teacher) => new{StudentName = $"{commonDepartment.student.FirstName} {commonDepartment.student.LastName}",DepartmentName = commonDepartment.department.Name,TeacherName = $"{teacher.First} {teacher.Last}"});

执行左外部联接

使用 LINQ 通过对分组 join 的结果调用 DefaultIfEmpty 方法来执行左外部 join。
第一步内联：
Department 对象列表基于与学生的 DepartmentID 匹配的 Department 对象的 ID，内部联接到 Student 对象列表。
第二部：
然后，您使用另一个from子句来展开这个中间序列，同时利用DefaultIfEmpty()方法来确保即使没有找到匹配的部门，学生记录仍然会被包含在结果中。

var query =from student in studentsjoin department in departments on student.DepartmentID equals department.ID into gjfrom subgroup in gj.DefaultIfEmpty()select new{student.FirstName,student.LastName,Department = subgroup?.Name ?? string.Empty};

//方法语句
var query = students.GroupJoin(departments,student => student.DepartmentID,department => department.ID,(student, departmentList) => new { student, subgroup = departmentList })//subgroup包含了每次匹配成功后的右表记录.SelectMany(joinedSet => joinedSet.subgroup.DefaultIfEmpty(),//即使匹配到的右表是null，任然保存左表的那行记录(student, department) => new{student.student.FirstName,student.student.LastName,Department = department.Name});

let 子句

将表达式的结果保存到新的范围变量中

string[] names = ["Svetlana Omelchenko", "Claire O'Donnell", "Sven Mortensen", "Cesar Garcia"];
IEnumerable<string> queryFirstNames =from name in nameslet firstName = name.Split(' ')[0]select firstName;foreach (var s in queryFirstNames)
{Console.Write(s + " ");
}

对分组操作执行子查询

ar queryGroupMax =from student in studentsgroup student by student.Year into studentGroupselect new{Level = studentGroup.Key,HighestScore = (//找出力每个组中，每个学生的平均成绩的最高分from student2 in studentGroup  select student2.Scores.Average()).Max()};
//方法语句```csharp
var queryGroupMax =students.GroupBy(student => student.Year).Select(studentGroup => new{Level = studentGroup.Key,HighestScore = studentGroup.Max(student2 => student2.Scores.Average())});

在运行时动态指定谓词筛选器Contains方法

当筛选条件不单一，则使用Contains方法

int[] ids = [111, 114, 112];var queryNames =from student in studentswhere ids.Contains(student.ID)//筛选出student.ID在ids集合中的学生select new{student.LastName,student.ID};

在查询表达式中处理 null 值

编码防御型：

var query1 =from c in categorieswhere c != nulljoin p in products on c.ID equals p?.CategoryIDselect new{Category = c.Name,Name = p.Name};