光腳丫學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; }       }   }  [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); }   }  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);   }  // 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) { }   }  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();  // 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();           }       }   }  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) { }       }   }