一步一步学Linq to sql（一）

2009-05-19 作者：lovecherry 来源：lovecherry的blog

（一）：预备知识

什么是Linq to sql

Linq to sql（或者叫DLINQ）是LINQ（.NET语言集成查询）的一部分，全称基于关系数据的 .NET 语言集成查询，用于以对象形式管理关系数据，并提供了丰富的查询功能，它和Linq to xml、Linq to objects、Linq to dataset、Linq to entities等组成了强大的LINQ。

要学好LINQ查询语法，就不得不先理解C# 3.0的一些新特性，下面一一简单介绍。

隐含类型局部变量

var age = 26;

var username = "zhuye";

var userlist = new [] {"a","b","c"};

foreach(var user in userlist)

Console.WriteLine(user);

纯粹给懒人用的var关键字，告诉编译器（对于CLR来说，它是不会知道你是否使用了var，苦力是编译器出的），你自己推断它的类型吧，我不管了。但是既然让编译器推断类型就必须声明的时候赋值，而且不能是null值。注意，这只能用于局部变量，用于字段是不可以的。

匿名类型

var data = new {username = "zhuye",age = 26};

Console.WriteLine("username:{0} age:{1}", data.username, data.age);

匿名类型允许开发人员定义行内类型，无须显式定义类型。常和var配合使用，var用于声明匿名类型。定义一个临时的匿名类型在LINQ查询句法中非常常见，我们可以很方便的实现对象的转换和投影。

扩展方法

public static class helper

