[翻译]LINQ Project: Unified Language Features for Object and Relational Queries(5)
Exercise 4 – LINQ to SQL: LINQ for Connected Databases
This exercise begins by demonstrating that the same features available for querying in-memory collections, xml files, and data sets, can also be applied to databases. The exercise then continues to show more advanced features available in LINQ to SQL.
本练习开始演示，可用于查询在内存中的收藏集、 xml 文件和数据集的相同功能也可以应用于数据库。 此练习继续展示SQL 的LINQ可用到的更高级功能。
LINQ to SQL is part of the LINQ project and allows you to query and manipulate objects associated with database tables. It eliminates the traditional mismatch between database tables and your application’s domain specific object model, freeing you to work with data as objects while the framework manages retrieving and updating your objects.
SQL 的LINQ 是 LINQ 项目的一部分并允许您来查询和处理与数据库表关联的对象。 它消除了传统的数据库表和您的应用程序的域特定的对象模型，当你使用的数据对象被框架管理重新检索或更新时将被释放。
To create an object model of a given database, classes must be mapped to database entities. There are three ways to create object mappings: adding attributes to an existing object, using the provided designer to auto-generate the objects and mappings, and using the command line SQLMetal tool. This exercise walks through the first two of these three methods.
若要创建一个给定的数据库的对象模型，类必须被映射到数据库实体。 有三种方法来创建对象映射： 将属性添加到现有的对象，使用所提供的设计器自动生成的对象和映射，并使用命令行 SQLMetal 工具。 本练习指导完成这三种方法。
Task 1 – Creating Object Mapping – Creating an Object and Providing Attributes
1. At the top of program.cs add the following using directives.
using System.Data.Linq;
using System.Data.Linq.Mapping;
ps：首先通过引用System.Data.Linq.dll文件。
2. Add the following attributes for Customer to create the mapping to the database Customers table that includes columns named CustomerID and City. Here you will only map two columns in the single Customers table in Northwind.
为Customer类增加下列属性以创建映射数据库中包含字段CustomerID和City的Customers表。这里你将仅影射两个字段在Northwind中的单表Customers
[Table(Name = "Customers")]
public class Customer
{
[Column]
public string CustomerID { get; set; }
[Column]
public string City { get; set; }
public override string ToString()
{
return CustomerID + "\t" + City;
}
}
3. Return to the ObjectQuery method. As you did for in-memory collections, xml, and datasets, again query to find customers that live in London. Notice that minimal changes are required. After creating a data connection you are able to get rows out of the Customers table and select those rows for customers that live in London, returning them as IEnumerable<Customer>.
返回到ObjectQuery方法。如同你做内存中集合，XML和数据集一样再次找到居住在London的数据。注意需要有很小的变化。创建数据库链接之后你能获得Customers表中所有数据，并且使用IEnumerable<Customer>返回过滤出居住在London的行，
static void ObjectQuery()
{
var db = new DataContext
(@"Data Source=.\sqlexpress;Initial Catalog=Northwind");
var results = from c in db.GetTable<Customer>()
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);
}
The DataContext object used in the ObjectQuery method is the main conduit through which objects are retrieved from the database and changes are submitted.
DataContext对象在ObjectQuery方法中被用于主要从数据库中接收对象和提交改变
4. Return to the Main method and change the method call to ObjectQuery
static void Main(string[] args)
{
ObjectQuery();
}
5. Press Ctrl+F5 to build and run the application. After viewing the results press any key to terminate the application.
6. Now add the following line to print the generated SQL query that runs on the database:
static void ObjectQuery()
{
DataContext db = new DataContext(
@"Data Source=.\sqlexpress;Initial Catalog=Northwind");
db.Log = Console.Out;
var results = from c in db.GetTable<Customer>()
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);
}
7. Press Ctrl+F5 to build and run the application. After viewing the results and the generated SQL query, press any key to terminate the application.
Task 2 – Creating Object Mapping – Using the Designer – Add Designer File
1. First remove the old mapping. Delete the entire Customer class.
2. Next, create objects to model the tables. Right click the LINQ Overview project and click Add | New Item.
3. In Templates click LINQ To SQL File.
4. Provide a name for the new item by entering “Northwind” in the Name box
5. Click OK.
Task 3 – Creating Object Mapping – Using the Designer – Create the Object View
1. Expand Data Connections in Server Explorer.
1. Open the Northwind folder.
2. Open the Tables folder.
3. Open the Northwind.dbml file by double clicking it in Solution Explorer.
4. From the tables folder drag the Customers table into the method pane.
5. From the tables folder drag the Products table into the method pane.
6. From the tables folder drag the Employees table into the method pane.
7. From the tables folder drag the Orders table into the method pane.
8. From the stored procedures folder drag the Top Most Extensive Products into the method pane
1. Press Ctrl+Shift+B to build the application. Take a look at the auto-generated mapping class. Notice a similar use of the attributes.
For databases with many tables and stored procedures, using the command line tool SQLMetal provides more automation and may be a better choice.
如果数据库需要有很多表和存储过程，使用命令行工具SQLMetal提供的更多自动操作可能会是一个更好的选择。
Task 4 – Querying using Expressions
1. Return to the program code file by double clicking on the program.cs file in Solution Explorer. Find the ObjectQuery method. Each table can now be accessed as a property of the db variable. At this point, querying is almost identical to the previous exercises. Add the following code to retrieve customers in London:
通过在资源浏览器(Solution Explorer)中双击program.cs文件返回程序代码。找到ObjectQuery方法。每张表能作为变量db的一个属性直接读取。这里指出，查询和早先的练习差不多。增加下面的代码到返回居住London的消费者。
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
where c.City == "London"
select c;
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);
}
This creates a NorthwindDataContext object (which extends the DataContext class previously used in the first task) that represents the strongly typed connection to the database. As such it is important to note that there is no connection string specified and intellisense shows all the tables specified by the designer.
创建 NorthwindDataContext 对象 （该扩展以前使用在第一个任务中 DataContext 类） 表示强类型连接到数据库。 同样值得重点注意，没有指定连接字符串并由设计器的智能感知说明显示所有表。
2. Press Ctrl+F5 to build and run the application. After viewing the results, press any key to terminate the application.
Six results are shown. These are customers in the Northwind Customers table with a City value of London.
六个结果显示。 这些是City值是London的Northwind Customers表中消费者
3. You also created mappings to other tables when using the designer. The Customer class has a one-to-many mapping to Orders. This next query selects from multiple tables.
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
from o in c.Orders
where c.City == "London"
select new { c.ContactName, o.OrderID };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.ContactName, c.OrderID);
}
The select statement creates a new object with an anonymous type (a new C# 3.0 feature). The type created holds two pieces of data, both strings with the names of the properties of the original data (in this case ContactName and OrderID). Anonymous types are quite powerful when used in queries. By using these types it saves the work of creating classes to hold every type of result created by various queries.
In the preceding example, the object model can easily be seen by noticing the object relationship shown by writing c.Orders. This relationship was defined in the designer as a one-to-many relationship and now can be accessed in this manner.
Select 语句用匿名类型 （一个新的 C# 3.0 功能） 创建一个新的对象。 创建该类型包含两个数据块、 两个字符串与原始数据 （在此案例ContactName和OrderID 中） 的属性的名称。 匿名类型是相当强大查询中使用时。 通过使用这些类型它保存的创建类，以保存每个类型的结果由各种查询创建的工作。
在前面的示例，对象模型可以很容易地看到通过通知所示编写 c.Orders 对象关系。 这种关系中设计器为一对多的关系定义的并且现在可以以这种方式进行访问。
4. Press Ctrl+F5 to build and run the application to view the results. Then press any key to terminate the application.
Task 5 – Modifying Database Data
In this task, you move beyond data retrieval and see how to manipulate the data. The four basic data operations are Create, Retrieve, Update, and Delete, collectively referred to as CRUD. You see how LINQ to SQL makes CRUD operations simple and intuitive. This task shows how to use the create and update operations.
在此任务，您超越数据检索并查看如何处理数据。 创建、 检索、 更新和删除四个基本的数据操作共同称为 CRUD。 您会看到LINQ to SQL 如何使 CRUD 操作简单和直观。 此任务演示如何使用创建和更新操作。
5. Create a new method that modifies the database data as well as a call from Main:
static void Main(string[] args)
{
ModifyData();
}
static void ModifyData()
{
var db = new NorthwindDataContext();
var newCustomer = new Customer
{
CompanyName = "AdventureWorks Cafe",
CustomerID = "ADVCA"
};
Console.WriteLine("Number Created Before: {0}",
db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());
db.Customers.Add(newCustomer);//vs2008英文正式版中没有找到Add方法。但在beta2中有
db.SubmitChanges();
Console.WriteLine("Number Created After: {0}",
db.Customers.Where( c => c.CustomerID == "ADVCA" ).Count());
}
6. Press Ctrl+F5 to build and run the application. Notice that the two lines written to the screen are different after the database is updated. Now press any key to terminate the application.
Notice that after the Add method is called, the changes are then submitted to the database using the SubmitChanges method. Note that once the customer has been inserted, it cannot be inserted again due to the primary key uniqueness constraint. Therefore this program can only be run once.
请注意，调用添加方法后, 这些更改使用了 SubmitChanges 方法提交到数据库。 请注意，一旦客户已插入，由于到主键唯一性约束它不能再次插入。 因此该程序只能运行一次。
7. Now update and modify data in the database. Add the following code that updates the contact name for the first customer retrieved.
现在在数据库中更新和修改数据。增加下列代码用于更新检索第一个客户的联系人名字。
static void ModifyData()
{
var db = new NorthwindDataContext();
var existingCustomer = db.Customers.First();
Console.WriteLine("Number Updated Before: {0}",
db.Customers.Where( c => c.ContactName == "New Contact" ).Count());
existingCustomer.ContactName = "New Contact";
db.SubmitChanges();
Console.WriteLine("Number Updated After: {0}",
db.Customers.Where( c => c.ContactName == "New Contact" ).Count());
}
8. Now press Ctrl+F5 to build and run the application. Notice again the number of contacts with the name “New Contact” changes. Press any key to terminate the application.
Task 6 – Calling Stored Procedures
Using the designer, recall adding a stored procedure along with the tables. In this task you call stored procedures.
1. 使用设计器，请记住添加存储过程以及相关的表。 此任务中您将调用存储过程。Create a new method that prints the results of a call to the Top Most Expensive Products stored procedure that was added to the NorthwindDataContext in the designer:
static void InvokeSproc()
{
var db = new NorthwindDataContext();
foreach (var r in db.Ten_Most_Expensive_Products())
Console.WriteLine(r.TenMostExpensiveProducts + "\t" + r.UnitPrice);
}
2. Now call this method from the Main method:
static void Main(string[] args)
{
InvokeSproc();
}
3. Press Ctrl+F5 to build and run the application. After viewing the results, press any key to terminate the application.
When the database cannot be accessed through dynamic SQL statements, you can use C# 3.0 and LINQ to run stored procedures to access the data.
当不能通过动态 SQL 语句访问数据库时,可以使用 C# 3.0 和 LINQ 来运行存储过程来访问数据。
Task 7 – Expanding Query Expressions
1. So far, the queries demonstrated in this lab have been primarily based on filtering. However, LINQ supports many options for querying data that go beyond simple filtering. For example, to sort customers in London by ContactName, you can use the orderby clause:
到目前为止，试验中的查询演示都主要基于筛选。 但是，LINQ 支持多个选项用于查询的数据超出简单筛选。 例如，要排序的联系人姓名南京客户，可以使用 orderby 子句
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
where c.City == "London"
orderby c.ContactName descending
select new { c.ContactName, c.CustomerID };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.CustomerID, c.ContactName);
}
2. Press Ctrl+F5 to build and run the application. Notice the customers are sorted by the second column, the name column, in a descending manner. Press any key to terminate the application.
3. Continue with different types of queries: write a query that finds the number of customers located in each city. To do this make use of the group by expression.
继续不同类型的排序查询：写一个查找每个城市消费者数量的查询。为此请使用Group by表达式实现。
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
group c by c.City into g
orderby g.Count() ascending
select new { City = g.Key, Count = g.Count() };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.City, c.Count);
}
4. Press Ctrl+F5 to run the application. After viewing the results , press any key to terminate the application.
5. Often when writing queries you want to search through two tables. This is usually performed using a join operation, which is supported in C# 3.0. In ObjectQuery replace the previous query with this one. Recall your query printed out all orders for each customer that lives in London. This time, instead of printing all the orders, print the number of orders per customer.
1. 通常编写查询时要搜索两个表。 这通常执行使用join操作，这在C# 3.0中被支持。 ObjectQuery 中替换以前的查询。 记得查询打印居住在伦敦客户的所有订单。 这次，打印每个客户订单的数，而不是打印所有订单。
static void ObjectQuery()
{
var db = new NorthwindDataContext();
db.Log = Console.Out;
var results = from c in db.Customers
join e in db.Employees on c.City equals e.City
group e by e.City into g
select new { City = g.Key, Count = g.Count() };
foreach (var c in results)
Console.WriteLine("{0}\t{1}", c.City, c.Count);
}
6. Press Ctrl+F5 to run the application. Taking a look at the output, the SQL query generated can also be seen. Press any key to terminate the application.
This example illustrates how a SQL style join can be used when there is no explicit relationship to navigate.
本示例演示了没有显式关系导航时如何使用一个 SQL 样式联接
