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