{

public static string MD5Hash(this string s)

{

return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(s,"MD5");

}

public static bool In(this object o, IEnumerable b)

{

foreach(object obj in b)

{

if(obj==o)

return true;

}

return false;

}

// 调用扩展方法

Console.WriteLine("123456".MD5Hash());

Console.WriteLine("1".In(new[]{"1","2","3"}));

很多时候我们需要对CLR类型进行一些操作，苦于无法扩展CLR类型的方法，只能创建一些helper方法，或者生成子类。扩展方法使得这些需求得意实现，同时也是实现LINQ的基础。定义扩展方法需要注意，只能在静态类中定义并且是静态方法，如果扩展方法名和原有方法名发生冲突，那么扩展方法将失效。

自动属性

public class Person

{

public string username { get; protected set; }

public int age { get; set; }

public Person()

{

this.username = "zhuye";

}

Person p = new Person();

//p.username = "aa";

Console.WriteLine(p.username);

意义不是很大，纯粹解决机械劳动。编译器自动为你生成get、set操作以及字段，并且你不能使用字段也不能自定义get、set操作，不过你可以分别定义get和set的访问级别。

对象初始化器

public class Person

{

public string username { get; set; }

public int age { get; set; }

public override string ToString()

{

return string.Format("username:{0} age:{1}", this.username, this.age);

}

Person p = new Person() {username = "zhuye", age=26};

Console.WriteLine(p.ToString());

编译器会自动为你做setter操作，使得原本几行的属性赋值操作能在一行中完成。这里需要注意：

允许只给一部分属性赋值，包括internal访问级别
可以结合构造函数一起使用，并且构造函数初始化先于对象初始化器执行

集合初始化器

public class Person

{

public string username { get; set; }

public int age { get; set; }

public override string ToString()

{

return string.Format("username:{0} age:{1}", this.username, this.age);

}

var persons = new List<Person> {

new Person {username = "a", age=1},

new Person {username = "b", age=2}};

foreach(var p in persons)

Console.WriteLine(p.ToString());

编译器会自动为你做集合插入操作。如果你为Hashtable初始化的话就相当于使用了两个对象初始化器。

Lambda表达式

var list = new [] { "aa", "bb", "ac" };

var result = Array.FindAll(list, s => (s.IndexOf("a") > -1));

foreach (var v in result)

Console.WriteLine(v);

其实和2.0中的匿名方法差不多，都是用于产生内联方法，只不过Lambda表达式的语法更为简洁。语法如下：

(参数列表) => 表达式或者语句块

其中：

参数个数：可以有多个参数，一个参数，或者无参数。

表达式或者语句块：这部分就是我们平常写函数的实现部分（函数体）。

前面的示例分别是1个参数的例子，下面结合扩展方法来一个复杂的例子：

public delegate int mydg(int a, int b);

public static class LambdaTest

{

public static int oper(this int a, int b, mydg dg)

{

return dg(a, b);

}

Console.WriteLine(1.oper(2, (a, b) => a + b));

Console.WriteLine(2.oper(1, (a, b) => a - b));

查询句法

var persons = new List<Person> {

new Person {username = "a", age=19},

new Person {username = "b", age=20},

new Person {username = "a", age=21},

};

var selectperson = from p in persons where p.age >= 20 select p.username.ToUpper();

foreach(var p in selectperson)

Console.WriteLine(p);

查询句法是使用标准的LINQ查询运算符来表达查询时一个方便的声明式简化写法。该句法能在代码里表达查询时增进可读性和简洁性，读起来容易，也容易让人写对。Visual Studio 对查询句法提供了完整的智能感应和编译时检查支持。编译器在底层把查询句法的表达式翻译成明确的方法调用代码，代码通过新的扩展方法和Lambda表达式语言特性来实现。上面的查询句法等价于下面的代码：

var selectperson = persons.Where(p=>p.age>=20).Select(p=>p.username.ToUpper());

LINQ查询句法可以实现90％以上T-SQL的功能（由于T-SQL是基于二维表的，所以LINQ的查询语法会比T-SQL更简单和灵活），但是由于智能感应的原因，select不能放在一开始就输入。

（二）：DataContext与实体

DataContext

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

DataContext提供了以下一些使用的功能：

以日志形式记录DataContext生成的SQL
执行SQL（包括查询和更新语句）
创建和删除数据库

DataContext是实体和数据库之间的桥梁，那么首先我们需要定义映射到数据表的实体。

定义实体类

using System.Data.Linq.Mapping;

[Table(Name = "Customers")]

public class Customer

{

[Column(IsPrimaryKey = true)]

public string CustomerID {get; set;}

[Column(Name = "ContactName")]

public string Name { get; set; }

[Column]

public string City {get; set;}

}

以Northwind数据库为例，上述Customers类被映射成一个表，对应数据库中的 Customers表。然后在类型中定义了三个属性，对应表中的三个字段。其中，CustomerID字段是主键，如果没有指定Column特性的Name属性，那么系统会把属性名作为数据表的字段名，也就是说实体类的属性名就需要和数据表中的字段名一致。

现在，创建一个ASP.NET页面，然后在页面上加入一个GridView控件，使用下面的代码进行绑定数据：

using System.Data.Linq;

DataContext ctx = new DataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

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

GridView1.DataSource = from c in Customers where c.CustomerID.StartsWith("A") select new {顾客ID=c.CustomerID, 顾客名=c.Name, 城市=c.City};

GridView1.DataBind();

使用DataContext类型把实体类和数据库中的数据进行关联。你可以直接在DataContext的构造方法中定义连接字符串，也可以使用IDbConnection：

using System.Data.SqlClient;

IDbConnection conn = new SqlConnection("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

DataContext ctx = new DataContext(conn);

之后，通过GetTable获取表示底层数据表的Table类型，显然，数据库中的Customers表的实体是Customer类型。随后的查询句法，即使你不懂SQL应该也能看明白。从Customers表中找出CustomerID以“A”开头的记录，并把CustomersID、Name以及City封装成新的匿名类型进行返回。

结果如下图：

强类型DataContext

public partial class NorthwindDataContext : DataContext

{

public Table<Customer> Customers;

public NorthwindDataContext(IDbConnection connection) : base(connection) { }

public NorthwindDataContext(string connection) : base(connection) { }

}

强类型数据上下文使代码更简洁：

NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };

GridView1.DataBind();

DataContext其实封装了很多实用的功能，下面一一介绍。

日志功能

using System.IO;

NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true); // Append

ctx.Log = sw;

GridView1.DataSource = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };

GridView1.DataBind();

sw.Close();

运行程序后在网站所在目录生成了log.txt，每次查询都会把诸如下面的日志追加到文本文件中：

SELECT [t0].[CustomerID], [t0].[ContactName], [t0].[City]

FROM [Customers] AS [t0]

WHERE [t0].[CustomerID] LIKE @p0

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

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

应该说这样的日志对于调试程序是非常有帮助的。

探究查询

using System.Data.Common;

using System.Collections.Generic;

NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

var select = from c in ctx.Customers where c.CustomerID.StartsWith("A") select new { 顾客ID = c.CustomerID, 顾客名 = c.Name, 城市 = c.City };

DbCommand cmd = ctx.GetCommand(select);

Response.Write(cmd.CommandText + "<br/>");

foreach (DbParameter parm in cmd.Parameters)

Response.Write(string.Format("参数名:{0},参数值:{1}<br/>", parm.ParameterName, parm.Value));

Customer customer = ctx.Customers.First();

customer.Name = "zhuye";

IList<object> queryText = ctx.GetChangeSet().ModifiedEntities;

Response.Write(((Customer)queryText[0]).Name);

在这里，我们通过DataContext的GetCommand方法获取了查询对应的DbCommand，并且输出了CommandText和所有的DbParameter。之后，我们又通过GetChangeSet方法获取了修改后的实体，并输出了修改内容。

执行查询

NorthwindDataContext ctx = new NorthwindDataContext("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

string newcity = "Shanghai";

ctx.ExecuteCommand("update Customers set City={0} where CustomerID like 'A%'", newcity);

IEnumerable<Customer> customers = ctx.ExecuteQuery<Customer>("select * from Customers where CustomerID like 'A%'");

GridView1.DataSource = customers;

GridView1.DataBind();

前一篇文章已经说了，虽然Linq to sql能实现90％以上的TSQL功能。但是不可否认，对于复杂的查询，使用TSQL能获得更好的效率。因此，DataContext类型也提供了执行SQL语句的能力。代码的执行结果如下图：

创建数据库

testContext ctx = new testContext("server=xxx;database=testdb;uid=xxx;pwd=xxx");

ctx.CreateDatabase();

[Table(Name = "test")]

public class test

{

[Column(IsPrimaryKey = true, IsDbGenerated = true)]

public int ID { get; set; }

[Column(DbType="varchar(20)")]

public string Name { get; set; }

}

public partial class testContext : DataContext

{

public Table<test> test;

public testContext(string connection) : base(connection) { }

}

这段代码在数据库中创建了名为testdb的数据库，等同于下面的脚本：

CREATE TABLE [dbo].[test](

[ID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](20) COLLATE Chinese_PRC_CI_AS NULL,

CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

同时，DataContext还提供了DeleteDatabase()方法，在这里就不列举了。

使用DbDataReader数据源

using System.Data.SqlClient;

var conn = new SqlConnection("server=xxx;database=Northwind;uid=xxx;pwd=xxx");

var ctx = new DataContext(conn);

var cmd = new SqlCommand("select * from customers where CustomerID like 'A%'", conn);

conn.Open();

var reader = cmd.ExecuteReader();

GridView1.DataSource = ctx.Translate<Customer>(reader);

GridView1.DataBind();

conn.Close();

你同样可以选择使用DataReader获取数据，增加了灵活性的同时也增加了性能。

看到这里，你可能会觉得手工定义和数据库中表对应的实体类很麻烦，不用担心，VS2008提供了自动生成实体类以及关系的工具，工具的使用将在以后讲解。今天就讲到这里，和DataContext相关的事务、加载选项、并发选项以及关系实体等高级内容也将在以后讲解。

（三）：增删改

示例数据库

字段名	字段类型	允许空	字段说明
ID	uniqueidentifier		表主键字段
UserName	varchar(50)		留言用户名
PostTime	datetime		留言时间
Message	varchar(400)	√	留言内容
IsReplied	bit		留言是否回复
Reply	varchar(400)	√	留言管理员回复

在数据库中创建一个名为GuestBook的数据库，在里面创建一个tbGuestBook的表，结构如上表。

生成实体类

右键点击网站项目，选择添加新项，然后选择“Linq to sql Classes”，命名为GuestBook。然后打开App_Code里面的GuestBook.dbml。设计视图上的文字提示你可以从服务器资源管理器或者攻击箱拖动项到设计界面上来创建实体类。

那么，我们就在服务器资源管理器中创建一个指向GuestBook数据库的数据连接，然后把tbGuestBook表拖动到GuestBook.dbml的设计视图上，按CTRL+S保存。打开GuestBook.designer.cs可以发现系统自动创建了GuestBook数据库中tbGuestBook表的映射，如下图：

简易留言簿

现在，我们就可以使用Linq to sql完成简易留言簿了。实现以下功能：

发表留言（增）
查看留言（查）
管理员回复留言（改）
管理员删除留言（删除）

首先，创建一个Default.aspx，在页面上加入一些控件：

<div>

姓名

<asp:TextBox ID="tb_UserName" runat="server"></asp:TextBox><br />

<br />

留言

<asp:TextBox ID="tb_Message" runat="server" Height="100px" TextMode="MultiLine" Width="300px"></asp:TextBox><br />

<br />

<asp:Button ID="btn_SendMessage" runat="server" Text="发表留言" OnClick="btn_SendMessage_Click" /><br />

<br />

<asp:Repeater ID="rpt_Message" runat="server">

<tr>

<%# Eval("Message")%>

</td>

<%# Eval("PostTime")%> - <%# Eval("UserName")%>

</td>

</tr>

<tr>

管理员回复：<%# Eval("IsReplied").ToString() == "False" ? "暂无" : Eval("Reply")%>

</td>

</tr>

</table>

<br/>

</ItemTemplate>

</asp:Repeater>

</div>

你可能很难想象，使用Linq to sql进行数据访问会是这么简单，后台代码：

public partial class _Default : System.Web.UI.Page

{

GuestBookDataContext ctx = new GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

SetBind();

}

protected void btn_SendMessage_Click(object sender, EventArgs e)

{

tbGuestBook gb = new tbGuestBook();

gb.ID = Guid.NewGuid();

gb.UserName = tb_UserName.Text;

gb.Message = tb_Message.Text;

gb.IsReplied = false;

gb.PostTime = DateTime.Now;

ctx.tbGuestBooks.Add(gb);

ctx.SubmitChanges();

SetBind();

}

private void SetBind()

{

rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime descending select gb;

rpt_Message.DataBind();

}

前面创建Linq to sql Classes的时候我们输入名字GuestBook，系统就为我们自动创建了GuestBookDataContext（你也可以在GuestBook.Designer.cs中找到类定义）。在绑定的时候我们使用查询句法查询留言表中所有留言，按照发表时间倒序（天哪？这是数据访问吗？好像仅仅定义了一句SQL啊）。在发表留言按钮中，我们为一个tbGuestBook赋值，然后把它加入留言表，再提交更改，就这样完成了记录的插入。

运行效果如下图：

然后，再创建一个Admin.aspx，前台代码如下：

<div>

<asp:Repeater ID="rpt_Message" runat="server" OnItemCommand="rpt_Message_ItemCommand">

<tr>

<%# Eval("Message")%>

</td>

<%# Eval("PostTime")%> - <%# Eval("UserName")%>

</td>

</tr>

<tr>

<asp:Button ID="btn_DeleteMessage" runat="server" Text="删除留言" CommandName="DeleteMessage" CommandArgument='<%# Eval("ID")%>'/>

管理员回复：<asp:TextBox runat="server" ID="tb_Reply" TextMode="MultiLine" Width="300px" Text='<%# Eval("Reply")%>'/>

<asp:Button ID="btn_SendReply" runat="server" Text="发表回复" CommandName="SendReply" CommandArgument='<%# Eval("ID")%>'/>

</td>

</tr>

</table>

<br/>

</ItemTemplate>

</asp:Repeater>

</div>

后台代码：

public partial class Admin : System.Web.UI.Page

{

GuestBookDataContext ctx = new GuestBookDataContext("server=xxx;database=GuestBook;uid=xxx;pwd=xxx");

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

SetBind();

}

private void SetBind()

{

rpt_Message.DataSource = from gb in ctx.tbGuestBooks orderby gb.PostTime descending select gb;

rpt_Message.DataBind();

}

protected void rpt_Message_ItemCommand(object source, RepeaterCommandEventArgs e)

{

if (e.CommandName == "DeleteMessage")

{

StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);

ctx.Log = sw;

tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new Guid(e.CommandArgument.ToString()));

ctx.tbGuestBooks.Remove(gb);

ctx.SubmitChanges();

SetBind();

sw.Close();

}

if (e.CommandName == "SendReply")

{

StreamWriter sw = new StreamWriter(Server.MapPath("log.txt"), true);

ctx.Log = sw;

tbGuestBook gb = ctx.tbGuestBooks.Single(b => b.ID == new Guid(e.CommandArgument.ToString()));

gb.Reply = ((TextBox)e.Item.FindControl("tb_Reply")).Text;

gb.IsReplied = true;

ctx.SubmitChanges();

SetBind();

sw.Close();

}

运行效果如下图：

在这里，我们通过Single方法获取一条记录，也就是一个tbGuestBook实例，更新了一些属性后保存也就完成了改这个操作。删除操作更简单，只需要从表中移除对象。你是不是觉得好像不是在操作数据库，像在操作内存中的对象。

由于写了日志，看看改和删操作会是怎么样的SQL？

UPDATE [dbo].[tbGuestBook]

SET [IsReplied] = @p4, [Reply] = @p5

WHERE ([ID] = @p0) AND ([UserName] = @p1) AND ([PostTime] = @p2) AND ([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND ([Reply] IS NULL)

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [00000000-0000-0000-0000-000000000000]

-- @p1: Input String (Size = 4; Prec = 0; Scale = 0) [ghgh]

-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 10:20:09]

-- @p3: Input String (Size = 3; Prec = 0; Scale = 0) [ghj]

-- @p4: Input Boolean (Size = 0; Prec = 0; Scale = 0) [True]

-- @p5: Input String (Size = 3; Prec = 0; Scale = 0) [qqq]

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

DELETE FROM [dbo].[tbGuestBook] WHERE ([ID] = @p0) AND ([UserName] = @p1) AND ([PostTime] = @p2) AND ([Message] = @p3) AND (NOT ([IsReplied] = 1)) AND ([Reply] = @p4)

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [158ec941-13ff-4093-bd8b-9fceae152171]

-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [44]

-- @p2: Input DateTime (Size = 0; Prec = 0; Scale = 0) [2007-8-16 9:56:19]

-- @p3: Input String (Size = 2; Prec = 0; Scale = 0) [44]

-- @p4: Input String (Size = 3; Prec = 0; Scale = 0) [222]

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

（四）：查询句法

select

描述：查询顾客的公司名、地址信息

查询句法：

var 构建匿名类型1 = from c in ctx.Customers

select new

{

公司名 = c.CompanyName,

地址 = c.Address

};

对应SQL：

SELECT [t0].[CompanyName], [t0].[Address]

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

描述：查询职员的姓名和雇用年份

查询句法：

var 构建匿名类型2 = from emp in ctx.Employees

select new

{

姓名 = emp.LastName + emp.FirstName,

雇用年 = emp.HireDate.Value.Year

};

对应SQL：

SELECT [t0].[LastName] + [t0].[FirstName] AS [value], DATEPART(Year, [t0].[HireDate]) AS [value2]

FROM [dbo].[Employees] AS [t0]

描述：查询顾客的ID以及联系信息（职位和联系人）

查询句法：

var 构建匿名类型3 = from c in ctx.Customers

select new

{

ID = c.CustomerID,

联系信息 = new

{

职位 = c.ContactTitle,

联系人 = c.ContactName

}

};

对应SQL：

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

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

描述：查询订单号和订单是否超重的信息

查询句法：

var select带条件 = from o in ctx.Orders

select new

{

订单号 = o.OrderID,

是否超重 = o.Freight > 100 ? "是" : "否"

};

对应SQL：

SELECT [t0].[OrderID],

(CASE

WHEN [t0].[Freight] > @p0 THEN @p1

ELSE @p2

END) AS [value]

FROM [dbo].[Orders] AS [t0]

-- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [100]

-- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [是]

-- @p2: Input String (Size = 1; Prec = 0; Scale = 0) [否]

where

描述：查询顾客的国家、城市和订单数信息，要求国家是法国并且订单数大于5

查询句法：

var 多条件 = from c in ctx.Customers

where c.Country == "France" && c.Orders.Count > 5

select new

{

国家 = c.Country,

城市 = c.City,

订单数 = c.Orders.Count

};

对应SQL：

SELECT [t0].[Country], [t0].[City], (

SELECT COUNT(*)

FROM [dbo].[Orders] AS [t2]

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

) AS [value]

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

WHERE ([t0].[Country] = @p0) AND (((

SELECT COUNT(*)

FROM [dbo].[Orders] AS [t1]

WHERE [t1].[CustomerID] = [t0].[CustomerID]

)) > @p1)

-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [France]

-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]

orderby

描述：查询所有没有下属雇员的雇用年和名，按照雇用年倒序，按照名正序

查询句法：

var 排序 = from emp in ctx.Employees

where emp.Employees.Count == 0

orderby emp.HireDate.Value.Year descending, emp.FirstName ascending

select new

{

雇用年 = emp.HireDate.Value.Year,

名 = emp.FirstName

};

对应SQL：

SELECT DATEPART(Year, [t0].[HireDate]) AS [value], [t0].[FirstName]

FROM [dbo].[Employees] AS [t0]

WHERE ((

SELECT COUNT(*)

FROM [dbo].[Employees] AS [t1]

WHERE [t1].[ReportsTo] = [t0].[EmployeeID]

)) = @p0

ORDER BY DATEPART(Year, [t0].[HireDate]) DESC, [t0].[FirstName]

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

分页

描述：按照每页10条记录，查询第二页的顾客

查询句法：

var 分页 = (from c in ctx.Customers select c).Skip(10).Take(10);

对应SQL：

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].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) 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

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

分组

描述：根据顾客的国家分组，查询顾客数大于5的国家名和顾客数

查询句法：

var 一般分组 = from c in ctx.Customers

group c by c.Country into g

where g.Count() > 5

orderby g.Count() descending

select new

{

国家 = g.Key,

顾客数 = g.Count()

};

对应SQL：

SELECT [t1].[Country], [t1].[value3] AS [顾客数]

FROM (

SELECT COUNT(*) AS [value], COUNT(*) AS [value2], COUNT(*) AS [value3], [t0].[Country]

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

GROUP BY [t0].[Country]

) AS [t1]

WHERE [t1].[value] > @p0

ORDER BY [t1].[value2] DESC

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

描述：根据国家和城市分组，查询顾客覆盖的国家和城市

查询句法：

var 匿名类型分组 = from c in ctx.Customers

group c by new { c.City, c.Country } into g

orderby g.Key.Country, g.Key.City

select new

{

国家 = g.Key.Country,

城市 = g.Key.City

};

对应SQL：

SELECT [t1].[Country], [t1].[City]

FROM (

SELECT [t0].[City], [t0].[Country]

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

GROUP BY [t0].[City], [t0].[Country]

) AS [t1]

ORDER BY [t1].[Country], [t1].[City]

描述：按照是否超重条件分组，分别查询订单数量

查询句法：

var 按照条件分组 = from o in ctx.Orders

group o by new { 条件 = o.Freight > 100 } into g

select new

{

数量 = g.Count(),

是否超重 = g.Key.条件 ? "是" : "否"

};

对应SQL：

SELECT

(CASE

WHEN [t2].[value2] = 1 THEN @p1

ELSE @p2

END) AS [value], [t2].[value] AS [数量]

FROM (

SELECT COUNT(*) AS [value], [t1].[value] AS [value2]

FROM (

SELECT

(CASE

WHEN [t0].[Freight] > @p0 THEN 1

WHEN NOT ([t0].[Freight] > @p0) THEN 0

ELSE NULL

END) AS [value]

FROM [dbo].[Orders] AS [t0]

) AS [t1]

GROUP BY [t1].[value]

) AS [t2]

-- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [100]

-- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [是]

-- @p2: Input String (Size = 1; Prec = 0; Scale = 0) [否]

distinct

描述：查询顾客覆盖的国家

查询句法：

var 过滤相同项 = (from c in ctx.Customers orderby c.Country select c.Country).Distinct();

对应SQL：

SELECT DISTINCT [t0].[Country]

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

union

描述：查询城市是A打头和城市包含A的顾客并按照顾客名字排序

查询句法：

var 连接并且过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Union

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax]

FROM (

SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax]

FROM (

SELECT [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 [t0].[City] LIKE @p0

UNION

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

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

WHERE [t1].[ContactName] LIKE @p1

) AS [t2]

) AS [t3]

ORDER BY [t3].[ContactName]

-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

concat

描述：查询城市是A打头和城市包含A的顾客并按照顾客名字排序，相同的顾客信息不会过滤

查询句法：

var 连接并且不过滤相同项 = (from c in ctx.Customers where c.City.Contains("A") select c).Concat

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t3].[CustomerID], [t3].[CompanyName], [t3].[ContactName], [t3].[ContactTitle], [t3].[Address], [t3].[City], [t3].[Region], [t3].[PostalCode], [t3].[Country], [t3].[Phone], [t3].[Fax]

