Web、移动开发之C#:(8)C#语言特性之Linq

创建时间:2016/7/30 18:07:12
关键字: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("保存成功");

        }

    }

}