单表继承
继承实体定义:
//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/>"); }