一步一步学Linq to sql（八）：继承与关系

最新推荐文章于 2024-08-09 21:25:57 发布

amkr21253

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量86

点赞数

文章标签：数据库人工智能

原文链接：http://www.cnblogs.com/IT-Bear/articles/Linq8.html

版权

转自：http://kb.cnblogs.com/page/42688/

论坛表结构

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

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表中加一些数据，如下图：

启动程序得到如下测试结果：

当然，你也可以在查询句法中直接查询派生实体：

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]

系列文章

转自：http://kb.cnblogs.com/page/42688/

本系列文章导航

一步一步学Linq to sql（一）：预备知识

一步一步学Linq to sql（二）：DataContext与实体

一步一步学Linq to sql（三）：增删改

一步一步学Linq to sql（四）：查询句法

一步一步学Linq to sql（五）：存储过程

一步一步学Linq to sql（六）：探究特性

一步一步学Linq to sql（七）：并发与事务

一步一步学Linq to sql（八）：继承与关系

一步一步学Linq to sql（九）：其它补充

一步一步学Linq to sql（十）：分层构架的例子

转载于:https://www.cnblogs.com/IT-Bear/articles/Linq8.html

amkr21253

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一步一步学Linq to sql（八）：继承与关系

转自：http://kb.cnblogs.com/page/42688/论坛表结构为了演示继承与关系，我们创建一个论坛数据库，在数据库中创建三个表：1、论坛版块分类表dbo.Categories：字段名字段类型可空备注CategoryIDintnot null...
复制链接

扫一扫