論壇表結(jié)構(gòu)
為了演示繼承與關(guān)系，我們創(chuàng)建一個(gè)論壇數(shù)據(jù)庫，在數(shù)據(jù)庫中創(chuàng)建三個(gè)表：
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
對應(yīng)論壇版塊分類表的CategoryID
3、 論壇主題表 dbo.Topics：
字段名
字段類型
可空
備注
TopicID
int
not null
identity/主鍵
TopicTitle
varchar(50)
not null
TopicContent
varchar(max)
not null
ParentTopic
int
null
如果帖子是主題貼這個(gè)字段為null，否則就是所屬主題id
TopicType
tinyint
not null
0 – 主題貼
1 – 回復(fù)帖
實(shí)體繼承的定義
Linq to sql支持實(shí)體的單表繼承，也就是基類和派生類都存儲(chǔ)在一個(gè)表中。對于論壇來說，帖子有兩種，一種是主題貼，一種是回復(fù)帖。那么，我們就先定義帖子基類：
[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; }
}
這些實(shí)體的定義大家應(yīng)該很熟悉了。下面，我們再來定義兩個(gè)實(shí)體繼承帖子基類，分別是主題貼和回復(fù)貼：
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; }
}
對于主題貼，在數(shù)據(jù)庫中的TopicType就保存為0，而對于回復(fù)貼就保存為1?；貜?fù)貼還有一個(gè)相關(guān)字段就是回復(fù)所屬主題貼的TopicID。那么，我們怎么告知Linq to sql在TopicType為0的時(shí)候識別為NewTopic，而1則識別為Reply那？只需稍微修改一下前面的Topic實(shí)體定義：
[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的時(shí)候類型就是NewTopic，1的時(shí)候就是Reply。并且為TopicType字段上的特性中加了IsDiscriminator = true，告知Linq to sql這個(gè)字段就是用于分類的字段。
實(shí)體繼承的使用
定義好繼承的實(shí)體之后，我們就可以使用了。先是自定義一個(gè)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是否能根據(jù)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("標(biāo)題：" + newtopic.TopicTitle + " 類型：" + newtopic.TopicType + "<br/>");
}
else if (topic is Reply)
{
Reply reply = topic as Reply;
Response.Write("標(biāo)題：" + reply.TopicTitle + " 類型：" + reply.TopicType + " 隸屬主題：" + reply.ParentTopic + "<br/>");
}
}
然后我們往Topics表中加一些數(shù)據(jù)，如下圖：
啟動(dòng)程序得到如下測試結(jié)果：
當(dāng)然，你也可以在查詢句法中直接查詢派生實(shí)體：
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是兩個(gè)GridView控件，執(zhí)行效果如下圖：
再來看看如何進(jìn)行增刪操作：
NewTopic nt = new NewTopic() { TopicTitle = "還是新主題", TopicContent = "還是新主題" };
Reply rpl = new Reply() { TopicTitle = "還是新回復(fù)", TopicContent = "還是新回復(fù)", 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();
實(shí)體關(guān)系的定義
比如我們的論壇分類表和論壇版塊表之間就有關(guān)系，這種關(guān)系是1對多的關(guān)系。也就是說一個(gè)論壇分類可能有多個(gè)論壇版塊，這是很常見的。定義實(shí)體關(guān)系的優(yōu)勢在于，我們無須顯式作連接操作就能處理關(guān)系表的條件。
首先來看看分類表的定義：
[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的映射沒有什么不同，只是我們還增加了一個(gè)Boards屬性，它返回的是Board實(shí)體集。通過特性，我們定義了關(guān)系外鍵為BoardCategory（Board表的一個(gè)字段）。然后來看看1對多，多端版塊表的實(shí)體：
[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);
}
}
}
在這里我們需要關(guān)聯(lián)分類，設(shè)置了Category屬性使用BoardCategory字段和分類表關(guān)聯(lián)。
實(shí)體關(guān)系的使用
好了，現(xiàn)在我們就可以在查詢句法中直接關(guān)聯(lián)表了（數(shù)據(jù)庫中不一定要設(shè)置表的外鍵關(guān)系）：
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個(gè)的分類-------------<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/>");
在數(shù)據(jù)庫中加一些測試數(shù)據(jù)，如下圖：
運(yùn)行程序后得到下圖的結(jié)果：
我想定義實(shí)體關(guān)系的方便我不需要再用語言形容了吧。執(zhí)行上述的程序會(huì)導(dǎo)致下面SQL的執(zhí)行：
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]
可以看到，第二個(gè)查詢并沒有做外連接，還記得DataLoadOptions嗎？我們可以要求Linq to sql在讀取版塊分類信息的時(shí)候也把版塊信息一起加載：
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<BoardCategory>(c => c.Boards);
ctx.LoadOptions = options;
Response.Write("-------------查詢版塊大于2個(gè)的分類-------------<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/>");
查詢經(jīng)過改造后會(huì)得到下面的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]
在添加分類的時(shí)候，如果這個(gè)分類下還有新的版塊，那么提交新增分類的時(shí)候版塊也會(huì)新增：
BoardCategory dbcat = new BoardCategory() { CategoryName = "Database" };
Board oracle = new Board() { BoardName = "Oracle", Category = dbcat};
ctx.BoardCategories.Add(dbcat);
ctx.SubmitChanges();
上述代碼導(dǎo)致下面的SQL被執(zhí)行：
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]
今天就講到這里。大家可以自己嘗試為帖子表也定義實(shí)體的關(guān)系，因?yàn)?，是不是可以直接通過帖子獲取帖子下的回復(fù)，或者直接通過回復(fù)得到所屬帖子那？
0
0
0(請您對文章做出評價(jià))
