Web开发之C#：（8）C#语言特性之Linq

作者：陆金龙发表时间：2016-07-30 18:07

1.Linq用到的基础知识

1.1 集合初始化器、对象初始化器

List<Person> people = new List<Person>{

new Person { FirstName = "Scott", LastName = "Guthrie", Age = 32 },

new Person { FirstName = "Bill", LastName = "Gates", test02 = 85},

new Person { FirstName = "Susanne", Age = 32 },

null,

};

1.2 匿名类型

//匿名(类型的)对象

var anon = new {Name ="Terry",Age = 34};

//在数组中填充匿名类型的对象

var family = new[]

{

new {Name ="Terry",Age = 34},

new {Name ="Juesy",Age = 30},

new {Name ="Lily",Age = 12},

new {Name ="Tom",Age = 19}

};

//计算总年龄

int totalAge=0;

foreach(var person in family)

{

totalAge =person.Age;

}

1.3 扩展方法

namespace ExtenMethod

{

public static class Program {

//扩展方法定义

public static int ToInt32(this string s) {

return Int32.Parse(s);

}

static void Main(string[] args)

{

String str="250";

int num1=str.ToInt32();//扩展方法到string类

Console.WriteLine(num1);

}

2.Linq查询基础

2.1 Linq查询三步骤

2.1.1 步骤一：获取数据源

int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };

2.1.2 步骤二：定义查询

//numQuery 是 IEnumerable<int>泛型

var numQuery =

from num in numbers

where (num % 2) == 0

orderby num descending

select num;

2.1.3 步骤三：执行查询

foreach (int num in numQuery)

{

Console.Write("{0,1} ", num);

}

2.2 Linq查询三大数据源

Linq 查询可针对 Object，XML，SQL进行，查询语法基本相同。

2.2.1 Linq To Object

List<Product> products =Product.GetData();

List<Supplier> suppliers=Supplier.GetData();

var objs = from p in products

join s in suppliers

on p.SupplierId equals s.SupplierId

where p.Price>10

order by s.Name,p.Name

select new{SupplierName=s.Name,ProductName=p.Name};

按名称排序和过滤重复数据：

List<Product> productsNew = (from product in products select product).OderBy< Product (p=>p.Name).Distinct().ToList();

2.2.2 Linq To XML

1)data.xml文档内容

<?xml version=”1.0”?>

<Data>

</products >

</suppliers >

</Data>

2)Linq To Xml 查询

Xdocument doc = Xdocument.Load(“data.xml”);

var objs = from p in doc.Decendants(“products”);

join s in doc.Decendants(“suppliers”);

on (int)p.Attribute(“SupplierId”) equals (int)s.Attribute(“SupplierId”)

where (decimal)p.Attribute(“Price”)>10

order by (string)s.Attribute(“Name”),(string)p.Attribute(“Name”)

select new{SupplierName=(string)s.Attribute(“Name”),ProductName=(string)p.Attribute(“Name”)

};

foreach(var obj in objs)

{

Console.WriteLine(“{0},{1}”,obj.SupplierName,obj.ProductName);

}

2.2.3 Linq To SQL

1)DataContext

查询是用C#代码来表示的，但是却是由SQL来执行的。

DataContext类型（数据上下文）是System.Data.Linq命名空间下的重要类型，用于把查询句法翻译成SQL语句，以及把数据从数据库返回给调用方和把实体的修改写入数据库。LinqDemoDataContext db = new LinqDemoDataContext();

2)Linq To SQL 查询

using(LinqDemoDataContext db = new LinqDemoDataContext())

