LINQ学习笔记

Technorati 标签: LINQ C#3.0

1.   LINQ是什么？LINQ要解决什么问题？

a)         LINQ(Language-INtegrated Query) 是 Orcas(VS2008) 提供的一套超强扩展功能，可以用一种标准、简单的方式查询和更新数据。被查询的数据可以是 .NET Framework collections、 SQL databases、ADO.NET Datasets、XML documents，以及其他任何实现 IEnumerable<T> 的数据类型。LINQ 是一种语言扩展，可以使用类似 SQL 的近自然语法或扩展方法 API 来操作这些数据源。

b)        解决的问题

                        i.              Data != Object

                      ii.              统一集合数据查询模型

2.   C# 3.0的新特性，优雅

a)         隐式类型变量(Implicitly typed local variables)

var关键字，编译器翻译，不能在运行期改变类型

var a = 123;

var b = "123";

 

b)        扩展方法(Extension Method)

为既有类型添加额外的功能

(1) 必须在静态类中定义。

(2) 必须是静态方法。

(3) 第一个参数必须添加 this 关键字，且参数类型必须是目标类型。

(4) 扩展方法不能访问目标类型 private、protected 成员。

(5) 同一目标类型不能有多个名称、签名一样的扩展方法。

(6) 如果扩展方法和目标类型方法名称和参数签名相同，则优先调用目标类型方法。

static class StringExtension

{

    public static void PrintLine(this string s)

    {

        Console.WriteLine(s);

    }

 

    public static void PrintLine(this string s, int count)

    {

        for (int i = 0; i < count; i++)

        {

            Console.WriteLine(s);

        }

    }

}

 

c)         对象初始化（Object initializers）

class MyClass

{

    public MyClass()

    {

    }

 

    public MyClass(int x, int y)

    {

        this.x = x;

        this.y = y;

    }

 

    private int x;

 

    public int X

    {

        get { return x; }

        set { x = value; }

    }

 

    private int y;

 

    public int Y

    {

        get { return y; }

        set { y = value; }

    }

 

    private string s;

 

    public string S

    {

        get { return s; }

        set { s = value; }

    }

}

 

Call Code:

MyClass o1 = new MyClass { X = 1, Y = 2, S = "abc" };

MyClass o2 = new MyClass { X = 1, Y = 2 };

MyClass o3 = new MyClass(1, 2) { S = "abc" };

 

d)        匿名属性(Automatic properties)

(1) 必须同时包含 get; set; 定义。(由于关联字段是编译器自动生成的，没有 set 访问器也就意味着永远没有办法为该属性赋值。同理，没有 get 也不成。)

(2) 可以为 get; set; 定义不同的访问权限。

class MyClass

{

    public int X { get; set; }

public int Y { get; set; }

public int Z { get; internal set; }

public string S { get; set; }

}

 

e)         匿名类型(Anonymous types)

var o1 = new { X = 1, Y = 2, S = "abc"};

var o2 = new { X = 1, Y = 2, S = "abc"};

Console.WriteLine(o1 == o2);

Console.WriteLine(o1.GetType() == o2.GetType());

匿名类型特征：

(1) 编码时没有定义类型名称。

(2) 通过对象初始化器定义属性。

(3) 引用变量只能是隐式类型或 Object。

      

应用环境：

(1) 局部信息存储交换。

(2) LINQ 返回信息存储。

(3) 用于数据控件绑定源。

      

f)         Lambda expressions

.Net 1.1时代的委托

public delegate bool NumberTester(int i);

 

public static bool IsOdd(int i)

{

    return (i % 2) == 1;

}

 

public static void PrintMatchingNumbers(int from, int to, NumberTester filter)

{

    for (int i = from; i <= to; ++i)

    {

        if (filter(i))

        {

            Console.WriteLine(i);

        }

    }

}

 

Call Code:

PrintMatchingNumbers(1, 10, new NumberTester(IsOdd));

 

.Net 2.0时代的匿名方法

