(翻译) LINQ to SQL(Part 2　定义数据模型类)　自ScottGu-CSDN博客

In Part 1 of my LINQ to SQL blog post series I discussed "What is LINQ to SQL" and provided a basic overview of some of the data scenarios it enables.

In my first post I provided code samples that demonstrated how to perform common data scenarios using LINQ to SQL including:

How to query a database How to update rows in a database How to insert and relate multiple rows in a database How to delete rows in a database How to call a stored procedure How to retrieve data with server-side paging

I performed all of these data scenarios using a LINQ to SQL class model that looked like the one below:

在我的LINQ to SQL系列的第一部分，探讨了什么是LINQ to SQL，以及一些数据操作情景的概览。

包括了如下常用的数据操作代码示例:

如何查询数据库
如何更新数据
如何插入数据
如何删除数据
如何调用存储过程
如何获得在服务器段分页的数据

演示数据操作的LINQ to SQL 类模型如下所示

In this second blog post in the series I'm going to go into more detail on how to create the above LINQ to SQL data model.

LINQ to SQL, the LINQ to SQL Designer, and all of the features that I'm covering in this blog post series will ship as part of the .NET 3.5 and Visual Studio "Orcas" release.

You can follow all of the steps below by downloading either Visual Studio "Orcas" Beta 1 or Visual Web Developer Express "Orcas" Beta1. Both can be installed and used side-by-side with VS 2005.

在此系列的第二篇日志里，我将详细介绍如何创建LINQ to SQL数据模型。

LINQ to SQL， LINQ to SQL设计器，以及此系列将涉及到的所有特性都将集成在.NET 3.5 和 Visual Studio "Orcas" 版本中。

你可以下载 Visual Studio "Orcas" Beta 1 或 Visual Web Developer Express "Orcas" Beta1来演示以下操作。它们都可以与 VS2005同时安装。

Create a New LINQ to SQL Data Model

You can add a LINQ to SQL data model to an ASP.NET, Class Library or Windows client project by using the "Add New Item" option within Visual Studio and selecting the "LINQ to SQL" item within it:

创建一个新的 LINQ to SQL 数据模型

可以通过Visual Studio的"Add New Item" 选项，选择"LINQ to SQL"为 ASP.NET、类库或者Windows客户项目添加一个 LINQ to SQL 数据模型 :

Selecting the "LINQ to SQL" item will launch the LINQ to SQL designer, and allow you to model classes that represent a relational database. It will also create a strongly-typed "DataContext" class that will have properties that represent each Table we modeled within the database, as well as methods for each Stored Procedure we modeled. As I described in Part 1 of this blog post series, the DataContext class is the main conduit by which we'll query entities from the database as well as apply changes back to it.

Below is a screen-shot of an empty LINQ to SQL ORM designer surface, and is what you'll see immediately after creating a new LINQ to SQL data model:

选择"LINQ to SQL"将启动"LINQ to SQL"设计器，然后可以创建映射关系数据库的类。强类型的"DataContext"类包含建模的每一张表的属性以及存储过程。如我在第一篇中所说，"DataContext"类是实现数据库查询和修改的主要渠道。

以下是LINQ to SQL ORM设计器空白界面，就是当你创建LINQ to SQL数据模型的起始界面

Entity Classes

LINQ to SQL enables you to model classes that map to/from a database. These classes are typically referred to as "Entity Classes" and instances of them are called "Entities". Entity classes map to tables within a database. The properties of entity classes typically map to the table's columns. Each instance of an entity class then represents a row within the database table.

Entity classes defined with LINQ to SQL do not have to derive from a specific base class, which means that you can have them inherit from any object you want. All classes created using the LINQ to SQL designer are defined as "partial classes" - which means that you can optionally drop into code and add additional properties, methods and events to them.

Unlike the DataSet/TableAdapter feature provided in VS 2005, when using the LINQ to SQL designer you do not have to specify the SQL queries to use when creating your data model and access layer.

Instead, you focus on defining your entity classes, how they map to/from the database, and the relationships between them. The LINQ to SQL OR/M implementation will then take care of generating the appropriate SQL execution logic for you at runtime when you interact and use the data entities. You can use LINQ query syntax to expressively indicate how to query your data model in a strongly typed way.

实体类

LINQ to SQL 可以创建映射数据库的类. 这些类被称为实体类，实体类的实例称为实体对象。实体类映射数据库中的表。实体类的属性映射表中的列。每一个实体类的实例代表数据库表的一行。

LINQ to SQL实体类不必基于一个特别的基类, 这意味着你可以继承任何类。所有通过LINQ to SQL 设计器创建的类被定义为 "partial classes" -这意味着你可以随意加入代码或是属性，方法及事件。

不象在VS2005 中提供的DataSet/TableAdapter 的特性, 使用LINQ to SQL 设计器时，在创建你的数据模型和数据访问层的时候你不必明确SQL查询语句。

相反，你要关注于如何定义你的实体类，如何使他们映射到数据库，以及类之间的关系。在使用数据实体或数据交互的时候，LINQ to SQL OR/M 将会在运行的时候生成恰当的ＳＱＬ执行逻辑。你可以使用 LINQ 查询语法明确如何以强类型的方式查询数据模型。