FROM (

SELECT [t2].[CustomerID], [t2].[CompanyName], [t2].[ContactName], [t2].[ContactTitle], [t2].[Address], [t2].[City], [t2].[Region], [t2].[PostalCode], [t2].[Country], [t2].[Phone], [t2].[Fax]

FROM (

SELECT [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 [t0].[City] LIKE @p0

UNION ALL

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

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

WHERE [t1].[ContactName] LIKE @p1

) AS [t2]

) AS [t3]

ORDER BY [t3].[ContactName]

-- @p0: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

-- @p1: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

取相交项

描述：查询城市是A打头的顾客和城市包含A的顾客的交集，并按照顾客名字排序

查询句法：

var 取相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Intersect

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

SELECT DISTINCT [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 (EXISTS(

SELECT NULL AS [EMPTY]

FROM [dbo].[Customers] AS [t2]

WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)

)) AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

排除相交项

描述：查询城市包含A的顾客并从中删除城市以A开头的顾客，并按照顾客名字排序

查询句法：

var 排除相交项 = (from c in ctx.Customers where c.City.Contains("A") select c).Except

(from c in ctx.Customers where c.ContactName.StartsWith("A") select c).OrderBy(c => c.ContactName);

对应SQL：

SELECT [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]

FROM (

SELECT DISTINCT [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 (NOT (EXISTS(

SELECT NULL AS [EMPTY]

FROM [dbo].[Customers] AS [t2]

WHERE ([t1].[CustomerID] = [t2].[CustomerID]) AND ([t2].[ContactName] LIKE @p0)

))) AND ([t1].[City] LIKE @p1)

ORDER BY [t1].[ContactName]

-- @p0: Input String (Size = 2; Prec = 0; Scale = 0) [A%]

-- @p1: Input String (Size = 3; Prec = 0; Scale = 0) [%A%]

子查询

描述：查询订单数超过5的顾客信息

查询句法：

var 子查询 = from c in ctx.Customers

where

(from o in ctx.Orders group o by o.CustomerID into o where o.Count() > 5 select o.Key).Contains(c.CustomerID)

select c;

对应SQL：

SELECT [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 EXISTS(

SELECT NULL AS [EMPTY]

FROM (

SELECT COUNT(*) AS [value], [t1].[CustomerID]

FROM [dbo].[Orders] AS [t1]

GROUP BY [t1].[CustomerID]

) AS [t2]

WHERE ([t2].[CustomerID] = [t0].[CustomerID]) AND ([t2].[value] > @p0)

)

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

in操作

描述：查询指定城市中的客户

查询句法：

var in操作 = from c in ctx.Customers

where new string[] { "Brandenburg", "Cowes", "Stavern" }.Contains(c.City)

select c;

对应SQL：

SELECT [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 [t0].[City] IN (@p0, @p1, @p2)

-- @p0: Input String (Size = 11; Prec = 0; Scale = 0) [Brandenburg]

-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [Cowes]

-- @p2: Input String (Size = 7; Prec = 0; Scale = 0) [Stavern]

join

描述：内连接，没有分类的产品查询不到

查询句法：

var innerjoin = from p in ctx.Products

join c in ctx.Categories

on p.CategoryID equals c.CategoryID

select p.ProductName;

对应SQL：

SELECT COUNT(*) AS [value]

FROM [dbo].[Products] AS [t0]

INNER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])

描述：外连接，没有分类的产品也能查询到

查询句法：

var leftjoin = from p in ctx.Products

join c in ctx.Categories

on p.CategoryID equals c.CategoryID

into pro

from x in pro.DefaultIfEmpty()

select p.ProductName;

对应SQL：

SELECT COUNT(*) AS [value]

FROM [dbo].[Products] AS [t0]

LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])

你可能会很奇怪，原先很复杂的SQL使用查询句法会很简单（比如按照条件分组）。但是原先觉得很好理解的SQL使用查询句法会觉得很复杂（比如连接查询）。其实，我们还可以通过其它方式进行连接操作，在以后说DataLoadOptions类型的时候会再说。虽然Linq to sql已经非常智能了，但是对于非常复杂的查询还是建议通过存储过程实现，下次讲解如何调用存储过程。

（五）：存储过程

普通存储过程

首先在查询分析器运行下面的代码来创建一个存储过程：

create proc sp_singleresultset

set nocount on

select * from customers

然后打开IDE的服务器资源管理器，之前我们从表中拖动表到dbml设计视图，这次我们从存储过程中找到刚才创建的存储过程，然后拖动到设计视图。在方法面板中可以看到已经创建了一个sp_singleresultset的方法，如下图：

然后打开Northwind.designer.cs，可以找到下面的代码：

[Function(Name="dbo.sp_singleresultset")]

public ISingleResult<sp_singleresultsetResult> sp_singleresultset()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return ((ISingleResult<sp_singleresultsetResult>)(result.ReturnValue));

}

