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

最新推荐文章于 2024-10-30 11:02:01 发布

George_Fal

最新推荐文章于 2024-10-30 11:02:01 发布

阅读量105

点赞数

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

前言

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

View Code

create DataBase ForumDataBase
go

--论坛版块分类表
create table Categories
(
    CategoryID int identity(1,1) not null primary key,
    CategoryName varchar(50) not null
)

select * from Categories
insert into Categories values('手机')
insert into Categories values('电脑')
insert into Categories values('生活')

--论坛版块表
create table Boards
(
    BoardID int identity(1,1) not null primary key,
    BoardName varchar(50) not null,
    BoardCategory int not null       ---对应论坛版块分类表的CategoryID
)

insert into Boards values('Nokia',1)
insert into Boards values('Iphone',1)

---论坛主题表
create table Topics
(
    TopicID int identity(1,1) not null primary key,
    TopicTitle varchar(50) not null,
    TopicContent varchar(max) not null,
    ParentTopic int null,
    TopicType tinyint not null   --0主题帖   1回复帖
)

drop table Topics

insert into Topics 
values('Nokia 820首发','1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111',null,0)

insert into Topics values('111','22222222222222222222222222222222222222222222222222222222',1,1)

实体继承的定义

直接上代码：

namespace InheritConnection
{
    [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 { set; get; }

        [Column(Name = "TopicType", DbType = "tinyint", CanBeNull = false, IsDiscriminator = true)]
        public int TopicType { set; get; }

        [Column(Name = "TopicTitle", DbType = "varchar(50)", CanBeNull = false)]
        public string TopicTitle { set; get; }

        [Column(Name = "TopicContent", DbType = "varchar(max)", CanBeNull = false)]
        public string TopicContent { set; get; }
    }

    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; }
    }
}

1.首先定义的是Topic实体基类，然后两个子类的继承，NewTopic--主题帖，Reply--回复帖。
2.Topic类上的特性，下面先来看一下特性类

View Code

    // 摘要:
    //     映射 LINQ to SQL 应用程序中的继承层次结构。
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = true, Inherited = false)]
    public sealed class InheritanceMappingAttribute : Attribute
    {
        // 摘要:
        //     初始化 System.Data.Linq.Mapping.InheritanceMappingAttribute 类的一个新实例。
        [TargetedPatchingOptOut("Performance critical to inline this type of method across NGen image boundaries")]
        public InheritanceMappingAttribute();

        // 摘要:
        //     获取或设置映射的继承层次结构中的鉴别器代码值。
        //
        // 返回结果:
        //     必须由用户指定。没有默认值。
        public object Code { get; set; }
        //
        // 摘要:
        //     获取或设置一个值，该值指示当鉴别器值与指定值不匹配时是否实例化此类型的对象。
        //
        // 返回结果:
        //     Default = false.
        public bool IsDefault { get; set; }
        //
        // 摘要:
        //     获取或设置层次结构中类的类型。
        //
        // 返回结果:
        //     必须由用户指定。没有默认值。
        public Type Type { get; set; }
    }

通过type指定子类。

实体继承的使用

定义好继承的实体之后，我们就可以使用了。先是自定义一个DataContext吧：

namespace InheritConnection
{
    public 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识别派生类.

1.先来定义一个获取数据库链接配置的字符串常量

public static string ConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

2.看一下在配置文件中定义的数据库链接字符串

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=MWVCMNFFK0ZAZT5;Initial Catalog=ForumDataBase;Persist Security Info=True;User ID=sa;Password=saa;Pooling=False"
  providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

3.下面就通过如下代码进行测试一下

        static void Main(string[] args)
        {
            BBSContext ctx = new BBSContext(ConnString);
            var query = from t in ctx.Topics select t;

            foreach (Topic topic in query)
            {

                if (topic is NewTopic)
                {
                    NewTopic newtopic = topic as NewTopic;
                    Console.WriteLine("标题：" + newtopic.TopicTitle + " 类型：" + newtopic.TopicType);
                }

                else if (topic is Reply)
                {
                    Reply reply = topic as Reply;
                    Console.WriteLine("标题：" + reply.TopicTitle + " 类型：" + reply.TopicType + " 隶属主题：" + reply.ParentTopic);
                }
            }
            Console.WriteLine("SQL");
            Console.ReadLine();
        }

这是预先插入好的数据

现在可以F5运行查看结果

结果很给力，与我们查询分析器插入的数据保持一致。

再来看看如何进行增删操作：

            NewTopic nt = new NewTopic() { TopicTitle = "还是新主题", TopicContent = "还是新主题"};
            Reply rpl = new Reply() { TopicTitle = "还是新回复", TopicContent = "还是新回复", ParentTopic = 1 };
            ctx.Topics.InsertOnSubmit(nt);    ///添加       
            ctx.Topics.InsertOnSubmit(rpl);
            ctx.SubmitChanges();
            rpl = ctx.Topics.OfType<Reply>().Single(reply => reply.TopicID == 3);  
            ctx.Topics.DeleteOnSubmit(rpl);   //删除
            ctx.SubmitChanges();

            var query1 = from t in ctx.Topics select t;
            foreach (Topic topic in query1)
            {

                if (topic is NewTopic)
                {
                    NewTopic newtopic = topic as NewTopic;
                    Console.WriteLine("标题：" + newtopic.TopicTitle + " 类型：" + newtopic.TopicType);
                }

                else if (topic is Reply)
                {
                    Reply reply = topic as Reply;
                    Console.WriteLine("标题：" + reply.TopicTitle + " 类型：" + reply.TopicType + " 隶属主题：" + reply.ParentTopic);
                }
            }

添加：先插入一条主题帖子，然后又插入一条回复帖。
删除：通过TopicID插入一条记录，然后删除已经之前的记录。

然后再次通过查询查看数据结果。

实体关系的定义

比如我们的论坛分类表和论坛版块表之间就有关系，这种关系是1对多的关系。也就是说一个论坛分类可能有多个论坛版块，这是很常见的。定义实体关系的优势在于，我们无须显式作连接操作就能处理关系表的条件。

首先来看看分类表的定义：

namespace InheritConnection
{
    [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对多，多端版块表的实体：

namespace InheritConnection
{
    [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字段和分类表关联。

实体关系的使用

好了，现在我们就可以在查询句法中直接关联表了（数据库中不一定要设置表的外键关系）：

            /////查看分类
            var queryBoard = from b in ctx.Boards where b.Category.CategoryID == 1 select b;

            foreach (Board b in queryBoard)

                Console.WriteLine(b.BoardID + " " + b.BoardName);


            Console.WriteLine("查看版块为两个以上的分类（包含两个）");
            var query2 = from c in ctx.BoardCategories where c.Boards.Count >= 2 select c;

            foreach (BoardCategory c in query2)

                Console.WriteLine(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count);

            Console.ReadLine();

下面是数据库中的测试数据

这是F5执行后的结果

可以看出上面并没有使用什么两个表之间的连接。

在添加分类的时候，如果这个分类下还有新的版块，那么提交新增分类的时候版块也会新增：

            ////添加分类
            BoardCategory dbcat = new BoardCategory() { CategoryName = "三星" };

            Board oracle = new Board() { BoardName = "小三", Category = dbcat };

            ctx.BoardCategories.InsertOnSubmit(dbcat);

            ctx.SubmitChanges();