Creating Entity Classes From a Database

If you already have a database schema defined, you can use it to quickly create LINQ to SQL entity classes modeled off of it.

The easiest way to accomplish this is to open up a database in the Server Explorer within Visual Studio, select the Tables and Views you want to model in it, and drag/drop them onto the LINQ to SQL designer surface:

从一个数据库创建实体类

如果存在一个数据库的纲要，你可以通过LINQ to SQL设计器很快的创建实体类

最方便的办法是在Visual Studio的Server Explorer 中打开一个数据库,　选择你需要建模的表和视图，然后把它们拖拽到LINQ to SQL 设计器。

When you add the above 2 tables (Categories and Products) and 1 view (Invoices) from the "Northwind" database onto the LINQ to SQL designer surface, you'll automatically have the following three entity classes created for you based on the database schema:

当把"Northwind"数据库中的以上两张表和一个视图拖拽入LINQ to SQL设计器中，基于数据库纲要三个实体类将会自动地创建。

Using the data model classes defined above, I can now run all of the code samples (expect the SPROC one) described in Part 1 of this LINQ to SQL series. I don't need to add any additional code or configuration in order to enable these query, insert, update, delete, and server-side paging scenarios.

使用以上数据模型类，不需要添加其他代码或设置来启用查询，插入，更新，删除以及服务器端排序，就可以运行LINQ to SQL系列第一部分的代码示例。

Naming and Pluralization

One of the things you'll notice when using the LINQ to SQL designer is that it automatically "pluralizes" the various table and column names when it creates entity classes based on your database schema. For example: the "Products" table in our example above resulted in a "Product" class, and the "Categories" table resulted in a "Category" class. This class naming helps make your models consistent with the .NET naming conventions, and I usually find having the designer fix these up for me really convenient (especially when adding lots of tables to your model).

If you don't like the name of a class or property that the designer generates, though, you can always override it and change it to any name you want. You can do this either by editing the entity/property name in-line within the designer or by modifying it via the property grid:

命名和单数化

有一点你需要注意，当创建实体类的时候，LINQ to SQL 设计器会基于数据库纲要自动单数化表及列的名称。例如："Products"表被映射为"Product"类， "Categories" 表被映射为 "Category" 类，这使实体类的命名与.NET的命名惯例保持一致。

如果你不喜欢设计器生成的类和属性的名称，那么在设计器的代码行里或是在属性栏里修改实体／属性的名字

The ability to have entity/property/association names be different from your database schema ends up being very useful in a number of cases. In particular:

1) When your backend database table/column schema names change. Because your entity models can have different names from the backend schema, you can decide to just update your mapping rules and not update your application or query code to use the new table/column name.

2) When you have database schema names that aren't very "clean". For example, rather than use "au_lname" and "au_fname" for the property names on an entity class, you can just name them to "LastName" and "FirstName" on your entity class and develop against that instead (without having to rename the column names in the database).

在很多情况下，实体类／属性／关系的名称与数据库纲要不同非常有用：

1) 后台数据库的表／列的命名改变。因为实体模型可以有不同于后台数据库纲要的名字，你可以更新你的映射规则来保持应用程序或者查询代码使用新的表／列的名字。

2) 数据库纲要不是非常清晰。例如，　你不使用 "au_lname"和"au_fname"作为你实体属性的名字，在实体类中你可以命名为 "LastName" 和 "FirstName" (而不需要更改你数据库中的名字).

Relationship Associations

When you drag objects from the server explorer onto the LINQ to SQL designer, Visual Studio will inspect the primary key/foreign key relationships of the objects, and based on them automatically create default "relationship associations" between the different entity classes it creates. For example, when I added both the Products and Categories tables from Northwind onto my LINQ to SQL designer you can see that a one to many relationship between the two is inferred (this is denoted by the arrow in the designer):

关系

当你将表或视图从服务器浏览中拖拽入LINQ to SQL 设计器时, Visual Studio 将会检查主／外键的关系，并且自动生成创建的实体类的缺省关系。　例如，当我把产品表和类别表加入设计器中时，你可以看到两个实体类一对多的关系:

The above association will cause cause the Product entity class to have a "Category" property that developers can use to access the Category entity for a given Product. It will also cause the Category class to have a "Products" collection that enables developers to retrieve all products within that Category.

以上关系会使产品类拥有类别属性，通过这个属性可以得到给定产品的类别。同样也是类别类拥有产品集合属性，通过这个属性可以得到此类别下所有产品。

If you don't like how the designer has modeled or named an association, you can always override it. Just click on the association arrow within the designer and access its properties via the property grid to rename, delete or modify it.

如果你不喜欢设计起生成的关系或其名称，你可以点击关系箭头，在它的属性栏中进行重命名，删除或修改。

Delay/Lazy Loading

LINQ to SQL enables developers to specify whether the properties on entities should be prefetched or delay/lazy-loaded on first access. You can customize the default pre-fetch/delay-load rules for entity properties by selecting any entity property or association in the designer, and then within the property-grid set the "Delay Loaded" property to true or false.