我们可以发现，IDE为这个存储过程单独生成了返回结果集的实体定义，你可能会觉得很奇怪，IDE怎么知道这个存储过程将会返回哪些数据那？其实，在把存储过程拖拽入dbml设计视图的时候，IDE就执行了类似下面的命令：

SET FMTONLY ON;

exec Northwind.dbo.sp_singleresultset

SET FMTONLY OFF;

这样就可以直接获取存储过程返回的元数据而无须执行存储过程。

其实我们存储过程返回的就是顾客表的数据，如果你觉得为存储过程单独设置结果集实体有些浪费的话可以在存储过程的属性窗口中调整返回类型从“自动生成的类型”到Customer，不过以后你只能通过删除方法面板中的存储过程，然后重新添加来还原到“自动生成的类型”。下面，我们可以写如下的Linq to object代码进行查询：

var 单结果集存储过程 =

from c in ctx.sp_singleresultset()

where c.CustomerID.StartsWith("A")

select c;

在这里确实是Linq to object的，因为查询句法不会被整句翻译成SQL，而是从存储过程的返回对象中再去对对象进行查询。SQL代码如下：

EXEC @RETURN_VALUE = [dbo].[sp_singleresultset]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

带参数的存储过程

创建如下存储过程：

create proc [dbo].[sp_withparameter]

