• 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/>");
                }
    
  • 相关阅读:
    Spring学习4_整合Hibernate进行数据库操作
    spring学习3_通过注解简单实现AOP
    Spring学习2_AOP通过XML配置简单实现
    Spring学习1_面向切面( AOP )实现原理
    hibernate学习6_session之clear与flush
    hibernate学习5_session之load与get区别
    hibernate学习4_openSession()与getCurrentSession()区别
    utf8汉字编码16进制对照
    Debug
    服务器设计过程中踩过的坑儿
  • 原文地址:https://www.cnblogs.com/JDotNet/p/3308924.html
Copyright © 2020-2023  润新知