vainnetwork的专栏

共同学习进步

一步一步学Linq to sql(八):继承与关系
 本系列课程均转自

LoveCherry

技术无极限http://www.cnblogs.com/lovecherry/archive/2007/08/14/855681.html

版权归LoveCherry所有

论坛表结构

 

       为了演示继承与关系,我们创建一个论坛数据库,在数据库中创建三个表:

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 sqlTopicType0的时候识别为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的时候类型就是NewTopic1的时候就是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();

       newtopicreplies是两个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>();

    }

}

       CategoryIDCategoryName的映射没有什么不同,只是我们还增加了一个Boards属性,它返回的是Board实体集。通过特性,我们定义了关系外键为BoardCategoryBoard表的一个字段)。然后来看看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]

阅读更多
个人分类: LINQ系列学习
想对作者说点什么? 我来说一句

LINQ中文教程

2013年02月19日 876KB 下载

LINQ中文教程word格式

2010年12月22日 855KB 下载

LINQ详细教程

2014年01月28日 865KB 下载

VS2008 LINQ基础教程

2011年03月07日 858KB 下载

LINQ中文教程LINQ中文教程

2011年03月22日 833KB 下载

一步一步学 Linq to sql

2010年01月29日 850KB 下载

一步一步学LINQ to sql

2009年07月27日 2.64MB 下载

没有更多推荐了,返回首页

不良信息举报

一步一步学Linq to sql(八):继承与关系

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