(翻译) LINQ to SQL (Part 3 - 查询数据库)

Last month I started a blog post series covering LINQ to SQL. LINQ to SQL is a built-in O/RM (object relational mapping) framework that ships in the .NET Framework 3.5 release, and which enables you to easily model relational databases using .NET classes. You can then use LINQ expressions to query the database with them, as well as update/insert/delete data from it.

Below are the first two parts of my LINQ to SQL series:

Part 1: LINQ to SQL概述

Part 2: 定义数据模型类

In today's blog post I'll be going into more detail on how to use the data model we created in the Part 2 post, and show how to use it to query data within an ASP.NET project.

上个月我开始写关于LINQ to SQL系列的文章。LINQ to SQL.是一个.NET 3.5版本内建的对象关系映射框架，可以很方便的使用.NET 类为关系数据库建模。然后通过LINQ表达式来查询/更新/插入/删除数据。

以下是此系列文章的前两部分：

Part 1: LINQ to SQL概述

Part 2: 定义数据模型类

在今天的文章里，我将更详细的描述如何使用我们在之前部分创建的数据模型以及如何在ASP.NET项目中实现数据查询。

Northwind Database Modeled using LINQ to SQL

In Part 2 of this series I walked through how to create a LINQ to SQL class model using the LINQ to SQL designer that is built-into VS 2008. Below is the class model that we created for the Northwind sample database:

使用LINQ to SQL模型化的Northwind数据库

在此系列第二部分介绍了如何通过VS2008 集成的LINQ to SQL设计器来创建LINQ to SQL 类模型. 以下是我们对Northwind数据库创建的类模型:

Retrieving Products

Once we have defined our data model classes above, we can easily query and retrieve data from our database. LINQ to SQL enables you to do this by writing LINQ syntax queries against the NorthwindDataContext class that we created using the LINQ to SQL designer above.

For example, to retrieve and iterate over a sequence of Product objects I could write code like below:

获取产品

在定义以上数据模型类之后，我们就可以很容易的从数据库查询获取数据。针对刚才由 LINQ to SQL设计器创建的NorthwindDataContext 类，编写一些LINQ 查询语句就可以实现数据获取的操作。

例如，我写如下代码就可以实现获取及遍历一个序列的产品对象。

In the query above I have used a "where" clause in my LINQ syntax query to only return those products within a specific category. I am using the CategoryID of the Product to perform the filter.

One of the nice things above LINQ to SQL is that I have a lot of flexibility in how I query my data, and I can take advantage of the associations I've setup when modeling my LINQ to SQL data classes to perform richer and more natural queries against the database. For example, I could modify the query to filter by the product's CategoryName instead of its CategoryID by writing my LINQ query like so:

在以上查询使用了LINQ语法的“where"子句去获得一个特定种类的产品. 通过Product的CategoryID 来实现过滤.

以上 LINQ to SQL具有非常好的灵活性,我可以充分利用在建模时建立的关系来实现更加丰富随意的查询.例如,我可以修改过滤条件为产品的CategoryName:

Notice above how I'm using the "Category" property that is on each of the Product objects to filter by the CategoryName of the Category that the Product belongs to. This property was automatically created for us by LINQ to SQL because we modeled the Category and Product classes as having a many to one relationship with each other in the database.

For another simple example of using our data model's association relationships within queries, we could write the below LINQ query to retrieve only those products that have had 5 or more orders placed for them:

注意我使用了"Category"属性去实现CategoryName条件过滤. 这个属性是由LINQ to SQL自动创建的, 这时因为我们在建模的时候Category和Product具有多对一的关系.

另外一个应用数据模型关系的简单例子, 我们可以写如下LINQ查询来获取具有５个或５个以上订单的产品

Notice above how we are using the "OrderDetails" collection that LINQ to SQL has created for us on each Product class (because of the 1 to many relationship we modeled in the LINQ to SQL designer).