@customerid nchar(5),

@rowcount int output

set nocount on

set @rowcount = (select count(*) from customers where customerid = @customerid)

使用同样的方法生成存储过程方法，然后使用下面的代码进行测试：

int? rowcount = -1;

ctx.sp_withparameter("", ref rowcount);

Response.Write(rowcount);

ctx.sp_withparameter("ALFKI", ref rowcount);

Response.Write(rowcount);

结果输出了“01”。说明ID为“”的顾客数为0，而ID为“ALFKI”的顾客数为1。存储过程的输出参数被封装成了ref参数，对于C#语法来说非常合情合理。SQL代码如下：

EXEC @RETURN_VALUE = [dbo].[sp_withparameter] @customerid = @p0, @rowcount = @p1 OUTPUT

-- @p0: Input StringFixedLength (Size = 5; Prec = 0; Scale = 0) []

-- @p1: InputOutput Int32 (Size = 0; Prec = 0; Scale = 0) [-1]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

带返回值的存储过程

再来创建第三个存储过程：

create proc [dbo].[sp_withreturnvalue]

@customerid nchar(5)

set nocount on

if exists (select 1 from customers where customerid = @customerid)

return 101

else

return 100

生成方法后，可以通过下面的代码进行测试：

Response.Write(ctx.sp_withreturnvalue(""));

