C#编程之Linq查询
1.Linq查询
Linq 查询可针对 Object，XML，SQL进行，查询语法基本相同。
1.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};
foreach(var obj in objs)
{
Console.WriteLine(“{0},{1}”,obj.SupplierName,obj.ProductName);
}
按名称排序和过滤重复数据：
List<Product> productsNew = (from product in products select product).OderBy< Product (p=>p.Name).Distinct().ToList();
1.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);
}
3) 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=lyncplus2013;user id =lyncplus;password=lyncplus"/>
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("保存成功");
}
}
}
1.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);
}
}
2.Linq查询应用详解
2.0 基本步骤
// 1. LINQ查询第一步，获取数据源.
int[] numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
// 2. LINQ查询第二步，定义查询numQuery 是 IEnumerable<int>泛型
var numQuery =
from num in numbers
where (num % 2) == 0
orderby num descending
select num;
// 3. LINQ查询第三步执行查询.
foreach (int num in numQuery)
{
Console.Write("{0,1} ", num);
}
Console.Read ();
2.1 查询数组
2.1.1 查询数组
static void Main(string[] args)
{
//声明一个有6个元素的字符串数组names
string[] names = { "Everett", "Albert", "George", "Harris", "David" };
var items = from name in names //声明一个匿名变量items，使用LINQ在数组names中找出长度
where name.Length >= 6 //超过5的元素，将找到的字符串转为大写并排序，
orderby name //将LINQ执行的结果赋给匿名变量items;
select name.ToUpper();
foreach (var item in items)
Console.WriteLine(item); //输出结果
Console.Read();
}
2.1.2查询数组应用
namespace MethodQuery
{
class Program
{
IEnumerable<string> QueryMethod(ref int[] ints) //定义方法QueryMethod，返回查询
{
var intsToStrings = from i in ints where i > 4 select i.ToString();//查询ints中大于4的元素
return intsToStrings;
}
static void Main(string[] args)
{
Program app = new Program();//创建对象app，以调用查询方法QueryMethod
int[] nums = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 }; //定义查询数据源nums
var myQuery = app.QueryMethod(ref nums);//调用查询的方法QueryMetho1
foreach (string s in myQuery)//执行查询
{
Console.WriteLine(s);
}
//修改查询，从myQuery结果中3取条结果按降序排列
myQuery = (from str in myQuery orderby str descending select str). Take(3);
Console.WriteLine("执行修改后的查询:");
foreach (string s in myQuery) //执行修改后的查询
{
Console.WriteLine(s);
}
Console.ReadKey(); //暂停运行，按任意键继续
}
}
}
2.2 查询实体对象集合
2.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);
}
}
2.2.2 分组查询
static void Main(string[] args)
{
// 创建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;
// 执行查询.
Console.WriteLine("排好序的姓和名是:");
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;
// 执行查询.
Console.WriteLine(Environment.NewLine + "分组排序结果是：");
foreach (var studentGroup in sortedGroups)
{
Console.WriteLine("分组的首字母是："+studentGroup.Key);
foreach (var student in studentGroup)
{
Console.WriteLine(" {0}, {1}", student.Last, student.First);
}
}
Console.WriteLine("Press any key to exit.");
Console.ReadKey();
}
2.2.3 分组连接查询
static void Main(string[] args)
{
int[] intAry1 = { 5, 15, 25, 30, 40, 50, 60, 70, };//定义数据源1
int[] intAry2 = { 10, 20, 30,30, 40, 50, 60 };//定义数据源2
//分组联接查询，查询数据源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 };
//执行查询，输出结果
Console.WriteLine("分组联接查询的结果,var1是分组关键字,var2分组查询结果");
foreach (var obj in query2)
{
Console.Write("Var1={0},", obj.Val1);
foreach (var item in obj.Val2)
{
Console.Write(" Var2={0} ", item);
}
Console.WriteLine();
}
//左外部联接查询，查询数据源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 };
Console.WriteLine("左外部联接查询的结果，var1是分组关键字，var2左外连接查询结果");
foreach (var item in query1)
{
Console.WriteLine(item);
}
Console.WriteLine();
Console.Read();
}
2.2.4 连接查询
static void Main(string[] args)
{
int[] intAry1 = { 5, 15, 25, 30, 40, 50, 60, 70, };//定义数据源1
int[] intAry2 = { 10, 20, 30, 40, 50, 60 };//定义数据源2
//内部连接查询，查询数据源1中和数据源2中相等的元素；
var query1 = from val1 in intAry1
join val2 in intAry2 on val1 equals val2
select new { Val1 = val1, Val2 = val2 };
//执行查询，输出结果
Console.WriteLine("内部连接查询的结果是：");
foreach (var item in query1)
{
Console.WriteLine(item);
}
Console.Read();
}
2.2.5 查询结果处理
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;
//执行查询
Console.Write("数组numbers中的偶数元素是:");
foreach (var item in even )
Console.Write(" {0}",item);
//利用Count方法，实现统计numbers中的偶数个数
var evenCount = (from m in numbers where (m % 2 == 0) select m).Count();
Console.WriteLine("。偶数个数是:{0}", evenCount);
//混合模式求最大值的
int maxValue = (from m in numbers select m).Max();
//混合模式求最小值
int minValue = (from m in numbers select m).Min();
Console.Write("数组numbers中的最大值是:{0}", maxValue);
Console.Write("，最小值是:{0}", minValue);
//用混合模式求平均值
var aver = (from m in numbers select m).Average();
Console.Write("，平均值是{0}", aver.ToString());
//混合模式求总和
var sum = (from m in numbers select m).Sum();
Console.WriteLine(",和是{0}", sum.ToString());
//分区类查询，取出数组跳过第一个元素后的两个数组元素
var zone1 = numbers.Skip(1).Take(2);
Console.Write("分区类查询结果:");
foreach (var item in zone1)
{
Console.Write(" {0}", item);
}
// 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}
Console.ReadKey();
}
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;
var even=numbers .Where (p => p%2 == 0).OrderByDescending(p => p).Select(p => p);
//执行查询
Console.Write("数组numbers中的偶数元素是:");
foreach (var item in even)
Console.Write(" {0}", item);
Console.WriteLine();
//利用Count方法，实现统计numbers中的偶数个数
int evenCount = numbers.Where(p => p%2 == 0).Count();
Console.WriteLine("数组numbers中的偶数个数是:{0}", evenCount);
//纯粹查询语法
int maxValue = numbers.Select(p => p).Max();
int minValue = numbers.Select(p => p).Min();
Console.WriteLine("数组numbers中的最大值是:{0}", maxValue);
Console.WriteLine("数组numbers中的最小值是:{0}", minValue);
//纯粹查询模式求平均值
var num1 = numbers.Select(p => p).Average();
Console.WriteLine("混合模式获取numbers中元素的平均值是={0}", num1.ToString());
//纯粹查询模式求总和
var num3 = numbers.Select(p => p).Sum();
Console.WriteLine("混合模式获取的数组numbers中元素的和={0}", num3.ToString());
//分区类查询，取出数组跳过第一个元素后的两个数组元素
var num5 = numbers.Skip(1).Take(2);
Console.Write("分区类查询结果:");
foreach (var item in num5)
{
Console.Write(" {0}", item);
}
Console.ReadKey();
}
2.2.6 Linq To DataSet
DataTable products = sds.Tables[0];
// LINQ查询
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 productQuery1 =products.AsEnumerable().OrderByDescending (p=>p.Field <string >(1)).Select( p=>p );// LINQ查询
var productQuery1 = products.AsEnumerable().Select(p => p);// LINQ查询
2.2.7 扩展
//声明一个有6个元素的字符串数组names
string[] names = { "Everett", "Albert", "George", "Harris", "David" };
var items = from name in names //声明一个匿名变量items，使用LINQ在数组names中找出长度
where name.Length >= 6 //超过5的元素，将找到的字符串转为大写并排序，
orderby name //将LINQ执行的结果赋给匿名变量items;
select name.ToUpper();
//等价写法，效果和上面语句一样
var items2 = names.Where(name => name.Length >= 6).OrderBy(name => name).
Select(name => name.ToUpper());
class Program
{
static void Main(string[] args)
{
/// <summary>
/// 传统查询方式
/// </summary>
int[] numbers = new int[] { 1,2,5,8,9,10,11,14,17,16,21 };//定义数组用于查找
List<int> even = new List<int>(); //定义集合用于存储查找的结果
foreach (int number in numbers) //遍历数组查找所有的偶数，添加到集合even中
{
if (number % 2 == 0)
{
even.Add(number);
}
}
even.Sort(); //对查找的结果排序
}
}
// 创建LINQ查询
IEnumerable<Student> sortedStudents = students.OrderBy(p => p.Last).ThenBy(p => p.First);
// 执行查询.
Console.WriteLine("排好序的姓和名是:");
foreach (Student student in sortedStudents)
Console.WriteLine(student.Last + " " + student.First);
// 创建分组并排序.
var sortedGroups = (students.OrderBy(p => p.Last).ThenBy(p => p.First).GroupBy(p => p.Last[0])).OrderBy(p => p.Key);
// 执行查询.
Console.WriteLine(Environment.NewLine + "分组排序结果是：");
foreach (var studentGroup in sortedGroups)
{
Console.WriteLine("分组的首字母是："+studentGroup.Key);
foreach (var student in studentGroup)
{
Console.WriteLine(" {0}, {1}", student.Last, student.First);
}
}
2.3 LinqDataType
using System.Data.Linq .Mapping ;
using System.Data.SqlClient;
namespace LinqDataType
{
class Program
{
static void Main(string[] args)
{
//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;
foreach (string str in nameQuery)
Console.WriteLine(str);
//下面的操作用到Northwind数据库中的Customers表，利用Linq to Sql创建DataCustomers.dbml
//对数据库操作，创建数据库上下文对象db
DataCustomersDataContext db=new DataCustomersDataContext ();
//2. 转换源数据的查询
//下面的代码演示了对数据执行简单转换的 LINQ to SQL 查询操作。
//查询将Customer 对象序列作为输入，并只选择结果中的 Name 属性。因为 Name 是一个字符串，所以LINQ查询将Customer 对象转换为一个字符串输出。
Table<Customers> customers = db.GetTable<Customers>(); //得到customers表
IQueryable<string> custNameQuery = from cust in