For a simple example of when I'd want to-do this, consider the "Category" entity class we modeled above. The categories table inside "Northwind" has a "Picture" column which stores a (potentially large) binary image of each category, and I only want to retrieve the binary image from the database when I'm actually using it (and not when doing a simply query just to list the category names in a list).

I could configure the Picture property to be delay loaded by selecting it within the LINQ to SQL designer and by settings its Delay Loaded value in the property grid:

延迟加载

LINQ to SQL 可以让开发者决定在首次访问时是否预／延迟加载实体中的属性。你可以为实体属性自定义缺省的预／延迟加载规则，在设计器中选择实体的属性或关系，然后再其属性栏设置 "Delay Loaded" 属性.

一个简单的例子，之前所述的"Category" 类，类别表中有一个存储二进制的类别图像"Picture"列 , 我希望只在真正要使用这个图片的时候提取这个二进制的图片（而不是在显示类别名字列表时）。

在 LINQ to SQL 设计器中选择该属性，我可以在其属性栏中设置 Delay Loaded 的值设置图片属性延迟加载：

Note: In addition to configuring the default pre-fetch/delay load semantics on entities, you can also override them via code when you perform LINQ queries on the entity class (I'll show how to-do this in the next blog post in this series).

注意：在设置缺省的预／延迟加载之后，在执行LINQ查询时你依然可以在代码中覆写设置（在接下来的日志中将会详细介绍）

Using Stored Procedures

LINQ to SQL allows you to optionally model stored procedures as methods on your DataContext class. For example, assume we've defined the simple SPROC below to retrieve product information based on a categoryID:

使用存储过程

LINQ to SQL　可以将存储过程映射为DataContext 类的方法. 例如，假设我们定义了如下基于categoryID来获得产品信息的存储过程：

I can use the server explorer within Visual Studio to drag/drop the SPROC onto the LINQ to SQL designer surface in order to add a strongly-typed method that will invoke the SPROC. If I drop the SPROC on top of the "Product" entity in the designer, the LINQ to SQL designer will declare the SPROC to return an IEnumerable<Product> result:

可以通过Visual Studio 的服务器浏览器，拖拽存储过程到 LINQ to SQL设计器中，来添加一个强类型的方法去调用存储过程。如果我在设计器中将SPROC存储过程置于"Product"实体顶端，LINQ to SQL设计器将声明这个返回IEnumerable<Product> 的存储过程。

I can then use either LINQ Query Syntax (which will generate an adhoc SQL query) or alternatively invoke the SPROC method added above to retrieve product entities from the database:

可以使用LINQ查询语法或是调用存储过程的方法来获得产品实体。

Using SPROCs to Update/Delete/Insert Data

By default LINQ to SQL will automatically create the appropriate SQL expressions for you when you insert/update/delete entities. For example, if you wrote the LINQ to SQL code below to update some values on a "Product" entity instance:

使用存储过程修改／删除／插入数据

在默认情况下，LINQ to SQL 会自动生成恰当的ＳＱＬ表达式当你插入／修改／删除实体的时候。　例如，以下代码更新某些"Product" 实体的值。

By default LINQ to SQL would create and execute the appropriate "UPDATE" statement for you when you submitted the changes (I'll cover this more in a later blog post on updates).

You can also optionally define and use custom INSERT, UPDATE, DELETE sprocs instead. To configure these, just click on an entity class in the LINQ to SQL designer and within its property-grid click the "..." button on the Delete/Insert/Update values, and pick a particular SPROC you've defined instead:

在默认情况下，在提交你的更新的时候，LINQ to SQL将会自动创建和执行恰当的 "UPDATE" 语句，(I以后的篇章会详述).

你也可以定义使用自定义的INSERT, UPDATE, DELETE 存储过程. 通过点击实体类，在其属性栏里点击Delete/Insert/Update值"..." 按钮，然后选择你已经定义的存储过程。

What is nice about changing the above setting is that it is done purely at the mapping layer of LINQ to SQL - which means the update code I showed earlier continues to work with no modifications required. This avoids developers using a LINQ to SQL data model from having to change code even if they later decide to put in a custom SPROC optimization later.

设置的改变仅仅是在LINQ to SQL的映射层，这意味着之前更新代码无需修改依然可用。这样避免开发者在使用存储过程优化的时候去修改代码。

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.

Using the built-in LINQ to SQL designer within Visual Studio and Visual Web Developer Express you can create and manage your data models for LINQ to SQL extremely fast. The LINQ to SQL designer also provides a lot of flexibility that enables you to customize the default behavior and override/extend the system to meet your specific needs.

In upcoming posts I'll be using the data model we created above to drill into querying, inserts, updates and deletes further. In the update, insert and delete posts I'll also discuss how to add custom business/data validation logic to the entities we designed above to perform additional validation logic.

Mike Taulty also has a number of great LINQ to SQL videos that I recommend checking out here. These provide a great way to learn by watching someone walkthrough using LINQ to SQL in action.