注意以上我们使用了LINQ to SQL创建的每个产品类的 "OrderDetails" 集合 (因为在设计器中我们建立了一对多的关系）.

Visualizing LINQ to SQL Queries in the Debugger

Object relational mappers like LINQ to SQL handle automatically creating and executing the appropriate SQL code for you when you perform a query or update against their object model.

One of the biggest concerns/fears that developers new to ORMs have is "but what SQL code is it actually executing?" One of the really nice things about LINQ to SQL is that it makes it super easy to see exactly what SQL code it is executing when you run your application within the debugger.

Starting with Beta2 of Visual Studio 2008 you can use a new LINQ to SQL visualizer plug-in to easily see (and test out) any LINQ to SQL query expression. Simply set a breakpoint and then hover over a LINQ to SQL query and click the magnify glass to pull up its expression visualizer within the debugger:

在调试中查看LINQ to SQL查询

在对模型对象执行一个查询或更新的时候，象LINQ to SQL之类的对象关系映射可以自动地创建和执行合适的SQL语句。

对于一个刚接触ORM的开发者来说，最关心/担心的是“它到底执行什么样的SQL代码？＂．值得高兴得是，当你调试运行你的应用程序的时候，你可以很容易看到它到底在执行什么样的ＳＱＬ语句．

启动Visual Studio 2008，一个 LINQ to SQL 可视化插件工具可以用来察看或测试LINQ to SQL查询. 设置一个断点，然后将鼠标移动到语句的上方，点击放大镜弹出表达式对话框:

This will then bring up a dialog that shows you the exact SQL that LINQ to SQL will use when executing the query to retrieve the Product objects:

对话框中显示了获得产品对象查询的实际SQL语句：

If you press the "Execute" button within this dialog it will allow you to evaluate the SQL directly within the debugger and see the exact data results returned from the database:

如果点击对话框中的”执行“按钮，你就可以直接在调试器中验证查询并察看返回的结果。

This obviously makes it super easy to see precisely what SQL query logic LINQ to SQL is doing for you. Note that you can optionally override the raw SQL that LINQ to SQL executes in cases where you want to change it - although in 98% of scenarios I think you'll find that the SQL code that LINQ to SQL executes is really, really good.

这使得查看实际的SQL语句非常的容易。注意你依然可以根据需要重写 LINQ to SQL生成的SQL语句,尽管在98%的情况下,我认为 LINQ to SQL生成的SQL语句非常非常好.

Databinding LINQ to SQL Queries to ASP.NET Controls

LINQ queries return results that implement the IEnumerable interface - which is also an interface that ASP.NET server controls support to databind object. What this means is that you can databind the results of any LINQ, LINQ to SQL, or LINQ to XML query to any ASP.NET control.

For example, we could declare an <asp:gridview> control in a .aspx page like so:

LINQ to SQL 查询数据绑定ASP.NET 控件

LINQ 查询返回实现IEnumerable接口的结果 - 这也是ASP.NET服务器空间支持的数据绑定对象的接口. 这意味着你可以绑定 LINQ, LINQ to SQL, 或者 LINQ to XML 查询结果集到任何一个 ASP.NET 控件.

例如, 我们可以在aspx文件中声明一个 <asp:gridview> 控件, 如下所示:

I could then databind the result of the LINQ to SQL query we wrote before to the GridView like so:

绑定 LINQ to SQL查询结果集如下所示:

This will then generate a page that looks like below:

将产生如下页:

Shaping our Query Results

Right now when we are evaluating our product query, we are retrieving by default all of the column data needed to populate the Product entity classes.

For example, this query to retrieve products:

调整查询结果

在评估查询结果的时候, 我们看到, 在缺省条件下,我们获取了填充Product实体类的所有列的数据.

例如, 获取产品的查询:

Results in all of this data being returned:

所有数据都被返回:

Often we only want to return a subset of the data about each product. We can use the new data shaping features that LINQ and the new C# and VB compilers support to indicate that we only want a subset of the data by modifying our LINQ to SQL query like so:

但是通常我们只需要返回每个产品数据的子集. 我们可以使用LINQ和C#以及VB编译器支持的新特性 "数据调整特性" 来表明我们只需要数据的一个子集。修改我们的LINQ to SQL查询如下所示:

This will result in only this data subset being returned from our database (as seen via our debug visualizer):

这样只有这个数据子集从我们的数据库中返回（可以通过可视化调试器看到）

What is cool about LINQ to SQL is that I can take full advantage of my data model class associations when shaping my data. This enables me to express really useful (and very efficient) data queries. For example, the below query retrieves the ID and Name from the Product entity, the total number of orders that have been made for the Product, and then sums up the total revenue value of each of the Product's orders:

LINQ to SQL一个很特别的好处是在调整数据的时候我可以利用数据模型类之间的关系。这样我可以作非常有用（有效）的数据查询。例如，以下查询从Product实体中获取ID 和 Name, 此产品的订单总数, 以及对每个产品订单总收入的合计:

The expression to the right of the "Revenue" property above is an example of using the "Sum" extension method provided by LINQ. It takes a Lambda expression that returns the value of each product order item as an argument.

LINQ to SQL is smart and is able to convert the above LINQ expression to the below SQL when it is evaluated (as seen via our debug visualizer):

以上例子的“收入”属性使用了LINQ提供的“Sum”扩展方法。它采用 Lambda 表达式返回的每个产品订单项目作为参数.

LINQ to SQL 能够转换以上 LINQ 表达式到以下SQL语句（可以通过可视化调试器看到）：

The above SQL causes all of the NumOrders and Revenue value computations to be done inside the SQL server, and results in only the below data being retrieved from the database (making it really fast):

以上SQL语句会使NumOrders和Revenue的值在SQL SERVER 中计算, 而且仅以下数据返回(这使得数据返回很快)

We can then databind the result sequence against our GridView control to generate pretty UI:

在Gridview控件中绑定数据集来生成漂亮的UI:

BTW - in case you were wondering, you do get full intellisense within VS 2008 when writing these types of LINQ shaping queries:

顺便说一句, 在你编写LINQ自定义数据列查询感到迷惑的时候,VS2008会提供全面的智能提示:

In the example above I'm declaring an anonymous type that uses object initialization to shape and define the result structure. What is really cool is that VS 2008 provides full intellisense, compilation checking, and refactoring support when working against these anonymous result sequences as well:

以上示例中，我声明使用对象初始化的匿名类型来调整和定义结果结构. VS2008 提供了全面的智能提示，编译检查，以及对匿名结果顺序提供重构支持。

Paging our Query Results

One of the common needs in web scenarios is to be able to efficiently build data paging UI. LINQ provides built-in support for two extension methods that make this both easy and efficient - the Skip() and Take() methods.

We can use the Skip() and Take() methods below to indicate that we only want to return 10 product objects - starting at an initial product row that we specify as a parameter argument:

在查询结果中排序

通常需要的场景中有一个是实现数据分页。LINQ提供两个内置的扩展方法Skip() 和Take()来实现高效的数据分页。

我们可以使用 Skip() 和 Take() 方法来表明我们只需要返回10个产品对象-从我们以参数指定的那行开始：

Note above how I did not add the Skip() and Take() operator on the initial products query declaration - but instead added it later to the query (when binding it to my GridView datasource). People often ask me "but doesn't this mean that the query first grabs all the data from the database and then does the paging in the middle tier (which is bad)?" No. The reason is that LINQ uses a deferred execution model - which means that the query doesn't actually execute until you try and iterate over the results.

One of the benefits of this deferred execution model is that it enables you to nicely compose queries across multiple code statements (which improves code readability). It also enables you to compose queries out of other queries - which enables some very flexible query composition and re-use scenarios.

Once I have the BindProduct() method defined above, I can write the code below in my page to retrieve the starting index from the querystring and cause the products to be paged and displayed in the gridview:

注意，在以上例子，我没有在初始化产品查询声明中添加 Skip() 和 Take() 操作符 - 但是我在稍后添加（当把数据绑定到GridView数据源的时候）人们经常问我“但这不意味着查询将从数据库抓取所有的数据，然后再作分页?" 不是这样, 原因是LINQ使用延迟的执行模型-这意味着只有在你提取/遍历数据的时候查询才会执行。

延迟执行模型的一个好处是这使你可以编写涵盖多个statement的查询（这可以提高可读性）。同时也可以使你从其它查询中提取查询-这使得实现一些非常灵活的查询组合和可重用的场景。

一旦定义了上面的BindProduct() 方法, 我可以编写以下代码从Querystring中获得开始的编号，使产品分页显示在GridView中。

This will then give us a products page, filtered to list only those products with more than 5 orders, showing dynamically computed product data, and which is pageable via a querystring argument:

于是将打开一个产品页，过滤并显示超过5个订单的产品，同时动态地计算产品数据，通过Querystring 的参数分页显示。

Note: When working against SQL 2005, LINQ to SQL will use the ROW_NUMBER() SQL function to perform all of the data paging logic in the database. This ensures that only the 10 rows of data we want in the current page view are returned from the database when we execute the above code:

注意：在 SQL 2005中, LINQ to SQL 使用ROW_NUMBER() SQL 函数实现数据分页逻辑。这保证在我们执行以上代码的时候，只有10行数据从数据库中返回并在当前页显示：

This makes it efficient and easy to page over large data sequences.

这使得对于很大的数据量分页变得高效和简便。

Summary

Hopefully the above walkthrough provides a good overview of some of the cool data query opportunities that LINQ to SQL provides. To learn more about LINQ expressions and the new language syntax supported by the C# and VB compilers with VS 2008, please read these earlier posts of mine:

Automatic Properties, Object Initializer and Collection Initializers

In my next post in this LINQ to SQL series I'll cover how we can cleanly add validation logic to our data model classes, and demonstrate how we can use it to encapsulate business logic that executes every time we update, insert, or delete our data. I'll then cover more advanced lazy and eager loading query scenarios, how to use the new <asp:LINQDataSource> control to support declarative databinding of ASP.NET controls, optimistic concurrency error resolution, and more.

总结

希望以上能够使你有一个LINQ to SQL数据查询优良特性的概览。学习 LINQ 表达式以及 VS2008中 C# 和 VB 编译器支持的新的语法, 请阅读一下文章: