本页内容：
1.LINQ查询语言的组成部分；
2.使用LINQ方法语法；
3.排序查询结果 orderby 字句；
4.聚合运算符；
5.查询复杂对象；
6.投影：在查询中创建新对象；
7.Distinct()、Any()、All()、First()、FirtOrDefault()、Take()、Skip()运算符；
8.组合查询
9.集运算符
10.联合 Join运算符
1.LINQ查询语言的组成部分
static void Main(string[] args) { string[] names = { "Alonso", "Zheng", "Smith", "Jones", "Smythe", "Small", "Ruiz", "Hsieh", "Jorgenson", "Ilyich", "Singh", "Samba", "Fatimah" }; var queryResults=from n in names where n.StartsWith("S") select n; Console.WriteLine("Names beginning with S:"); foreach (var item in queryResults) { Console.WriteLine(item); } Console.ReadLine(); }
该例子使用了LINQ查询语法声明了一个LINQ查询
var queryResults=from n in names where n.StartsWith("S") select n;
该语句包括以下几个组成部分：
1.1 声明查询结果变量 var queryResults=；
1.2指定数据源 from n in names ；
1.3指定条件 where n.StartsWith("S")；
1.4指定查询元素 select n;
注：LINQ是延迟执行的，它是在实际使用时执行的，不是在声明的时候执行的，查询结果变量queryResults只是保存了一个查询计划。本例中是在foreach循环时执行LINQ查询的。
2.使用LINQ方法语法
使用LINQ查询有多种方式，可以使用LINQ查询语法，如上例，也可以使用LINQ方法语法，也称显式语法。查询语法是LINQ查询的首选方式，因为查询语法比较通俗易懂，但是也需要用到方法语法，因为一些LINQ功能不能通过查询语法来查询，如聚合运算符。
static void Main(string[] args) { string[] names = { "Alonso", "Zheng", "Smith", "Jones", "Smythe", "Small", "Ruiz", "Hsieh", "Jorgenson", "Ilyich", "Singh", "Samba", "Fatimah" }; var queryResults=names.Where(n=> n.StartsWith("S")); Console.WriteLine("Names beginning with S:"); foreach (var item in queryResults) { Console.WriteLine(item); } Console.ReadLine(); }
使用LINQ方法语法只需要调用对应的方法即可，这个方法需要传送一个匿名方法作为参数，一般使用lamabda表达式创建匿名方法。
3.排序查询结果 orderby 字句
可以使用orderby 子句对查询结果进行排序，如下对结果按字母对名字进行升序排序
static void Main(string[] args) { string[] names = { "Alonso", "Zheng", "Smith", "Jones", "Smythe", "Small", "Ruiz", "Hsieh", "Jorgenson", "Ilyich", "Singh", "Samba", "Fatimah" }; var queryResults=from n in names where n.StartsWith("S") orderby n select n; Console.WriteLine("Names beginning with S:"); foreach (var item in queryResults) { Console.WriteLine(item); } Console.ReadLine(); }
orderby子句可以按照任意表达式对结果进行排序，如按照按照名字最后一个字母排序 orderby n.Substring(n.Length-1)
orderby默认升序排序，如果要降序排序可以添加descending关键字 orderby n descending
用方法语法排序
static void Main(string[] args) { string[] names = { "Alonso", "Zheng", "Smith", "Jones", "Smythe", "Small", "Ruiz", "Hsieh", "Jorgenson", "Ilyich", "Singh", "Samba", "Fatimah" }; var queryResults = names.Where(n => n.StartsWith("s")).OrderBy(n => n); Console.WriteLine("Names beginning with S:"); foreach (var item in queryResults) { Console.WriteLine(item); } Console.ReadLine(); }
给元素按降序排序可以使用OrderByDescending
var queryResults = names.Where(n => n.StartsWith("s")).OrderByDescending(n => n.Substring(n.Length-1));
多级排序
有时候需用用到多个字段来进行排序，比如按照销量降序，国家升序，城市降序，就需要联合使用OrderByOrderByDescending和ThenBy/ThenByDescending
var queryResult = customers .OrderByDescending(n=>n.Sales).ThenBy(n=>n.Country).ThenByDescending(n=>n.City);
4.聚合运算符
static void Main(string[] args) { int[] numbers = GenerateLotsOfNumbers(12345678); var queryResults = from n in numbers where n > 1000 select n; Console.WriteLine("Count of numbers >1000:"+queryResults.Count()); Console.WriteLine("Max of numbers >1000:" + queryResults.Max()); Console.WriteLine("Min of numbers >1000:" + queryResults.Min()); Console.WriteLine("Average of numbers >1000:" + queryResults.Average()); Console.WriteLine("Sum of numbers >1000:" + queryResults.Sum(n=>(long)n));//必须转换为long，因为sum太大超过了int存储的范围 Console.ReadLine(); }
使用聚合运算符在不进行循环的情况下分析结果，特别是对数字类型的结果特别有用。
5.查询复杂对象
上面的例子中查询的对象都是数字，字符串等简单类型，也可以使用LINQ查询对象等复杂数据类型
public class Customer { public string ID { get; set; } public string City { get; set; } public string Country { get; set; } public string Region { get; set; } public decimal Sales { get; set; } public override string ToString() { return "ID:" + ID + ",City:" + City + ",Country:" + Country + ",Region:" + Region + ",Sales:"+Sales; } } static void Main(string[] args) { List<Customer> customers = new List<Customer> { new Customer {ID="A" ,City ="New York" ,Country ="USA", Region="North America", Sales =9999}, new Customer {ID="B" ,City ="Munbai" ,Country ="India", Region="Asia", Sales =8888}, new Customer {ID="C" ,City ="Karachi" ,Country ="Pakistan", Region="Asia", Sales =7777}, new Customer {ID="D" ,City ="Delhi" ,Country ="India", Region="Asia", Sales =6666}, new Customer {ID="E" ,City ="S o Paulo" ,Country ="Brazil", Region="South America", Sales =5555}, }; var queryResults = from n in customers where n.Region == "Asia" select n; Console.WriteLine("Customers in Asin:"); foreach (var item in queryResults) Console.WriteLine(item); Console.ReadLine(); }
6.投影：在查询中创建新对象
投影（Projection）是在LINQ查询中从其他数据类型创建新数据类型的技术术语，select关键字是投影运算符
public class Customer { public string ID { get; set; } public string City { get; set; } public string Country { get; set; } public string Region { get; set; } public decimal Sales { get; set; } //public override string ToString() //{ // return "ID:" + ID + ",City:" + City + ",Country:" + Country + ",Region:" + Region + ",Sales:"+Sales; //} } static void Main(string[] args) { List<Customer> customers = new List<Customer> { new Customer {ID="A" ,City ="New York" ,Country ="USA", Region="North America", Sales =9999}, new Customer {ID="B" ,City ="Munbai" ,Country ="India", Region="Asia", Sales =8888}, new Customer {ID="C" ,City ="Karachi" ,Country ="Pakistan", Region="Asia", Sales =7777}, new Customer {ID="D" ,City ="Delhi" ,Country ="India", Region="Asia", Sales =6666}, new Customer {ID="E" ,City ="S o Paulo" ,Country ="Brazil", Region="South America", Sales =5555}, }; var queryResults = from n in customers where n.Region == "Asia" select new { n.Region , n.Country ,n.City ,n.Sales }; Console.WriteLine("Customers in Asin:"); foreach (var item in queryResults) Console.WriteLine(item); Console.ReadLine(); }
投影创建的匿名类型不用提供ToString重新方法，因为编译器提供了默认的重新方法，以名值对的方式输出对象（正常类如果不重写Tostring方法输出的是类名，如ConsoleApplication1.Customer）
7.Distinct()、Any()、All()、First()、FirtOrDefault()、Take()、Skip()运算符
Distinct()和SQL distinct一样返回数据集中的唯一值
var queryResult = customers.Select(n=>n.Region).Distinct(); var queryResult2 = customers.Select(n =>new {n.Country,n.City }).Distinct();
Any()检查数据集是否有任何一个元素满足条件，如果有返回true，没有返回false；All()检查数据集是否所有元素都符合条件
bool anyUSA = customers.Any(n=>n.Country=="UAS"); var allAsia = customers.All(n=>n.Region =="Asia");
First()返回数据集第一个满足条件的元素，如果没有符合条件的元素将抛出异常；FirstOrDefault()返回满足条件的第一个元素，与First不同的是如果没有符合条件的元素返回空，不会抛出异常
//查询语法 var queryResult=(from n in customers select n).First(n => n.Country == "USA");//查询语法和方法语法混用 //方法语法 var customerUSA = customers.First(n => n.Country == "USA"); var customerAsia = customers.FirstOrDefault(n => n.Region == "Asia");
take()取结果集前N条，skip()略过前N条取剩余的
var salesTop3 = customers.OrderByDescending(n=>n.Sales).Take(3); var salesLeft = customers.OrderByDescending(n => n.Sales).Skip(3);
8.组合查询
组合查询类似SQL group by，允许把数据分组，按组来排序，计算聚合值和进行比较。
var queryResult = from n in customers group n by n.Region into cg select new { TotalSales = cg.Sum(c => c.Sales), Region = cg.Key }; var orderResult = from cg in queryResult orderby cg.TotalSales descending select cg;
9.集运算符
使用集运算符，可以快速的对两个结果集进行操作。集运算符要求集成员有相同的类型，才能得到希望的结果
public class Customer { public string ID { get; set; } public string City { get; set; } public string Country { get; set; } public string Region { get; set; } public decimal Sales { get; set; } public override string ToString() { return "ID:" + ID + ",City:" + City + ",Country:" + Country + ",Region:" + Region + ",Sales:" + Sales; } } public class Order { public string ID { get; set; } public decimal Amount { get; set; } }
List<Customer> customers = new List<Customer> { new Customer {ID="A" ,City ="New York" ,Country ="USA", Region="North America", Sales =9999}, new Customer {ID="B" ,City ="Munbai" ,Country ="India", Region="Asia", Sales =8888}, new Customer {ID="C" ,City ="Karachi" ,Country ="Pakistan", Region="Asia", Sales =7777}, new Customer {ID="D" ,City ="Delhi" ,Country ="India", Region="Asia", Sales =6666}, new Customer {ID="E" ,City ="S o Paulo" ,Country ="Brazil", Region="South America", Sales =5555}, }; List<Order> orders = new List<Order> { new Order {ID ="A",Amount=100}, new Order {ID ="B",Amount=200}, new Order {ID ="F",Amount=300}, }; var customerIds = from n in customers select n.ID; var orderIds = from n in orders select n.ID; //有订单的客户ID var customerWithOrders= customerIds .Intersect(orderIds); //没有客户的订单 var ordersNoCustomers = orderIds.Except(customerIds); //客户和订单的ID集合 var allCustomersOrdersIds = customerIds.Union(orderIds);
10.联合 Join运算符
Join运算符类似SQL Join操作，可以把两个数据集用关键字段连接起来。
var queryResult = from n in customers join o in orders on n.ID equals o.ID select new { n.ID ,n.City ,SaleBefore=n.Sales ,OrderAmount=o.Amount ,SaleAfter=n.Sales +o.Amount };