Response.Write(ctx.sp_withreturnvalue("ALFKI"));

运行后程序输出“100101”

多结果集的存储过程

再来创建一个多结果集的存储过程：

create proc [dbo].[sp_multiresultset]

set nocount on

select * from customers

select * from employees

找到生成的存储过程方法：

[Function(Name="dbo.sp_multiresultset")]

public ISingleResult<sp_multiresultsetResult> sp_multiresultset()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return ((ISingleResult<sp_multiresultsetResult>)(result.ReturnValue));

}

由于现在的VS2008会把多结果集存储过程识别为单结果集存储过程（只认识第一个结果集），我们只能对存储过程方法多小动手术，修改为：

[Function(Name="dbo.sp_multiresultset")]

[ResultType(typeof(Customer))]

[ResultType(typeof(Employee))]

public IMultipleResults sp_multiresultset()

{

IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));

return (IMultipleResults)(result.ReturnValue);

}

然后使用下面的代码测试：

var 多结果集存储过程 = ctx.sp_multiresultset();

var Customers = 多结果集存储过程.GetResult<Customer>();

var Employees = 多结果集存储过程.GetResult<Employee>();

GridView1.DataSource = from emp in Employees where emp.FirstName.Contains("A") select emp;

GridView1.DataBind();

GridView2.DataSource = from c in Customers where c.CustomerID.StartsWith("A") select c;

