光脚丫学LINQ(017)：[演练]跨关系查询(C#)：手动编码

视频演示：http://u.115.com/file/f2e338988d

 

本演练演示如何使用 LINQ to SQL 关联来表示数据库中的外键关系。

此演示是建立在前一个演示的基础上。光脚丫学LINQ(016)：[演练]创建简单对象模型和LINQ查询(C#)，你可以通过这个链接转到前一个演示。

跨表映射关系
在 Customer 类定义的后面，创建包含如下代码的 Order 实体类定义，这些代码表示 Order.Customer 作为外键与 Customer.CustomerID 相关。
在 Customer 类后面键入或粘贴如下代码：
[Table(Name = "Orders")]public class Order{ private int _OrderID = 0; private string _CustomerID; private EntityRef <Customer> _Customer; public Order() { this._Customer = new EntityRef<Customer>(); } [Column(Storage = "_OrderID", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)] public int OrderID { get { return this._OrderID; } // No need to specify a setter because IsDBGenerated is // true. } [Column(Storage = "_CustomerID", DbType = "NChar(5)")] public string CustomerID { get { return this._CustomerID; } set { this._CustomerID = value; } } [Association (Storage = "_Customer", ThisKey = "CustomerID")] public Customer Customer { get { return this._Customer.Entity; } set { this._Customer.Entity = value; } }}

 

对Customer类进行批注
在此步骤中，您要对 Customer 类进行批注，以指示它与 Order 类的关系。 （这种添加批注的操作并非绝对必需的，因为定义任一方向上的关系都足以满足创建链接的需要。 但添加此批注确实便于您在任一方向上定位对象。）
将下面的代码键入或粘贴到 Customer 类中：
private EntitySet <Order> _Orders;public Customer(){ this._Orders = new EntitySet<Order>();}[Association(Storage = "_Orders", OtherKey = "CustomerID")]public EntitySet<Order> Orders{ get { return this._Orders; } set { this._Orders.Assign(value); }}

跨 Customer-Order 关系创建并运行查询
现在您可以直接从 Customer 对象访问 Order 对象，或反过来进行访问。 您不需要在客户和订单之间具有显式联接。
使用Customer对象访问Order对象
1、 通过将下面的代码键入或粘贴到 Main 方法中修改此方法：
// Query for customers who have placed orders.var CustomersHasOrders = from CustomerObject in Customers where CustomerObject.Orders.Any() select CustomerObject;foreach (var CustomerObject in CustomersHasOrders){ Console.WriteLine("ID={0}, Qty={1}", CustomerObject.CustomerID, CustomerObject.Orders.Count);}

 

2、 按 F5 调试应用程序。
 说明
您可以通过注释掉 db.Log = Console.Out; 来消除控制台窗口中的 SQL 代码。
3、 在控制台窗口中按 Enter，以停止调试。

 

创建数据库的强类型化视图
从数据库的强类型化视图着手要容易得多。 通过将 DataContext 对象强类型化，您无需调用 GetTable。 当您使用强类型化的 DataContext 对象时，您可以在所有查询中使用强类型化表。
在以下步骤中，您将创建 Customers 作为映射到数据库中的 Customers 表的强类型化表。
对 DataContext 对象进行强类型化
1、 将下面的代码添加到 Customer 类声明的上方。
public class Northwind : DataContext{ // Table<T> abstracts database details per table/data type. public Table<Customer> Customers; public Table<Order> Orders; public Northwind(string connection) : base(connection) { }}

 

2、 将 Main 方法修改为使用强类型化的 DataContext，如下所示：
// Use a connection string.Northwind db = new Northwind(@"C:/linqtest5/Northwind.mdf");// Query for customers from Seattle. var SeattleCustomers = from CustomerObject in db.Customers where CustomerObject.City == "Seattle" select CustomerObject;foreach (var CustomerObject in SeattleCustomers){ Console.WriteLine("ID={0}", CustomerObject.CustomerID);}// Freeze the console window.Console.ReadLine();

 

 

完整的演示代码如下：

Program.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.Linq;using System.Data.Linq.Mapping;namespace Demo02{ [Table(Name = "Customers")] public class Customer { private string _CustomerID; [Column(IsPrimaryKey = true, Storage = "_CustomerID")] public string CustomerID { get { return this._CustomerID; } set { this._CustomerID = value; } } private string _City; [Column(Storage = "_City")] public string City { get { return this._City; } set { this._City = value; } } private EntitySet<Order> _Orders; public Customer() { this._Orders = new EntitySet<Order>(); } [Association(Storage = "_Orders", ThisKey = "CustomerID", OtherKey = "CustomerID")] public EntitySet<Order> Orders { get { return this._Orders; } set { this._Orders.Assign(value); } } } [Table(Name = "Orders")] public class Order { private int _OrderID = 0; private string _CustomerID; private EntityRef<Customer> _Customer; public Order() { this._Customer = new EntityRef<Customer>(); } [Column(Storage = "_OrderID", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)] public int OrderID { get { return this._OrderID; } // No need to specify a setter because IsDBGenerated is // true. } [Column(Storage = "_CustomerID", DbType = "NChar(5)")] public string CustomerID { get { return this._CustomerID; } set { this._CustomerID = value; } } [Association(Storage = "_Customer", ThisKey = "CustomerID", OtherKey = "CustomerID")] public Customer Customer { get { return this._Customer.Entity; } set { this._Customer.Entity = value; } } } class Program { static void Main(string[] args) { // ************************************************* // 跨关系查询。 // ************************************************* // Use a connection string. DataContext db = new DataContext(@"C:/linqtest5/Northwind.mdf"); // Get a typed table to run queries. Table<Customer> Customers = db.GetTable<Customer>(); // Attach the log to show generated SQL. //db.Log = Console.Out; // Query for customers who have placed orders. var CustomersHasOrders = from CustomerObject in Customers where CustomerObject.Orders.Any() select CustomerObject; foreach (var CustomerObject in CustomersHasOrders) { Console.WriteLine("ID={0}, Qty={1}", CustomerObject.CustomerID, CustomerObject.Orders.Count); //Console.WriteLine(CustomerObject.Orders[0].Customer.CustomerID); } // Prevent console window from closing. Console.ReadLine(); // ************************************************* // 使用强类型的数据上下文。 // ************************************************* // Use a connection string. //Northwind db = new Northwind(@"C:/linqtest5/Northwind.mdf"); //// Query for customers from Seattle. //var SeattleCustomers = // from CustomerObject in db.Customers // where CustomerObject.City == "Seattle" // select CustomerObject; //foreach (var CustomerObject in SeattleCustomers) //{ // Console.WriteLine("ID={0}", CustomerObject.CustomerID); //} //// Freeze the console window. //Console.ReadLine(); } }}

 

Northwind.cs

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.Linq;namespace Demo02{ public class Northwind : DataContext { // Table<T> abstracts database details per table/data type. public Table<Customer> Customers; public Table<Order> Orders; public Northwind(string connection) : base(connection) { } }}