Linq to Sql学习总结6

单表继承

继承实体定义：

   //linq to sql支持实体单表继承，即某一实体类（具有映射关系的类）可以派生多个子类，子类不用再通过特性映射基类的关系数据
    //子类对基类实体进行分类，通过特性InheritanceMapping对基类实体分类
    //基类实体以某一成员属性作为分类依据（IsDiscriminator）
    //应用场景：对于论坛来说，帖子有两种，一种是主题贴，一种是回复帖。
    [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 = "TopicTitle", DbType = "varchar(50)", CanBeNull = false)]
        public string TopicTitle { get; set; }
        [Column(Name = "TopicType", DbType = "tinyint", CanBeNull = false, IsDiscriminator = true)]
        public int TopicType { 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; }
    }

派生实体的使用：

          //查询基类实体
            var topic = from t in bbs.Topics select t;
            foreach (Topic t in topic)
            {
                if (t is NewTopic)
                {
                    //将基类实体转换为派生类实体
                       NewTopic newTopic = t as NewTopic;
                    Response.Write("标题：" + newTopic.TopicTitle + " 类型：" + newTopic.TopicType + "<br/>");
                }
                else if (t is Reply)
                {
                    Reply reply = t as Reply;
                    Response.Write("标题：" + reply.TopicTitle + " 类型：" + reply.TopicType + " 隶属主题：" + reply.ParentTopic + "<br/>");
                }
            }

            //直接查询派生类实体
              IEnumerable q1 = (from t in bbs.Topics.OfType<NewTopic>() select t).ToList();
            IEnumerable q2 = (from t in bbs.Topics.OfType<Reply>() select t).ToList();

            //对派生类实体进行增删改
              NewTopic nt = new NewTopic() { TopicTitle = "还是新主题", TopicContent = "还是新主题" };
            Reply rpl = new Reply() { TopicTitle = "还是新回复", TopicContent = "还是新回复", ParentTopic = 4 };

            bbs.Topics.InsertOnSubmit(nt);
            bbs.Topics.InsertOnSubmit(rpl);
            bbs.SubmitChanges();

            Reply rp = bbs.Topics.OfType<Reply>().Single(r => r.TopicID == 8);
            bbs.Topics.DeleteOnSubmit(rp);
            bbs.SubmitChanges();

实体关系定义

通过定义实体之间的关系，可以不用在对应的关系数据表之间建立外键关系

具有关系的实体定义：

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

       //定义一个私有存储字段存储属性的值，EntitySet表示需要关联的实体集合
        private EntitySet<Board> _boards;
        //OtherKey指定要关联的（需要延迟加载的）的实体类的成员，通过该成员与指定实体类关联
        [Association(OtherKey = "BoardCategory", Storage = "_boards")]
        public EntitySet<Board> Boards
        {
            set { this._boards.Assign(value); }
            get { return this._boards; }
        }

        public BoardCategory()
        {
            this.Boards = new EntitySet<Board>();
        }
    }

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

        //EntityRef表示关联当前实体类的实体引用
        private EntityRef<BoardCategory> _Category;
        //TisKey指定当前实体类的一个成员，用于延迟加载关联当前实体类的实体
        [Association(ThisKey = "BoardCategory", Storage = "_Category")]
        public BoardCategory Category
        {
            get { return this._Category.Entity; }
            set 
            {
                this._Category.Entity = value;
                value.Boards.Add(this);
            }
        }
    }

关系实体类的应用：

            var query1 = from b in bbs.Boards where b.Category.CategoryID == 1 select b;
            var query2 = from c in bbs.Categories where c.Boards.Count() > 2 select c;

            //在添加分类的时候，如果这个分类下还有新的版块，那么提交新增分类的时候版块也会新增
              BoardCategory dbcat = new BoardCategory() { CategoryName = "Database" };
            Board oracle = new Board() { BoardName = "Oracle", Category = dbcat };
            bbs.Categories.InsertOnSubmit(dbcat);
            bbs.SubmitChanges();

关系实体结合DataLoadOptions使用，查询句法生成的sql会得到优化，从而提高查询性能：

            DataLoadOptions options = new DataLoadOptions();
            options.LoadWith<BoardCategory>(c => c.Boards);
            bbs.LoadOptions = options;
            Response.Write("-------------查询版块大于2个的分类-------------<br/>");
            var query2 = from c in bbs.Categories where c.Boards.Count > 2 select c;
            foreach (BoardCategory c in query2)
            {
                Response.Write(c.CategoryID + " " + c.CategoryName + " " + c.Boards.Count + "<br/>");
            }