GridView2.DataBind();

使用存储过程新增数据

存储过程除了可以直接调用之外，还可以用于实体的增删改操作。还记得在《一步一步学Linq to sql（三）：增删改》中创建的留言簿程序吗？下面我们就来改造这个程序，使用存储过程而不是系统生成的SQL实现实体增删改。首先，我们创建下面的存储过程

create proc sendmessage

@username varchar(50),

@message varchar(500)

insert into tbguestbook

(id,username,posttime,[message],isreplied,reply)

values

(newid(),@username,getdate(),@message,0,'')

然后，打开留言簿dbml，把存储过程从服务器资源管理器拖拽到设计视图上。右键点击tbGuestBook实体类，选择配置行为。如下图，为插入操作选择刚才创建的存储过程方法，并进行参数匹配：

由于我们的存储过程只接受2个参数，相应修改以下创建留言的按钮处理事件：

protected void btn_SendMessage_Click(object sender, EventArgs e)

{

tbGuestBook gb = new tbGuestBook();

gb.UserName = tb_UserName.Text;

gb.Message = tb_Message.Text;

ctx.tbGuestBooks.Add(gb);

ctx.SubmitChanges();

SetBind();

}

运行程序后可以发现，在提交修改的时候调用了下面的SQL：

EXEC @RETURN_VALUE = [dbo].[sendmessage] @username = @p0, @message = @p1