{

var objs = from p in db.Products

join s in db.Suppliers

on p.SupplierId equals s.SupplierId

where p.Price>10

order by s.Name,p.Name

select new{SupplierName=s.Name,ProductName=p.Name};

foreach(var obj in objs)

{

Console.WriteLine(“{0},{1}”,obj.SupplierName,obj.ProductName);

}

3.Linq查询语法详解

3.1 查询数组

3.1.1查询数组

static void Main(string[] args)

{

//声明一个有6个元素的字符串数组names，声明一个匿名变量items，使用LINQ在数组names中找出长度，超过5的元素，将找到的字符串转为大写并排序，执行查询输出结果。

string[] names = { "Everett", "Albert", "George", "Harris", "David" };

var items = from name in names

where name.Length >= 6

orderby name

select name.ToUpper();

foreach (var item in items)

Console.WriteLine(item);

}

3.1.2查询数组应用

static IEnumerable<string> QueryMethod(ref int[] ints) //定义方法，返回查询

{

var intsToStrings = from i in ints where i > 4 select i.ToString();//查询ints中大于4的元素

return intsToStrings;

}

static void Main(string[] args)

{

int[] nums = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; //定义查询数据源nums

var myQuery = QueryMethod(ref nums); //调用查询的方法

foreach (string s in myQuery)//执行查询

{

Console.WriteLine(s);

}

//修改查询，从myQuery结果中取3条结果按降序排列

myQuery = (from str in myQuery orderby str descending select str). Take(3);

//执行修改后的查询

foreach (string s in myQuery)

{

Console.WriteLine(s);

}

3.2 查询实体对象集合

3.2.1 简单查询

static void QueryByID(string[] ids)

{

var queryNames =from student in students let i = student.ID.ToString()

where ids.Contains(i) select new { student.Name, student.ID };

foreach (var name in queryNames)

{

Console.WriteLine("姓名：{0}，学号: {1}", name.Name, name.ID);

}

3.2.2 分组查询、分组求和

1.分组查询

// 创建LINQ查询数据源 students

List<Student> students = GetStudents();

// 创建LINQ查询

IEnumerable<Student> sortedStudents =

from student in students

orderby student.Last ascending, student.First ascending //先按Lastname排序，再按First排序

select student;

// 执行查询.

foreach (Student student in sortedStudents)

Console.WriteLine(student.Last " " student.First);

// 创建分组并排序.

var sortedGroups =

from student in students

orderby student.Last, student.First

group student by student.Last[0] into newGroup//按照Lastname首字母进行分组

orderby newGroup.Key

select newGroup;

2.分组求和

//单个字段分组求和

var yearsData = (from d in list

group d by new { d.Year } into g

select new

{

Year = g.Key.Year,

Sum = g.Sum(p => Convert.ToDecimal(p.Vaule))

}).OrderBy(d => d.Sum).ToList();

//多字段分组求和（数据源为DataTable）

var result =from c in t.AsEnumerable()

group c by new { name= c.Field<string>("name"), spec= c.Field<string>("spec") } into s select new {

//对每一组结果查询得到一个对象

name= s.Select(p => p.Field<string>("name")).First(),

spec= s.Select(p => p.Field<string>("spec")).First(), count= s.Sum(p => Convert.ToInt32(p.Field<int>("count")))

};

3.2.3 连接查询、分组连接查询

int[] intAry1 = { 5, 15, 25, 30, 40, 50, 60, 70, };//定义数据源1

int[] intAry2 = { 10, 20, 30, 40, 50, 60 };//定义数据源2

1.普通连接查询

内部连接查询，查询数据源1中和数据源2中相等的元素；

var query1 = from val1 in intAry1

join val2 in intAry2 on val1 equals val2

select new { Val1 = val1, Val2 = val2 };

2.左外部连接查询

查询数据源1中和数据源2中相等的元素,将查询结果按照第一个数据集中的所有元素进行分组,并将数据分组并保存到valGrp中；

var query1 = from val1 in intAry1

join val2 in intAry2 on val1 equals val2 into valGrp

from grp in valGrp.DefaultIfEmpty()

select new { Val1 = val1, Val2 = grp };

3.分组连接查询

查询数据源1中和数据源2中相等的元素,将查询结果按照

第一个数据集中的元素进行分组,并将数据分组并保存到valGrp中；

var query2 = from val1 in intAry1

join val2 in intAry2 on val1 equals val2 into valGrp

select new { Val1 = val1, Val2 = valGrp };

3.2.4 查询结果处理

1.常规语法查询

static void Main(string[] args)

{

//定义数据源

int[] numbers = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

//查询LINQ语句，查询numbers中的偶数,按降序排列

var even = from m in numbers where (m % 2 == 0) orderby m descending select m;

//执行查询，输出偶数元素

foreach (var item in even )

Console.Write(" {0}",item);

//利用Count方法，实现统计numbers中的偶数个数

var evenCount = (from m in numbers where (m % 2 == 0) select m).Count();

//混合模式求最大值、最小值、平均值、求和

int maxValue = (from m in numbers select m).Max();

int minValue = (from m in numbers select m).Min();

var aver = (from m in numbers select m).Average();

var sum = (from m in numbers select m).Sum();

//分区类查询，取出数组跳过第一个元素后的两个数组元素

var zone1 = numbers.Skip(1).Take(2);

// var zone2 = numbers.SkipWhile((x, i) => i < 5); //在numbers中查询不小于5的元素，结果为{5，6，7，8，9}

// var zone3 = numbers.TakeWhile((x, i) => i < 5); //在numbers中查询小于5的元素，结果为{0，1，2，3，4}

}

2.纯粹语法查询（Lambda表达式）

static void Main(string[] args)

{

//定义数据源

int[] numbers = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

//查询LINQ语句，查询numbers中的偶数,按降序排列

var even=numbers .Where (p => p%2 == 0).OrderByDescending(p => p).Select(p => p);

//利用Count方法，实现统计numbers中的偶数个数

int evenCount = numbers.Where(p => p%2 == 0).Count();

//纯粹查询语法

int maxValue = numbers.Select(p => p).Max();

int minValue = numbers.Select(p => p).Min();

var num1 = numbers.Select(p => p).Average();

var num3 = numbers.Select(p => p).Sum();

//分区类查询，取出数组跳过第一个元素后的两个数组元素

var num5 = numbers.Skip(1).Take(2);

}

3.2.5 Linq To DataSet

DataTable products = sds.Tables[0];

var productQuery =

from product in products.AsEnumerable()

orderby product.Field<string>(1) ascending

select product;

//输出Product表前10行，第一列ProductID表示Produc主键,

//第二列Name表示ProductNumber产品的名称第三列ProductNumber表示唯一的产品标识号。

foreach (DataRow product in productQuery)

{

Console.WriteLine("{0} {1} {2}", product[0], product[1], product[2]);

}

var productQuery =products.AsEnumerable()

.OrderByDescending (p=>p.Field <string >(1)).Select( p=>p );// LINQ查询

3.2.6 Linq高级查询：使用Lambda

将Lambda表达式、表达式树和扩展方法合并到一起，是LINQ在C#语言这一面的全部体现。

string[] names = { "Everett", "Albert", "George", "Harris", "David" };

var items = from name in names

where name.Length >= 6

orderby name

select name.ToUpper();

//Lambda等价写法，效果和上面语句一样

var items2 = names.Where(name => name.Length >= 6).OrderBy(name => name).

Select(name => name.ToUpper());

//创建多字段排序的Lambda语法

IEnumerable<Student> sortedStudents = students.OrderBy(p => p.Last).ThenBy(p => p.First);

// 创建分组并排序.

var sortedGroups = (students.OrderBy(p => p.Last).ThenBy(p => p.First)

.GroupBy(p => p.Last[0])).OrderBy(p => p.Key);

3.3 Linq查询中的数据库类型

3.3.1 不转换源数据的查询

下面代码演示了不对数据执行转换的 LINQ to Objects 查询操作。

源包含一个字符串序列，查询输出也是一个字符串序列：

List<string> names = new List<string>{"Anderson","Bliss","Pessi","Tony","Pato"};

IEnumerable<string> nameQuery = from name in names

where name[0] =='P'

select name;

3.3.2 转换源数据的查询

下面的代码演示了对数据执行简单转换的 LINQ to SQL 查询操作。

查询将Customer 对象序列作为输入，并只选择结果中的 Name 属性。因为 Name 是一个字符串，所以LINQ查询将Customer 对象转换为一个字符串输出。

Table<Customers> customers = db.GetTable<Customers>();

IQueryable<string> custNameQuery = from cust in customers where cust.City == "London" select cust.ContactName ;

foreach (string str in custNameQuery)

Console.WriteLine(str);

3.3.3 让编译器推断类型信息

关键字 var 可用于查询操作中的任何局部变量,编译器将为查询操作中的各个变量提供强类型。

Table<Customer> Customers = db.GetTable<Customers>();

var namePhoneQuery = from cust in customers where cust.City == "London" select new { name = cust.ContactName, phone = cust.Phone };

附：Linq To XML应用 - 数据库连接工具

主要功能：

1)获取数据库连接设置，显示到界面。

2)测试数据库连接是否成功

3)修改和保存数据库连接设置

