一步一步学Linq to sql(八)：继承与关系
前言
为了演示继承与关系，我们创建一个论坛数据库，在数据库中创建三个表：
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();
总结
现在对于继承和关系有了初步的了解和使用，那么接下来就是在项目中的应用了。
示例代码下载链接https://files.cnblogs.com/aehyok/InheritConnection.rar