public delegate bool NumberTester(int i);

public static void PrintMatchingNumbers(int from, int to, NumberTester filter)

{

    for (int i = from; i <= to; ++i)

    {

        if (filter(i))

        {

            Console.WriteLine(i);

        }

    }

}

 

Call Code:

PrintMatchingNumbers(1, 10, delegate(int i)

{

    return (i % 2) == 1;

});

 

.Net 3.0时代的Lambda expressions（参数=> 表达式）

public delegate bool NumberTester(int i);

public static void PrintMatchingNumbers(int from, int to, NumberTester filter)

{

    for (int i = from; i <= to; ++i)

    {

        if (filter(i))

        {

            Console.WriteLine(i);

        }

    }

}

 

Call Code:

PrintMatchingNumbers(1, 10, i => (i % 2) == 1);

 

3.   LINQ是如何实现的

在 System.Linq 名字空间下，你会发现大量直接或间接绑定到 IEnumerable<T> 的扩展方法。也就是说 LINQ 的功能都是通过这些扩展方法实现的，而编译器负责将 LINQ 语法翻译成对这些扩展方法的调用。

4.   LINQ的组成

5.   LINQ开门三件事

a)         获得数据源

b)        创建查询

c)         执行查询

 

//  Data source.

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

 

//  Query creation.

IEnumerable<int> numQuery =

    from num in numbers

    where (num % 2) == 0

    select num;

 

//  Query execution.

foreach (int j in numQuery)

{

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

}

 

numQuery.ToList().ForEach(n => Console.WriteLine(n));

 

/*

IEnumerable<int> numQuery =

    numbers.Where(new Func<int,bool>(IsEven));

   

    numbers.Where(

        delegate(int n)

        {

            return (n % 2) == 0;

        }

    );

   

    numbers.Where(n => (n % 2) == 0);

from num in numbers where (num % 2) == 0 select num;

 

* public static bool IsEven(int n)

    {

        return (n % 2) == 0;

    }

 * */

 

6.   LINQ To Object

a)         DEMO：获取String类型的方法

（贯穿C# 3.0的特性，Lambda表达式，简单表达式，Select子句，OrderBy子句，Form子句，Where子句，GroupBy子句，对结果的去重，限制结果个数等）

MethodInfo[] methodInfos = typeof(string).GetMethods();

 

var query = from i in methodInfos

orderby i.Name

select i.Name;

 

IEnumerable<string> query = methodInfos

                .OrderBy(i => i.Name)

                .Select(i => i.Name);

 

var query = from i in methodInfos

            where i.IsPublic

            orderby i.Name

            select i.Name;

 

var query = from i in methodInfos

            where i.IsPublic

            orderby i.Name

            select new { i.Name, i.ReturnType };

 

var query = (from i in methodInfos

            where i.IsPublic

            orderby i.Name

            select new { i.Name, i.ReturnType }).Distinct();

 

var query = (from i in methodInfos

            where i.IsPublic

            orderby i.Name

            select new { i.Name, i.ReturnType }).Distinct().Take(1);

 

var query = from i in methodInfos

            where i.IsPublic

            orderby i.Name

            group i by i.Name into g

            select new {KEY = g.Key, Items = g};

 

foreach (var s in query)

{

    Console.WriteLine(s.KEY);

    foreach (var q in s.Items)

    {

        Console.WriteLine("\t{0}", q.GetParameters().Count());

    }

}

 

var query = from i in methodInfos

            where i.IsPublic

            orderby i.Name

            group i by i.Name into g

            select new { KEY = g.Key, MaxParameterCount = g.Max(p=>p.GetParameters().Count()) };

 

b)        DEMO：获取Int类型相对String所特有的方法； 获取Int类型和String的全部方法；获取Int类型和String类型的共有方法

（两个集合求差Except，求合Union，关联Join）

（查询结果就是集合，集合就可以查询。查询可以嵌套）