-- @p0: Input AnsiString (Size = 5; Prec = 0; Scale = 0) [zhuye]

-- @p1: Input AnsiString (Size = 11; Prec = 0; Scale = 0) [new message]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

使用存储过程删除数据

创建如下存储过程：

create proc delmessage

@id uniqueidentifier

delete tbguestbook where id=@id

按照前面的步骤生成存储过程方法，并为删除操作执行这个存储过程方法。在选择参数的时候我们可以看到，ID分当前值和原始值，我们选择当前值即可，如下图：

无须改动任何逻辑代码，进行删除留言操作后可以跟踪到下面的SQL：

EXEC @RETURN_VALUE = [dbo].[delmessage] @id = @p0

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [9e3c5ee3-2575-458e-899d-4b0bf73e0849]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

使用存储过程更改数据

创建如下存储过程：

create proc replymessage

@id uniqueidentifier,

@reply varchar(500)

update tbguestbook set reply=@reply,isreplied=1 where id=@id

由于更新的时候并不会更新主键，所以我们可以为两个参数都指定当前值。回复留言后可以跟踪到下面的SQL：

EXEC @RETURN_VALUE = [dbo].[replymessage] @id = @p0, @reply = @p1

-- @p0: Input Guid (Size = 0; Prec = 0; Scale = 0) [67a69d0f-a88b-4b22-8939-fed021eb1cb5]

-- @p1: Input AnsiString (Size = 6; Prec = 0; Scale = 0) [464456]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

假设有这样一种应用，我们需要修改留言簿中不合法的用户名：

create proc modiusername

@oldusername varchar(50),

@newusername varchar(50)

update tbguestbook set username=@newusername where username = @oldusername

有个网友起名叫“admin”，我们要把所有这个名字修改为“notadmin”。那么，可以如下图设置update操作：

然后运行下面的测试代码：

var messages = from gb in ctx.tbGuestBooks

select gb;

foreach (var gb in messages)

{

if(gb.UserName == "admin")

gb.UserName = "notadmin";

}

运行程序后能跟踪到下面的SQL：

SELECT [t0].[ID], [t0].[UserName], [t0].[PostTime], [t0].[Message], [t0].[IsReplied], [t0].[Reply]

FROM [dbo].[tbGuestBook] AS [t0]

EXEC @RETURN_VALUE = [dbo].[modiusername] @oldusername = @p0, @newusername = @p1

-- @p0: Input AnsiString (Size = 5; Prec = 0; Scale = 0) [admin]

-- @p1: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [notadmin]

-- @RETURN_VALUE: Output Int32 (Size = 0; Prec = 0; Scale = 0) []

| 联系我们 | 招聘信息 | 火龙果软件 |

火龙果软件/UML软件工程组织致力于提高您的软件工程实践能力，我们不断地吸取业界的宝贵经验，向您提供经过数百家企业验证的有效的工程技术实践经验，同时关注最新的理论进展，帮助您“领跑您所在行业的软件世界”。