光脚丫学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) { }   
    }   
}