(翻译) LINQ to SQL(Part 1) 自ScottGu

写在前面，偶尔中看了下LINQ，尽管和iBatis似乎差不多，但微软的集成环境显然是开源框架无法比拟的。强大的图形化操作，集成的开发环境，看来其他ＯＲＭ在新版本的ＶＳ发布之后需要有新的创意了。

Over the last few months I wrote a series of blog posts that covered some of the new language features that are coming with the Visual Studio and .NET Framework "Orcas" release.  Here are pointers to the posts in my series:

前几个月曾经写了一系列关于Visual Studio 和 .NET Framework新的语言特性的文章，以下是那些系列的链接：

Automatic Properties, Object Initializer and Collection Initializers Extension Methods Lambda Expressions Query Syntax Anonymous Types

The above language features help make querying data a first class programming concept.  We call this overall querying programming model "LINQ" - which stands for .NET Language Integrated Query.

以上语言特性使查询数据首次具有类型编程的概念．我们称此查询编程对象为"LINQ",  意思是.NET语言集成查询．

Developers can use LINQ with any data source.  They can express efficient query behavior in their programming language of choice, optionally transform/shape data query results into whatever format they want, and then easily manipulate the results.  LINQ-enabled languages can provide full type-safety and compile-time checking of query expressions, and development tools can provide full intellisense, debugging, and rich refactoring support when writing LINQ code.

LINQ supports a very rich extensibility model that facilitates the creation of very efficient domain-specific operators for data sources.  The "Orcas" version of the .NET Framework ships with built-in libraries that enable LINQ support against Objects, XML, and Databases.

不论任何数据源，开发者都能够使用ＬＩＮＱ。　可以采用不同的编程语言去实现有效的查询，或者将查询结果任意转换成他们需要的、易于操作类型。　LINQ-enabled 的语言可以提供完全的类型安全以及在编译时验证查询表达式， 而且在编写LINQ的代码时开发工具可以提供完全的智能提示，调试，和丰富的重构支持。

LINQ 支持一个具有广泛扩展性的模型，针对于不同数据源可以方便的创建有效而特定的操作因子。 Orcas版本的.Net框架内建了支持Objects， XML， 和Databases的LINQ类库。

What Is LINQ to SQL?

LINQ to SQL is an O/RM (object relational mapping) implementation that ships in the .NET Framework "Orcas" release, and which allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.

LINQ to SQL fully supports transactions, views, and stored procedures.  It also provides an easy way to integrate data validation and business logic rules into your data model.

什么是LINQ to SQL

LINQ to SQL 是对象关系映射在.NET框架中的一种实现， 如此你可以将关系数据库映射为.NET中的一些类。然后你可以使用LINQ对数据库进行数据查询，修改，插入，删除的操作。

LINQ to SQL 完全支持事务，视图和存储过程。　同时对于数据模型中集成数据合法性验证和商业逻辑规则也提供了简便易行的方法。

Modeling Databases Using LINQ to SQL:

Visual Studio "Orcas" ships with a LINQ to SQL designer that provides an easy way to model and visualize a database as a LINQ to SQL object model.  My next blog post will cover in more depth how to use this designer (you can also watch this video I made in January to see me build a LINQ to SQL model from scratch using it). 

Using the LINQ to SQL designer I can easily create a representation of the sample "Northwind" database like below:

使用LINQ对数据库建模

Visual Studio "Orcas“中包含了LINQ to SQL设计器, 可以很方便的对数据库建模，并且将数据库可视化为一个LINQ to SQL 对象模型。　下一篇日志将详细描述如何使用设计器（或者看一月份录制的ｖｉｄｅｏ）。

以下是使用LINQ to SQL 设计器为示例数据库"Northwind"创建而成的

My LINQ to SQL design-surface above defines four entity classes: Product, Category, Order and OrderDetail.  The properties of each class map to the columns of a corresponding table in the database.  Each instance of a class entity represents a row within the database table.

The arrows between the four entity classes above represent associations/relationships between the different entities.  These are typically modeled using primary-key/foreign-key relationships in the database.  The direction of the arrows on the design-surface indicate whether the association is a one-to-one or one-to-many relationship.  Strongly-typed properties will be added to the entity classes based on this.  For example, the Category class above has a one-to-many relationship with the Product class.  This means it will have a "Categories" property which is a collection of Product objects within that category.  The Product class then has a "Category" property that points to a Category class instance that represents the Category to which the Product belongs.

The right-hand method pane within the LINQ to SQL design surface above contains a list of stored procedures that interact with our database model.  In the sample above I added a single "GetProductsByCategory" SPROC.  It takes a categoryID as an input argument, and returns a sequence of Product entities as a result.  We'll look at how to call this SPROC in a code sample below.

以上图示定义了四个实体类：产品, 类别,订单和订单细节. 每一个类的属性与数据库中相应表的列相对应。每一个实体类的实例代表数据库表中的一行。