var query = (from i in typeof(int).GetMethods()

            select i.Name).Except(

            from i in typeof(string).GetMethods()

            select i.Name

            );

 

var query = (from i in typeof(int).GetMethods()

              select i.Name).Union(

            from i in typeof(string).GetMethods()

            select i.Name

            );

 

var query = (from s in typeof(string).GetMethods()

            join i in typeof(int).GetMethods()

            on s.Name equals i.Name

            select s.Name).Distinct();

 

var query = (from s in typeof(string).GetMethods()

            from i in typeof(int).GetMethods()

            where s.Name == i.Name

            select s.Name).Distinct();

 

7.   LINQ To Relation

a)         LINQ To DataSet

借助于扩展方法 DataTableExtensions.AsEnumerable() 将 DataTable 转换成

EnumerableRowCollection<TRow>:IEnumerable<TRow> 来实现对 LINQ 的支持。

using System.Data;

using System.Data.Common;

using System.Data.SqlClient;

 

DbProviderFactory factory = SqlClientFactory.Instance;

string connStr = ConsoleApplication1.Properties.Settings.Default.NorthwindConnectionString;

 

using (DbConnection conn = factory.CreateConnection())

{

    conn.ConnectionString = connStr;

    conn.Open();

 

    DbCommand cmd = conn.CreateCommand();

    cmd.CommandText = "select * from Customers";

 

    DbDataAdapter ad = factory.CreateDataAdapter();

    ad.SelectCommand = cmd;

 

    DataSet ds = new DataSet();

    ad.Fill(ds);

 

    var table = ds.Tables[0];

    var q = from o in table.AsEnumerable()

            where o.Field<string>("City") == "London"

            select new

            {

                Name = o.Field<string>("ContactName"),

                Phone = o.Field<string>("Phone")

            };

 

    foreach (var u in q)

    {

        Console.WriteLine(u);

    }

}

 

b)        LINQ To SQL (DLINQ)

                        i.              ORM工具

添加一个 “LINQ To SQL Classes”项（扩展名为dbml） 服务管理器中拖拽

                      ii.              DataContext

NorthWindDataContext northwind = new NorthWindDataContext()

var customer = from c in northwind.Customers

               select c;

 

DataContext northwind = new DataContext(connString);

Table<Customer> customers = northwind.GetTable<Customer>();

var query = from c in customers select c;

 

                    iii.              LOG

northwind.Log = Console.Out;

 

                        i.              简单查询

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

 

    var query = from c in northwind.Customers

                orderby c.ContactName

                select c;

 

    foreach (var c in query)

    {

        Console.WriteLine("{0} {1}", c.ContactName, c.ContactTitle);

    }

}

 

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

    string city = "London";

    var query = from c in northwind.Customers

                where c.City == city

                orderby c.ContactName

                select c;

 

    foreach (var c in query)

    {

        Console.WriteLine("{0} {1}", c.ContactName, c.City);

    }

}

 

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

    string city = "London";

    var query = from c in northwind.Customers

                where c.City == city

                orderby c.ContactName

                select c;

    city = "Paris";

 

    foreach (var c in query)

    {

        Console.WriteLine("{0} {1}", c.ContactName, c.City);

    }

}

                      ii.              One to One /One to Many / Many to Many

var p = (from c in northwind.Categories

        where c.CategoryName == "Meat/Poultry"

        select c).First().Products;

 

var c = (from p in northwind.Products

        where p.CategoryID == 2

        select p).First().Category;

 

var q = from c in northwind.Categories

        from p in northwind.Products

        where c.CategoryName == "Meat/Poultry" && c.CategoryID == p.CategoryID

        select new { c.CategoryName, p.ProductName };

 

