Web开发之C#:(8)C#语言特性之Linq
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 >
<product Name=”West” Price=”9.99” SupplierId=”1” />
<product Name=”Frogs” Price=”13.99” SupplierId=”3” />
</products >
<suppliers >
<supplier Name=”Solely” SupplierId=”1” />
<supplier Name=”Barber” SupplierId=”2” />
<supplier Name=”Tom” SupplierId=”3” />
</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("保存成功");
}
}
}