四个实体类间的箭头代表了不同实体间的关系。这些关系主要是由数据库中的主键/外键关系形成。箭头的指向表明了关系是一对一还是一对多。由此强类型的属性将被加到实体类中。例如,  “类别”类和产品类有一对多的关系，这意味着在"类别“类中将有一个Categories的属性--该类别所属的产品集合. 产品类将有一个类别属性指向该产品所述的类别。

在右手的method区域是与数据库交互的存储过程列表。 在示例中，我加了一个"GetProductsByCategory"存储过程。通过CategoryID作为输入参数，返回相应产品实体的列表。 在以下代码我们将看看如何调用存储过程

Understanding the DataContext Class

When you press the "save" button within the LINQ to SQL designer surface, Visual Studio will persist out .NET classes that represent the entities and database relationships that we modeled.  For each LINQ to SQL designer file added to our solution, a custom DataContext class will also be generated.  This DataContext class is the main conduit by which we'll query entities from the database as well as apply changes.  The DataContext class created will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we added.

For example, below is the NorthwindDataContext class that is persisted based on the model we designed above:

理解DataContext类

当你点击"Save" 按钮的时候，Visual Studio 将会保存我们映射实体和数据库的类。 对于每一个加到解决方案的LINQ to SQL designer文件，都会生成一个用户 DataContext类。 这个DataContext类是对数据库进行查询，修改得主要渠道。DataContext类将会包含代表数据库表字段的所有属性以及对应每一个存储过程的方法。

以下是基于我们设计的模型的NorthwindDataContext类

LINQ to SQL Code Examples

Once we've modeled our database using the LINQ to SQL designer, we can then easily write code to work against it.  Below are a few code examples that show off common data tasks:

LINQ to SQL代码示例

一旦使用了LINQ to SQL designer 对数据库建模，我们就可以方便的编写代码进行数据库的操作。以下是一些常用代码的示例。

1) Query Products From the Database

The code below uses LINQ query syntax to retrieve an IEnumerable sequence of Product objects.  Note how the code is querying across the Product/Category relationship to only retrieve those products in the "Beverages" category:

1)在数据库中查询产品

以下代码使用LINQ查询语法得到产品对象的列表。注意代码是如何通过Product/Category的关系获得"Beverages"的产品。

2) Update a Product in the Database

The code below demonstrates how to retrieve a single product from the database, update its price, and then save the changes back to the database:

2) 更新数据库中的一个产品

以下代码显示如何从数据库中获得一个产品, 更新它的价格, 然后保存改变到数据库中:

3) Insert a New Category and Two New Products into the Database

The code below demonstrates how to create a new category, and then create two new products and associate them with the category.  All three are then saved into the database.

Note below how I don't need to manually manage the primary key/foreign key relationships. Instead, just by adding the Product objects into the category's "Products" collection, and then by adding the Category object into the DataContext's "Categories" collection, LINQ to SQL will know to automatically persist the appropriate PK/FK relationships for me. 

3) 插入一个新的类别,两个新的产品

以下代码显示如何创建一个类别、两个新的产品, 设定这两个产品为此种类, 保存到数据库中:。

注意不需要手动地管理主键/外键关系。 反而，只是将产品对象加入类别的产品集合中， 然后将类别对象加入DataContext的类别集合中，LINQ to SQL 将自动保持正确的主外键关系

4) Delete Products from the Database

The code below demonstrates how to delete all Toy products from the database:

4) 删除产品

以下代码显示如何删除产品:

5) Call a Stored Procedure

The code below demonstrates how to retrieve Product entities not using LINQ query syntax, but rather by calling the "GetProductsByCategory" stored procedure we added to our data model above.  Note that once I retrieve the Product results, I can update/delete them and then call db.SubmitChanges() to persist the modifications back to the database.

5) 调用存储过程

以下代码显示如何不通过LINQ查询，而是通过调用我们加入数据模型的存储过程"GetProductsByCategory" 来获得产品实例.  注意一旦获得了产品结果，就可以修改／删除它们，之后调用 db.SubmitChanges() 将修改保存到数据库。

6) Retrieve Products with Server Side Paging

The code below demonstrates how to implement efficient server-side database paging as part of a LINQ query.  By using the Skip() and Take() operators below, we'll only return 10 rows from the database - starting with row 200.

6) 获取在服务器端排序的产品

以下代码显示如何实现有效的在服务器排序。 通过 Skip()  Take() 操作，　我们将只获得从第200行开始的10 行数据。

Summary

LINQ to SQL provides a nice, clean way to model the data layer of your application.  Once you've defined your data model you can easily and efficiently perform queries, inserts, updates and deletes against it. 

Hopefully the above introduction and code samples have helped whet your appetite to learn more.  Over the next few weeks I'll be continuing this series to explore LINQ to SQL in more detail.

总结

LINQ to SQL　提供了一个优秀的，清晰的方法来为你应用程序的数据层建模。 一旦你定义了数据模型你就可以方便有效地执行查询，插入，更新和删除操作。

希望以上的介绍和代码示例能够让你有兴趣去深入的学习。在接下来的几个星期，我将继续这个系列去探究 LINQ to SQL 的更多细节。

转载于:https://www.cnblogs.com/robinsmile/archive/2007/09/21/901696.html