【笔记】【LINQ编程技术内幕】第七章 对查询进行排序和分组

对信息进行排序

通过orderby关键字，LINQ可以实现升序和降序排列。LINQ还支持次要排序(也可以按升序和降序排列)，只要将需要排序的项用逗号隔开即可。

升序，将一句话中的单词拆开，然后将这些单词升序排列

string quote = "Most people have the will to win, few have the will to prepare to win.";
string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);

// 依据指定的内容进行升序排列，同时 `ascending`可以省略，因为默认执行升序排列
var sorted = from word in words 
             orderby word ascending
             select word;

foreach (var s in sorted)
	Console.WriteLine(s);

降序

string quote = "Most people have the will to win, few have the will to prepare to win.";
string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);
var descending = from word in words 
                 orderby word descending
                 select word;

foreach (var s in descending)
	Console.WriteLine(s);

扩展方法降序

string quote = "Most people have the will to win, few have the will to prepare to win.";
string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);
foreach (var s in words.OrderByDescending(s => s))
	Console.WriteLine(s);

次要排序

var gamblers = new[]
{
	new {LastName="Kimmel",  First="Paul", Age=41},
	new {LastName="Swanson", First="Dena", Age=26},
	new {LastName="Swanson", First="Joe",  Age=4},
	new {LastName="Kimmel",  First="Noah", Age=11}
};

// 在关键字 orderby 之后，列举出的项是排序依据项，其中排序优先级依次降低。同时每个依据项可天降ascending或descending关键字用于指定是升序还是降序排列
var sordid = from gambler in gamblers
			 orderby gambler.LastName, gambler.Age
			 select gambler;

foreach (var playa in sordid)
	Console.WriteLine(playa);

// 实现相同功能的拓展方法版本
gamblers.OrderBy(item=>item.LastName).ThenBy(item=>item.Age).Select(item=>item);

反转顺序

string quote = "Most people have the will to win, few have the will to prepare to win.";
string[] words = quote.Split(new char[] { ' ', ',', '.' }, StringSplitOptions.RemoveEmptyEntries);

foreach (var s in words.Reverse())
	Console.WriteLine(s);

对信息进行分组

LINQ中的许多概念都跟SQL很相似。其中之一就是根据相似性将数据组织到逻辑分组中。在LINQ中，分组功能是通过group by子句实现的。

var nums = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
// 根据条件执行分组，该例中 n % 2 只有两个值0和1，结果被分为两组
// 如果使用 n % 3 则会被分成 3 组
var result = from n in nums group n by n % 2;

// really quite funky
Array.ForEach(result.ToArray(), x =>
{
	Console.WriteLine(x.Key == 0 ? "evens:" : "odds:");
	Array.ForEach(x.ToArray(), y => Console.WriteLine(y));
});

数据库查询 + LINQ分组

class Program
{
	// 查询语句，内连接。左表中一条记录可连接右表中满足条件的多条记录
	static string sql = "SELECT Categories.CategoryName, Products.ProductID, Products.ProductName, Products.UnitsInStock, Products.UnitPrice " +
	                    "FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID";

	// 连接字符串
	static string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=True;";

	// 定义一个数据类，用于存储SQL查询的单条记录
	public class ProductItem
	{
		public string CategoryName { get; set; }
		public int ProductID { get; set; }
		public string ProductName { get; set; }
		public int UnitsInStock { get; set; }
		public decimal UnitPrice { get; set; }

		public override string ToString()
		{
			const string mask = "Category Name: {0}, Product ID: {1}, Product Name: {2}, Units In Stock: {3}, Unit Price: {4}";
			return string.Format(mask, CategoryName, ProductID, ProductName, UnitsInStock, UnitPrice);
		}
	}

	// 读取辅助类，用以判断是否为空值
	static T SafeRead<T>(IDataReader reader, string name, T defaultValue)
	{
		object o = reader[name];
		if (o != System.DBNull.Value && o != null)
			return (T)Convert.ChangeType(o, defaultValue.GetType());

		return defaultValue;
	}

	static void Main(string[] args)
	{
		// 定义存储查询结果的集合
		List<ProductItem> products = new List<ProductItem>();

		// 执行查询
		using (SqlConnection connection = new SqlConnection(connectionString))
		{
			connection.Open();
			SqlCommand command = new SqlCommand(sql, connection);
			SqlDataReader reader = command.ExecuteReader();
			while (reader.Read())
			{
				products.Add(new ProductItem
				{
					CategoryName = SafeRead(reader, "CategoryName", ""),
					ProductID = SafeRead(reader, "ProductID", -1),
					ProductName = SafeRead(reader, "ProductName", ""),
					UnitsInStock = SafeRead(reader, "UnitsInStock", 0),
					UnitPrice = SafeRead(reader, "UnitPrice", 0M)
				});
			}
		}
		
		// 分割线
		string line = new string('-', 40);

		// 定义LINQ
		// 将分组结果被组织到IGrouping对象中，该对象含有一个Key/Element对。
		// Key代表的是含有数据的分组，而Element则代表的是数据
		// 通过 into 子句将分组结果存储于指定的变量grp中
		var grouped = from p in products
					  orderby p.CategoryName, p.ProductName
					  group p by p.CategoryName into grp
					  select new { Category = grp.Key, Product = grp };

		// 打印结果
		Array.ForEach(grouped.ToArray(), g =>
		{
			Console.WriteLine(g.Category);
			Console.WriteLine(line);
			
		  	// dump each product in the group
		  	Array.ForEach(g.Product.ToArray(), p => Console.WriteLine(p));
			Console.WriteLine(Environment.NewLine);
		});
		
	}
}