代码清单：

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Xml.Linq;

using System.Configuration;

using System.Text.RegularExpressions;

using System.Data.SqlClient;

namespace WindowsFormsApplication1

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

string connstr = null;

/// <summary>

/// 加载窗体，显示配置文件中的连接字符串数据到文本框

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void Form1_Load(object sender, EventArgs e)

{

string connTemp = null;

XDocument xdoc = XDocument.Load("LinqToXml_EditApp.exe.config");

var query = from conn in

(from ele in xdoc.Descendants()

where ele.Name == "connectionStrings"

select ele).Elements()

where conn.Attribute("name").Value == "sql"

select conn.Attribute("connectionString").Value;

var temp = query.GetEnumerator();

if (temp.MoveNext())

{

//得到连接字符串

connTemp = temp.Current;

//分割得到连接字符串各参数的值

List<string> list = new List<string>();

string[] strs = connTemp.Split(';');

for (int i = 0; i < 4; i )

{

list.Add(strs[i].Split('=')[1].Trim());

}

//给文本框赋值

textServer.Text = list[0];

textDatabase.Text = list[1];

textUser.Text = list[2];

textPass.Text = list[3];

}

/// <summary>

/// 测试数据库连接

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void button1_Click(object sender, EventArgs e)

{

//拼接得到连接字符串

string server = textServer.Text.Trim();

string database = textDatabase.Text.Trim();

string uid = textUser.Text.Trim();

string pwd = textPass.Text.Trim();

if (string.IsNullOrEmpty(server) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(uid) || string.IsNullOrEmpty(pwd))

{

MessageBox.Show("输入不能为空！");

return;

}

connstr = "server = " server ";database = " database ";uid = " uid ";pwd = " pwd;

using (SqlConnection conn = new SqlConnection(connstr))

{

conn.Close();

try

{

conn.Open();

MessageBox.Show("连接成功！");

}

catch (Exception)

{

MessageBox.Show("连接失败！");

}

/// <summary>

/// 保存新的连接字符串数据到配置文件

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

private void button2_Click(object sender, EventArgs e)

{

//拼接得到连接字符串

string server= textServer.Text.Trim() ;

string database = textDatabase.Text.Trim() ;

string uid = textUser.Text.Trim();

string pwd = textPass.Text.Trim();

if (string.IsNullOrEmpty(server)||string.IsNullOrEmpty(database)||string.IsNullOrEmpty(uid)||string.IsNullOrEmpty(pwd))

{

MessageBox.Show("输入不能为空！");

return;

}

connstr = "server = " server ";database = " database ";uid = " uid ";pwd = " pwd;

//用LinqToXml读取app文件

XDocument xdoc = XDocument.Load("LinqToXml_EditApp.exe.config");

//获取connectionStrings元素

var conns = from conn in xdoc.Descendants()

where conn.Name == "connectionStrings"

select conn ;

//创建枚举器,获取第一个add节点

//<add name="conString" connectionString="Data Source=.;Initial Catalog=cms;user id =king;password=king"/>

var enumtor=conns.Elements().GetEnumerator();

enumtor.MoveNext();

XElement addNode= (XElement)enumtor.Current;

//修改属性值，更新xdoc到配置文件

addNode.LastAttribute.SetValue(connstr);

xdoc.Save("LinqToXml_EditApp.exe.config");

MessageBox.Show("保存成功");

}