var q = from c in northwind.Categories

        where c.CategoryName == "Meat/Poultry"

        join p in northwind.Products

        on c.CategoryID equals p.CategoryID

        select new { c.CategoryName, p.ProductName };

 

                    iii.              增删改

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

 

    Product p = new Product { ProductName = "MyProduct" };

    northwind.Products.Add(p);

 

    northwind.SubmitChanges();

 

    Console.WriteLine(p.ProductID);

}

 

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

 

    Product product = (from p in northwind.Products

                where p.ProductName == "MyProduct"

                select p).First();

 

    northwind.Products.Remove(product);

 

    northwind.SubmitChanges();

}

 

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

   

    var query = (from p in northwind.Products

                where p.ProductID == 5

                select p).First();

 

    query.QuantityPerUnit = "48 boxes";

 

    northwind.SubmitChanges();

}

 

                     iv.              事务

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Connection.Open();

    northwind.Transaction = northwind.Connection.BeginTransaction();

    try

    {

        Product p = new Product { ProductName = "MyProduct2" };

        northwind.Products.Add(p);

        northwind.SubmitChanges();

 

        northwind.Transaction.Commit();

    }

    catch(Exception ex)

    {

        northwind.Transaction.Rollback();

        Console.WriteLine(ex.Message);

    }

}

                       v.              分页

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

 

    int pageIndex = 4;

    int pageSize = 10;

 

    int skipCount = (pageIndex - 1) * pageSize;

 

    var query = from c in northwind.Customers

                orderby c.ContactName

                select c;

 

    Console.WriteLine("Page {0}", pageSize);

    foreach (var c in query.Skip(skipCount).Take(pageSize))

    {

        Console.WriteLine("Name:{0} Phone:{1}", c.ContactName, c.Phone);

    }

}

 

SELECT TOP 10 [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName],

[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],

[t0].[Country], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE NOT (EXISTS(

    SELECT NULL AS [EMPTY]

    FROM (

        SELECT TOP 30 [t1].[CustomerID]

        FROM [dbo].[Customers] AS [t1]

        ORDER BY [t1].[ContactName]

        ) AS [t2]

    WHERE [t0].[CustomerID] = [t2].[CustomerID]

    ))

ORDER BY [t0].[ContactName]

-- Context: SqlProvider(Sql2000) Model: AttributedMetaModel Build: 3.5.20706.1

 

SELECT TOP 10 [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName],

[t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode],

[t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ContactName]) AS [ROW_NUMBER], [t0]

.[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0]

.[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].

[Phone], [t0].[Fax]

    FROM [dbo].[Customers] AS [t0]

    ) AS [t1]

WHERE [t1].[ROW_NUMBER] > @p0

ORDER BY [t1].[ContactName]

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [30]

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

 

                     vi.              ExecuteCommand、ExecuteQuery

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

    northwind.ExecuteCommand(

                "Update Employees Set Country = {0} where title = {1}",

                "USA",

                "Sales Manager"

                );

}

 

using (NorthWindDataContext northwind = new NorthWindDataContext())

{

    northwind.Log = Console.Out;

    var q = northwind.ExecuteQuery<Employee>("select * from [Employees]");

 

    foreach (var e in q)

    {

        Console.WriteLine(e.LastName);

    }

}

8.   LINQ To XML

a)         查询

var doc = XDocument.Load("NorthWindMapping.xml");

 

var query = from t in doc.Elements("Database").Elements("Table")

            where t.Attribute("Name").Value.StartsWith("C")

            select t;

 

foreach (var i in query)

{

    Console.WriteLine(i.Attribute("Name").Value);

}

 

// 从XML字符串生成XDocument对象

var doc = XDocument.Parse(

  @"<people>

    <person>

      <id>1</id>

      <name>user1</name>

      <age>1</age>

    </person>

  </people>");

b)        创建

var doc = new XDocument(

  new XDeclaration("1.0", "utf-8", "yes"),

  new XComment("test xml"),

 

  new XElement("Customers",

    new XElement("Customer",

      new XAttribute("title", "CEO"),

      new XElement("id", 1),

      new XElement("name", "张三"),

      new XElement("age", 36),

      new XElement("email", new XCData("<a href=\"mailto:zhangsan@XXX.com\">邮件</a>"))

    ),

 

    new XElement("Customer",

      new XElement("id", 2),

      new XElement("name", "李四"),

      new XElement("age", 28)

    )

  )

);

