論壇表結構
為了演示繼承與關系，我們創建一個論壇數據庫，在數據庫中創建三個表：
1、論壇版塊分類表 dbo.Categories：
字段名 字段類型 可空 備注 CategoryID int not null identity/主鍵 CategoryName varchar(50) not null
2、論壇版塊表 dbo.Boards：
字段名 字段類型 可空 備注 BoardID int not null identity/主鍵 BoardName varchar(50) not null BoardCategory int not null 對應論壇版塊分類表的CategoryID
3、論壇主題表 dbo.Topics：
字段名 字段類型 可空 備注 TopicID int not null identity/主鍵 TopicTitle varchar(50) not null TopicContent varchar(max) not null ParentTopic int null 如果帖子是主題貼這個字段為null，否則就是所屬主題id TopicType tinyint not null 0 – 主題貼 1 – 回復帖
實體繼承的定義
Linq to sql支持實體的單表繼承，也就是基類和派生類都存儲在一個表中。對於論壇來說，帖子有兩種，一種是主題貼，一種是回復帖。那麼，我們就先定義帖子基類：
[Table(Name = "Topics")]
public class Topic
{
[Column(Name = "TopicID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int TopicID { get; set; }
[Column(Name = "TopicType", DbType = "tinyint", CanBeNull = false)]
public int TopicType { get; set; }
[Column(Name = "TopicTitle", DbType = "varchar(50)", CanBeNull = false)]
public string TopicTitle { get; set; }
[Column(Name = "TopicContent", DbType = "varchar(max)", CanBeNull = false)]
public string TopicContent { get; set; }
}
這些實體的定義大家應該很熟悉了。下面，我們再來定義兩個實體繼承帖子基類，分別是主題貼和回復貼：
public class NewTopic : Topic
{
public NewTopic()
{
base.TopicType = 0;
}
}
public class Reply : Topic
{
public Reply()
{
base.TopicType = 1;
}
[Column(Name = "ParentTopic", DbType = "int", CanBeNull = false)]
public int ParentTopic { get; set; }
}
對於主題貼，在數據庫中的TopicType就保存為0，而對於回復貼就保存為1。回復貼還有一個相關字段就是回復所屬主題貼的TopicID。那麼，我們怎麼告知Linq to sql在TopicType為0的時候識別為NewTopic，而1則識別為Reply那？只需稍微修改一下前面的Topic實體定義：
[Table(Name = "Topics")]
[InheritanceMapping(Code = 0, Type = typeof(NewTopic), IsDefault = true)]
[InheritanceMapping(Code = 1, Type = typeof(Reply))]
public class Topic
{
[Column(Name = "TopicID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int TopicID { get; set; }
[Column(Name = "TopicType", DbType = "tinyint", CanBeNull = false, IsDiscriminator = true)]
public int TopicType { get; set; }
[Column(Name = "TopicTitle", DbType = "varchar(50)", CanBeNull = false)]
public string TopicTitle { get; set; }
[Column(Name = "TopicContent", DbType = "varchar(max)", CanBeNull = false)]
public string TopicContent { get; set; }
}
為類加了InheritanceMapping特性定義，0的時候類型就是NewTopic，1的時候就是Reply。並且為TopicType字段上的特性中加了IsDiscriminator = true，告知Linq to sql這個字段就是用於分類的字段。
實體繼承的使用
定義好繼承的實體之後，我們就可以使用了。先是自定義一個DataContext吧：
public partial class BBSContext : DataContext
{
public Table<BoardCategory> BoardCategories;
public Table<Board> Boards;
public Table<Topic> Topics;
public BBSContext(string connection) : base(connection) { }
}
然後，我們來測試一下Linq to sql是否能根據TopicType識別派生類：
BBSContext ctx = new BBSContext("server=xxx;database=BBS;uid=xxx;pwd=xxx");
var query = from t in ctx.Topics select t;
foreach (Topic topic in query)
{
if (topic is NewTopic)
{
NewTopic newtopic = topic as NewTopic;
Response.Write("標題：" + newtopic.TopicTitle + " 類型：" + newtopic.TopicType + "<br/>");
}
else if (topic is Reply)
{
Reply reply = topic as Reply;
Response.Write("標題：" + reply.TopicTitle + " 類型：" + reply.TopicType + " 隸屬主題：" + reply.ParentTopic + "<br/>");
}
}
然後我們往Topics表中加一些數據，如下圖：
啟動程序得到如下測試結果：
當然，你也可以在查詢句法中直接查詢派生實體：
IEnumerable newtopiclist = (from t in ctx.Topics.OfType<NewTopic>() select t).ToList();
newtopics.DataSource = newtopiclist;
IEnumerable replylist = (from t in ctx.Topics.OfType<Reply>() select t).ToList();
replies.DataSource = replylist;
Page.DataBind();
newtopic和replies是兩個GridView控件，執行效果如下圖：
再來看看如何進行增刪操作：
NewTopic nt = new NewTopic() { TopicTitle = "還是新主題", TopicContent = "還是新主題" };
Reply rpl = new Reply() { TopicTitle = "還是新回復", TopicContent = "還是新回復", ParentTopic = 4 };
ctx.Topics.Add(nt);
ctx.Topics.Add(rpl);
ctx.SubmitChanges();
rpl = ctx.Topics.OfType<Reply>().Single(reply => reply.TopicID == 8);
ctx.Topics.Remove(rpl);
ctx.SubmitChanges();
實體關系的定義
比如我們的論壇分類表和論壇版塊表之間就有關系，這種關系是1對多的關系。也就是說一個論壇分類可能有多個論壇版塊，這是很常見的。定義實體關系的優勢在於，我們無須顯式作連接操作就能處理關系表的條件。
首先來看看分類表的定義：
[Table(Name = "Categories")]
public class BoardCategory
{
[Column(Name = "CategoryID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int CategoryID { get; set; }
[Column(Name = "CategoryName", DbType = "varchar(50)", CanBeNull = false)]
public string CategoryName { get; set; }
private EntitySet<Board> _Boards;
[Association(OtherKey = "BoardCategory", Storage = "_Boards")]
public EntitySet<Board> Boards
{
get { return this._Boards; }
set { this._Boards.Assign(value); }
}
public BoardCategory()
{
this._Boards = new EntitySet<Board>();
}
}
CategoryID和CategoryName的映射沒有什麼不同，只是我們還增加了一個Boards屬性，它返回的是Board實體集。通過特性，我們定義了關系外鍵為BoardCategory（Board表的一個字段）。然後來看看1對多，多端版塊表的實體：
[Table(Name = "Boards")]
public class Board
{
[Column(Name = "BoardID", DbType = "int identity", IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
public int BoardID { get; set; }
[Column(Name = "BoardName", DbType = "varchar(50)", CanBeNull = false)]
public string BoardName { get; set; }
[Column(Name = "BoardCategory", DbType = "int", CanBeNull = false)]
public int BoardCategory { get; set; }
private EntityRef<BoardCategory> _Category;
[Association(ThisKey = "BoardCategory", Storage = "_Category")]
public BoardCategory Category
{
get { return this._Category.Entity; }
set
{
this._Category.Entity = value;
value.Boards.Add(this);
}
}
}
在這裡我們需要關聯分類，設置了Category屬性使用BoardCategory字段和分類表關聯。
實體關系的使用
好了，現在我們就可以在查詢句法中直接關聯表了（數據庫中不一定要設置表的外鍵關系）：
Response.Write("-------------查詢分類為1的版塊-------------<br/>");
var query1 = from b in ctx.Boards where b.Category.CategoryID == 1 select b;
foreach (Board b in query1)
Response.Write(b.BoardID + " " + b.BoardName + "<br/>");
Response.Write("-------------查詢版塊大於2個的分類-------------<br/>");
var query2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c;
foreach (BoardCategory c in query2)
Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>");
在數據庫中加一些測試數據，如下圖：
運行程序後得到下圖的結果：
我想定義實體關系的方便我不需要再用語言形容了吧。執行上述的程序會導致下面SQL的執行：SELECT [t0].[BoardID], [t0].[BoardName], [t0].[BoardCategory]
FROM [Boards] AS [t0]
INNER JOIN [Categories] AS [t1] ON [t1].[CategoryID] = [t0].[BoardCategory]
WHERE [t1].[CategoryID] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
SELECT [t0].[CategoryID], [t0].[CategoryName]
FROM [Categories] AS [t0]
WHERE ((
SELECT COUNT(*)
FROM [Boards] AS [t1]
WHERE [t1].[BoardCategory] = [t0].[CategoryID]
)) > @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
SELECT [t0].[BoardID], [t0].[BoardName], [t0].[BoardCategory]
FROM [Boards] AS [t0]
WHERE [t0].[BoardCategory] = @p0
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
可以看到，第二個查詢並沒有做外連接，還記得DataLoadOptions嗎？我們可以要求Linq to sql在讀取版塊分類信息的時候也把版塊信息一起加載：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<BoardCategory>(c => c.Boards);
ctx.LoadOptions = options;
Response.Write("-------------查詢版塊大於2個的分類-------------<br/>");
var query2 = from c in ctx.BoardCategories where c.Boards.Count > 2 select c;
foreach (BoardCategory c in query2)
Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>");
查詢經過改造後會得到下面的SQL：SELECT [t0].[CategoryID], [t0].[CategoryName], [t1].[BoardID], [t1].[BoardName], [t1].[BoardCategory], (
SELECT COUNT(*)
FROM [Boards] AS [t3]
WHERE [t3].[BoardCategory] = [t0].[CategoryID]
) AS [count]
FROM [Categories] AS [t0]
LEFT OUTER JOIN [Boards] AS [t1] ON [t1].[BoardCategory] = [t0].[CategoryID]
WHERE ((
SELECT COUNT(*)
FROM [Boards] AS [t2]
WHERE [t2].[BoardCategory] = [t0].[CategoryID]
)) > @p0
ORDER BY [t0].[CategoryID], [t1].[BoardID]
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
在添加分類的時候，如果這個分類下還有新的版塊，那麼提交新增分類的時候版塊也會新增：
BoardCategory dbcat = new BoardCategory() { CategoryName = "Database" };
Board oracle = new Board() { BoardName = "Oracle", Category = dbcat};
ctx.BoardCategories.Add(dbcat);
ctx.SubmitChanges();
上述代碼導致下面的SQL被執行：
INSERT INTO [Categories]([CategoryName]) VALUES (@p0)
SELECT [t0].[CategoryID]
FROM [Categories] AS [t0]
WHERE [t0].[CategoryID] = (SCOPE_IDENTITY())
-- @p0: Input AnsiString (Size = 8; Prec = 0; Scale = 0) [Database]
INSERT INTO [Boards]([BoardName], [BoardCategory]) VALUES (@p0, @p1)
SELECT [t0].[BoardID]
FROM [Boards] AS [t0]
WHERE [t0].[BoardID] = (SCOPE_IDENTITY())
-- @p0: Input AnsiString (Size = 6; Prec = 0; Scale = 0) [Oracle]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [23]
今天就講到這裡。大家可以自己嘗試為帖子表也定義實體的關系，因為，是不是可以直接通過帖子獲取帖子下的回復，或者直接通過回復得到所屬帖子那？