c)         更改

var doc = XDocument.Load("test.xml");

var query = from c in doc.Elements("Customers").Elements("Customer")

            where c.Attribute("title") != null && c.Attribute("title").Value == "CEO"

            select c;

 

foreach (var e in query)

{

    e.SetAttributeValue("title", "CEO,CTO");

}

 

var query2 = from c in doc.Elements("Customers").Elements("Customer")

             where Convert.ToInt16(c.Element("id").Value) == 2

        select c;

 

foreach (var e in query2)

{

    e.Remove();

}

 

doc.Save("test.xml");

 

9.   LINQ的性能

using System.Data;

using System.Data.Linq;

using System.Data.SqlClient;

using System.Diagnostics;

 

static public string UsingSqlReader()

{

    Stopwatch watch = new Stopwatch();

    watch.Start();

 

    using (SqlConnection conn = new SqlConnection(connString))

    {

        if (conn.State != System.Data.ConnectionState.Open)

            conn.Open();

        for (int i = 1; i <= 1000; i++)

        {

            SqlCommand cmd = new SqlCommand(

                "SELECT ProductID,ProductName FROM Products WHERE ProductID=" + i.ToString(), conn);

            cmd.CommandType = CommandType.Text;

            using (SqlDataReader reader = cmd.ExecuteReader())

            {

                if (reader.Read())

                {

                    Product p = new Product();

                    p.ProductID = (int)reader["ProductID"];

                    p.ProductName = (string)reader["ProductName"];

                }

            }

 

        }

    }

 

    watch.Stop();

 

    return "[UsingSqlReader] total time: " + watch.Elapsed.ToString();

}

 

static public string UsingLinqToSQL()

{

    Stopwatch watch = new Stopwatch();

    watch.Start();

 

    NorthWindDataContext northwind = new NorthWindDataContext();

 

    for (int i = 1; i <= 1000; i++)

    {

        Product product = northwind.Products.SingleOrDefault(p => p.ProductID == i);

    }

 

    watch.Stop();

 

    return "[UsingLinqToSQL] total time: " + watch.Elapsed.ToString();

}

 

static public string UsingOptimizeLinqToSQL()

{

    Stopwatch watch = new Stopwatch();

    watch.Start();

 

    using (SqlConnection conn = new SqlConnection(connString))

    {

        if (conn.State != System.Data.ConnectionState.Open)

            conn.Open();

 

        NorthWindDataContext northwind = new NorthWindDataContext(conn);

 

        for (int i = 1; i <= 1000; i++)

        {

            Product product = northwind.Products.SingleOrDefault(p => p.ProductID == i);

        }

    }

    watch.Stop();

 

    return "[UsingOptimizedLinqToSQL] total time: " + watch.Elapsed.ToString();

 

}

 

static public string UsingCompiledLinqToSQL()

{

    Stopwatch watch = new Stopwatch();

    watch.Start();

 

    var query = CompiledQuery.Compile(

            (NorthWindDataContext northwind, int index)

            => northwind.Products.SingleOrDefault(p => p.ProductID == index));

 

    using (SqlConnection conn = new SqlConnection(connString))

    {

        if (conn.State != System.Data.ConnectionState.Open)

            conn.Open();

 

        NorthWindDataContext northwind = new NorthWindDataContext(conn);

 

        for (int i = 1; i <= 1000; i++)

        {

            Product p = query(northwind, i);

        }

    }

    watch.Stop();

 

    return "[UsingCompiledLinqToSQL] total time: " + watch.Elapsed.ToString();

 

}

 

Console.WriteLine(UsingSqlReader());

Console.WriteLine(UsingLinqToSQL());

Console.WriteLine(UsingOptimizeLinqToSQL());

Console.WriteLine(UsingCompiledLinqToSQL());

 

Console.ReadLine();