Entity Framework技术系列之4：灵活应用实体数据模型
前言
上一篇文章中，我详细解析了使用Database First和Model First开发模式自动生成的Entity Framework实体数据模型的内容结构，如下图所示：
图1实体数据模型内容结构图
本文将就实体数据模型内容作文章，将其拆分为多个文件，并作适当的调整，实现一个DIY的实体数据模型，其内容结构如下图所示：
图2调整后的实体数据模型内容结构图
做这些调整的目的有两个，其一是使用POCO替换实体数据模型中繁琐的实体类，使代码更整洁并易于维护；其二是使程序支持多种数据库，并且易于扩展。
E-R模型
E-R模型继续沿用上一篇文章中使用的模型，如下图所示：
图3 E-R模型
对应的SQL Server 数据库DDL语句如下：
1 --创建表 2 CREATE TABLE [Role]( 3 [ID] [uniqueidentifier] NOT NULL, 4 [Name] [nvarchar](40) NOT NULL, 5 [ParentID] [uniqueidentifier] NULL) 6 GO 7 8 CREATE TABLE [User]( 9 [ID] [uniqueidentifier] NOT NULL, 10 [Account] [nvarchar](50) NOT NULL, 11 [Password] [varchar](50) NOT NULL) 12 GO 13 14 CREATE TABLE [UserDetail]( 15 [ID] [uniqueidentifier] NOT NULL, 16 [Name] [nvarchar](50) NOT NULL, 17 [Sex] [nvarchar](2) NULL, 18 [Birthday] [datetime] NULL) 19 GO 20 21 CREATE TABLE [UserRole]( 22 [UserID] [uniqueidentifier] NOT NULL, 23 [RoleID] [uniqueidentifier] NOT NULL) 24 GO 25 26 --创建主键 27 ALTER TABLE [Role] 28 ADD CONSTRAINT [PK_Role] 29 PRIMARY KEY CLUSTERED ([ID] ASC); 30 GO 31 32 ALTER TABLE [User] 33 ADD CONSTRAINT [PK_User] 34 PRIMARY KEY CLUSTERED ([ID] ASC); 35 GO 36 37 ALTER TABLE [UserDetail] 38 ADD CONSTRAINT [PK_UserDetail] 39 PRIMARY KEY CLUSTERED ([ID] ASC); 40 GO 41 42 ALTER TABLE [UserRole] 43 ADD CONSTRAINT [PK_UserRole] 44 PRIMARY KEY CLUSTERED ([UserID] ASC, RoleID ASC); 45 GO 46 47 --创建外键 48 ALTER TABLE [Role] WITH CHECK ADD CONSTRAINT [FK_Role_Role] FOREIGN KEY([ParentID]) 49 REFERENCES [Role] ([ID]) 50 GO 51 52 ALTER TABLE [UserDetail] WITH CHECK ADD CONSTRAINT [FK_UserDetail_User] FOREIGN KEY([ID]) 53 REFERENCES [User] ([ID]) 54 ON DELETE CASCADE 55 GO 56 57 ALTER TABLE [UserRole] WITH CHECK ADD CONSTRAINT [FK_UserRole_User] FOREIGN KEY([UserID]) 58 REFERENCES [User] ([ID]) 59 ON DELETE CASCADE 60 GO 61 62 ALTER TABLE [UserRole] WITH CHECK ADD CONSTRAINT [FK_UserRole_Role] FOREIGN KEY([RoleID]) 63 REFERENCES [Role] ([ID]) 64 ON DELETE CASCADE 65 GO 66 67 --创建储存过程 68 --SQL Server自关联表无法通过外键设置级联删除，所以专门写一个触发器来完成该工作 69 CREATE TRIGGER TRG_Role_Delete 70 ON [Role] 71 INSTEAD OF DELETE 72 AS 73 BEGIN 74 -- SET NOCOUNT ON added to prevent extra result sets from 75 -- interfering with SELECT statements. 76 SET NOCOUNT ON; 77 78 DECLARE @ID uniqueidentifier; 79 SELECT @ID = ID FROM deleted; 80 81 DELETE [Role] WHERE ParentID = @ID; 82 DELETE [Role] WHERE ID = @ID; 83 END 84 GO
使用SQL Server Management Studio工具创建名为“Membership”的数据库，并执行以上的DDL语句，建立本示例所需的数据库对象。
DIY实体数据模型
接下来将对前文中的实体数据模型内容进行灵活的调整，使其支持POCO和多数据库，调整的内容包括：
- 将Membership.Designer.cs文件中的上下文环境类放入单独的EntityContext.cs文件中，并只保留构造函数和实体集属性两部分内容；
- 直接定义Role、User和UserDetail三个POCO实体类；
- 将Membership.edmx文件中CSDL、MSL和SSDL的内容分别放入Membership.csdl、Membership.msl和Membership.SqlServer.ssdl三个XML文件中；
- 复制Membership.SqlServer.ssdl文件为Membership.MySQL.ssdl文件，并依据MySQL数据库SQL方言，替换相应的数据类型。
一、实体上下文环境
1 public class EntityContext : ObjectContext 2 { 3 public EntityContext() : this("name=Membership") 4 { 5 this.ContextOptions.LazyLoadingEnabled = true; 6 } 7 8 public EntityContext(string connectionString) : base(connectionString, "Membership") 9 { 10 this.ContextOptions.LazyLoadingEnabled = true; 11 } 12 13 public EntityContext(EntityConnection connection) : base(connection, "Membership") 14 { 15 this.ContextOptions.LazyLoadingEnabled = true; 16 } 17 18 private ObjectSet<Role> roles; 19 public ObjectSet<Role> Roles 20 { 21 get 22 { 23 if ((roles == null)) 24 roles = base.CreateObjectSet<Role>("Roles"); 25 26 return roles; 27 } 28 } 29 30 private ObjectSet<User> users; 31 public ObjectSet<User> Users 32 { 33 get 34 { 35 if ((users == null)) 36 { 37 users = base.CreateObjectSet<User>("Users"); 38 } 39 return users; 40 } 41 } 42 43 private ObjectSet<UserDetail> userDetails; 44 public ObjectSet<UserDetail> UserDetails 45 { 46 get 47 { 48 if ((userDetails == null)) 49 { 50 userDetails = base.CreateObjectSet<UserDetail>("UserDetails"); 51 } 52 return userDetails; 53 } 54 } 55 }
二、实体类
Role实体类：
1 public class Role 2 { 3 public Guid ID { get; set; } 4 5 public string Name { get; set; } 6 7 public Guid? ParentID { get; set; } 8 9 public Role Parent { get; set; } 10 11 public IList<Role> Children { get; set; } 12 13 public IList<User> Users { get; set; } 14 }
User实体类：
1 public class User 2 { 3 public Guid ID { get; set; } 4 5 public string Account { get; set; } 6 7 public string Password { get; set; } 8 9 public UserDetail UserDetail { get; set; } 10 11 public IList<Role> Roles { get; set; } 12 }
UserDetail实体类：
1 public class UserDetail 2 { 3 public Guid ID { get; set; } 4 5 public string Name { get; set; } 6 7 public string Sex { get; set; } 8 9 public DateTime Birthday { get; set; } 10 11 public User User { get; set; } 12 }
三、CSDL
1 <?xml version="1.0" encoding="utf-8" ?> 2 <Schema Namespace="MembershipModel" Alias="Self" xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" xmlns="http://schemas.microsoft.com/ado/2008/09/edm"> 3 <EntityContainer Name="Membership" annotation:LazyLoadingEnabled="true"> 4 <EntitySet Name="Roles" EntityType="MembershipModel.Role" /> 5 <EntitySet Name="Users" EntityType="MembershipModel.User" /> 6 <EntitySet Name="UserDetails" EntityType="MembershipModel.UserDetail" /> 7 <AssociationSet Name="FK_Role_Role" Association="MembershipModel.FK_Role_Role"> 8 <End Role="Parent" EntitySet="Roles" /> 9 <End Role="Children" EntitySet="Roles" /> 10 </AssociationSet> 11 <AssociationSet Name="FK_UserDetail_User" Association="MembershipModel.FK_UserDetail_User"> 12 <End Role="User" EntitySet="Users" /> 13 <End Role="UserDetail" EntitySet="UserDetails" /> 14 </AssociationSet> 15 <AssociationSet Name="UserRole" Association="MembershipModel.UserRole"> 16 <End Role="Role" EntitySet="Roles" /> 17 <End Role="User" EntitySet="Users" /> 18 </AssociationSet> 19 </EntityContainer> 20 <EntityType Name="Role"> 21 <Key> 22 <PropertyRef Name="ID" /> 23 </Key> 24 <Property Name="ID" Type="Guid" Nullable="false" /> 25 <Property Name="Name" Type="String" Nullable="false" MaxLength="40" Unicode="true" FixedLength="false" /> 26 <Property Name="ParentID" Type="Guid" /> 27 <NavigationProperty Name="Children" Relationship="MembershipModel.FK_Role_Role" FromRole="Parent" ToRole="Children" /> 28 <NavigationProperty Name="Parent" Relationship="MembershipModel.FK_Role_Role" FromRole="Children" ToRole="Parent" /> 29 <NavigationProperty Name="Users" Relationship="MembershipModel.UserRole" FromRole="Role" ToRole="User" /> 30 </EntityType> 31 <EntityType Name="User"> 32 <Key> 33 <PropertyRef Name="ID" /> 34 </Key> 35 <Property Name="ID" Type="Guid" Nullable="false" /> 36 <Property Name="Account" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" /> 37 <Property Name="Password" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" /> 38 <NavigationProperty Name="UserDetail" Relationship="MembershipModel.FK_UserDetail_User" FromRole="User" ToRole="UserDetail" /> 39 <NavigationProperty Name="Roles" Relationship="MembershipModel.UserRole" FromRole="User" ToRole="Role" /> 40 </EntityType> 41 <EntityType Name="UserDetail"> 42 <Key> 43 <PropertyRef Name="ID" /> 44 </Key> 45 <Property Name="ID" Type="Guid" Nullable="false" /> 46 <Property Name="Name" Type="String" Nullable="false" MaxLength="50" Unicode="true" FixedLength="false" /> 47 <Property Name="Sex" Type="String" MaxLength="2" Unicode="true" FixedLength="false" /> 48 <Property Name="Birthday" Type="DateTime" /> 49 <NavigationProperty Name="User" Relationship="MembershipModel.FK_UserDetail_User" FromRole="UserDetail" ToRole="User" /> 50 </EntityType> 51 <Association Name="FK_Role_Role"> 52 <End Role="Parent" Type="MembershipModel.Role" Multiplicity="0..1" /> 53 <End Role="Children" Type="MembershipModel.Role" Multiplicity="*" /> 54 <ReferentialConstraint> 55 <Principal Role="Parent"> 56 <PropertyRef Name="ID" /> 57 </Principal> 58 <Dependent Role="Children"> 59 <PropertyRef Name="ParentID" /> 60 </Dependent> 61 </ReferentialConstraint> 62 </Association> 63 <Association Name="FK_UserDetail_User"> 64 <End Role="User" Type="MembershipModel.User" Multiplicity="1"> 65 <OnDelete Action="Cascade" /> 66 </End> 67 <End Role="UserDetail" Type="MembershipModel.UserDetail" Multiplicity="0..1" /> 68 <ReferentialConstraint> 69 <Principal Role="User"> 70 <PropertyRef Name="ID" /> 71 </Principal> 72 <Dependent Role="UserDetail"> 73 <PropertyRef Name="ID" /> 74 </Dependent> 75 </ReferentialConstraint> 76 </Association> 77 <Association Name="UserRole"> 78 <End Role="Role" Type="MembershipModel.Role" Multiplicity="*" /> 79 <End Role="User" Type="MembershipModel.User" Multiplicity="*" /> 80 </Association> 81 </Schema>
四、MSL
1 <?xml version="1.0" encoding="utf-8" ?> 2 <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs"> 3 <EntityContainerMapping StorageEntityContainer="MembershipModelStoreContainer" CdmEntityContainer="Membership"> 4 <EntitySetMapping Name="Roles"> 5 <EntityTypeMapping TypeName="MembershipModel.Role"> 6 <MappingFragment StoreEntitySet="Role"> 7 <ScalarProperty Name="ID" ColumnName="ID" /> 8 <ScalarProperty Name="Name" ColumnName="Name" /> 9 <ScalarProperty Name="ParentID" ColumnName="ParentID" /> 10 </MappingFragment> 11 </EntityTypeMapping> 12 </EntitySetMapping> 13 <EntitySetMapping Name="Users"> 14 <EntityTypeMapping TypeName="MembershipModel.User"> 15 <MappingFragment StoreEntitySet="User"> 16 <ScalarProperty Name="ID" ColumnName="ID" /> 17 <ScalarProperty Name="Account" ColumnName="Account" /> 18 <ScalarProperty Name="Password" ColumnName="Password" /> 19 </MappingFragment> 20 </EntityTypeMapping> 21 </EntitySetMapping> 22 <EntitySetMapping Name="UserDetails"> 23 <EntityTypeMapping TypeName="MembershipModel.UserDetail"> 24 <MappingFragment StoreEntitySet="UserDetail"> 25 <ScalarProperty Name="ID" ColumnName="ID" /> 26 <ScalarProperty Name="Name" ColumnName="Name" /> 27 <ScalarProperty Name="Sex" ColumnName="Sex" /> 28 <ScalarProperty Name="Birthday" ColumnName="Birthday" /> 29 </MappingFragment> 30 </EntityTypeMapping> 31 </EntitySetMapping> 32 <AssociationSetMapping Name="UserRole" TypeName="MembershipModel.UserRole" StoreEntitySet="UserRole"> 33 <EndProperty Name="Role"> 34 <ScalarProperty Name="ID" ColumnName="RoleID" /> 35 </EndProperty> 36 <EndProperty Name="User"> 37 <ScalarProperty Name="ID" ColumnName="UserID" /> 38 </EndProperty> 39 </AssociationSetMapping> 40 </EntityContainerMapping> 41 </Mapping>
五、SSDL
Membership.SqlServer.ssdl
1 <?xml version="1.0" encoding="utf-8" ?> 2 <Schema Namespace="MembershipModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl"> 3 <EntityContainer Name="MembershipModelStoreContainer"> 4 <EntitySet Name="Role" EntityType="MembershipModel.Store.Role" store:Type="Tables" Schema="dbo" /> 5 <EntitySet Name="User" EntityType="MembershipModel.Store.User" store:Type="Tables" Schema="dbo" /> 6 <EntitySet Name="UserDetail" EntityType="MembershipModel.Store.UserDetail" store:Type="Tables" Schema="dbo" /> 7 <EntitySet Name="UserRole" EntityType="MembershipModel.Store.UserRole" store:Type="Tables" Schema="dbo" /> 8 <AssociationSet Name="FK_Role_Role" Association="MembershipModel.Store.FK_Role_Role"> 9 <End Role="Parent" EntitySet="Role" /> 10 <End Role="Children" EntitySet="Role" /> 11 </AssociationSet> 12 <AssociationSet Name="FK_UserDetail_User" Association="MembershipModel.Store.FK_UserDetail_User"> 13 <End Role="User" EntitySet="User" /> 14 <End Role="UserDetail" EntitySet="UserDetail" /> 15 </AssociationSet> 16 <AssociationSet Name="FK_UserRole_Role" Association="MembershipModel.Store.FK_UserRole_Role"> 17 <End Role="Role" EntitySet="Role" /> 18 <End Role="UserRole" EntitySet="UserRole" /> 19 </AssociationSet> 20 <AssociationSet Name="FK_UserRole_User" Association="MembershipModel.Store.FK_UserRole_User"> 21 <End Role="User" EntitySet="User" /> 22 <End Role="UserRole" EntitySet="UserRole" /> 23 </AssociationSet> 24 </EntityContainer> 25 <EntityType Name="Role"> 26 <Key> 27 <PropertyRef Name="ID" /> 28 </Key> 29 <Property Name="ID" Type="uniqueidentifier" Nullable="false" /> 30 <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="40" /> 31 <Property Name="ParentID" Type="uniqueidentifier" /> 32 </EntityType> 33 <EntityType Name="User"> 34 <Key> 35 <PropertyRef Name="ID" /> 36 </Key> 37 <Property Name="ID" Type="uniqueidentifier" Nullable="false" /> 38 <Property Name="Account" Type="nvarchar" Nullable="false" MaxLength="50" /> 39 <Property Name="Password" Type="varchar" Nullable="false" MaxLength="50" /> 40 </EntityType> 41 <EntityType Name="UserDetail"> 42 <Key> 43 <PropertyRef Name="ID" /> 44 </Key> 45 <Property Name="ID" Type="uniqueidentifier" Nullable="false" /> 46 <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" /> 47 <Property Name="Sex" Type="nvarchar" MaxLength="2" /> 48 <Property Name="Birthday" Type="datetime" /> 49 </EntityType> 50 <EntityType Name="UserRole"> 51 <Key> 52 <PropertyRef Name="UserID" /> 53 <PropertyRef Name="RoleID" /> 54 </Key> 55 <Property Name="UserID" Type="uniqueidentifier" Nullable="false" /> 56 <Property Name="RoleID" Type="uniqueidentifier" Nullable="false" /> 57 </EntityType> 58 <Association Name="FK_Role_Role"> 59 <End Role="Parent" Type="MembershipModel.Store.Role" Multiplicity="0..1" /> 60 <End Role="Children" Type="MembershipModel.Store.Role" Multiplicity="*" /> 61 <ReferentialConstraint> 62 <Principal Role="Parent"> 63 <PropertyRef Name="ID" /> 64 </Principal> 65 <Dependent Role="Children"> 66 <PropertyRef Name="ParentID" /> 67 </Dependent> 68 </ReferentialConstraint> 69 </Association> 70 <Association Name="FK_UserDetail_User"> 71 <End Role="User" Type="MembershipModel.Store.User" Multiplicity="1"> 72 <OnDelete Action="Cascade" /> 73 </End> 74 <End Role="UserDetail" Type="MembershipModel.Store.UserDetail" Multiplicity="0..1" /> 75 <ReferentialConstraint> 76 <Principal Role="User"> 77 <PropertyRef Name="ID" /> 78 </Principal> 79 <Dependent Role="UserDetail"> 80 <PropertyRef Name="ID" /> 81 </Dependent> 82 </ReferentialConstraint> 83 </Association> 84 <Association Name="FK_UserRole_Role"> 85 <End Role="Role" Type="MembershipModel.Store.Role" Multiplicity="1"> 86 <OnDelete Action="Cascade" /> 87 </End> 88 <End Role="UserRole" Type="MembershipModel.Store.UserRole" Multiplicity="*" /> 89 <ReferentialConstraint> 90 <Principal Role="Role"> 91 <PropertyRef Name="ID" /> 92 </Principal> 93 <Dependent Role="UserRole"> 94 <PropertyRef Name="RoleID" /> 95 </Dependent> 96 </ReferentialConstraint> 97 </Association> 98 <Association Name="FK_UserRole_User"> 99 <End Role="User" Type="MembershipModel.Store.User" Multiplicity="1"> 100 <OnDelete Action="Cascade" /> 101 </End> 102 <End Role="UserRole" Type="MembershipModel.Store.UserRole" Multiplicity="*" /> 103 <ReferentialConstraint> 104 <Principal Role="User"> 105 <PropertyRef Name="ID" /> 106 </Principal> 107 <Dependent Role="UserRole"> 108 <PropertyRef Name="UserID" /> 109 </Dependent> 110 </ReferentialConstraint> 111 </Association> 112 </Schema>
模型应用
首先需要在应用程序配置文件中配置数据连接：
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <!--SQL Server--> 5 <add name="Membership" providerName="System.Data.EntityClient" connectionString="provider=System.Data.SqlClient;provider connection string=" Data Source=localhost; User Id=sa; Password=11111111; Initial Catalog=Membership; Integrated Security=False;"; metadata=..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.SqlServer.ssdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.csdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.msl"/> 6 </connectionStrings> 7 </configuration>
然后就可以使用该实体数据模型了，测试示例代码如下：
1 using (var db = new EntityContext()) 2 { 3 var role = new Role(); 4 role.ID = Guid.NewGuid(); 5 role.Name = "员工"; 6 7 var role1 = new Role(); 8 role1.ID = Guid.NewGuid(); 9 role1.Name = "部门经理"; 10 role1.Parent = role; 11 db.Roles.AddObject(role1); 12 13 db.SaveChanges(); 14 }
多数据库支持
下面以扩展对MySQL数据库支持为例，讲解该实体数据模型如何实现对多种数据库的支持。需要做以下三件事情：
首先，编写MySQL数据库的DDL语句，并执行它以生成数据库（我是使用Navicat工具连接MySQL，创建Membership数据库，然后执行DDL语句）。DDL语句如下：
1 CREATE TABLE `Role` ( 2 `ID` char(36) NOT NULL, 3 `Name` varchar(40) NOT NULL, 4 `ParentID` char(36) NULL, 5 PRIMARY KEY (`ID`), 6 KEY `ParentID` (`ParentID`), 7 CONSTRAINT `FK_Role_Role` FOREIGN KEY (`ParentID`) REFERENCES `Role` (`ID`) ON DELETE CASCADE 8 ); 9 10 CREATE TABLE `User` ( 11 `ID` char(36) NOT NULL, 12 `Account` varchar(50) NOT NULL, 13 `Password` varchar(50) NOT NULL, 14 PRIMARY KEY (`ID`) 15 ); 16 17 CREATE TABLE `UserDetail` ( 18 `ID` char(36) NOT NULL, 19 `Name` varchar(50) NOT NULL, 20 `Sex` varchar(2) NULL, 21 `Birthday` datetime DEFAULT NULL, 22 PRIMARY KEY (`ID`), 23 CONSTRAINT `FK_UserDetail_User` FOREIGN KEY (`ID`) REFERENCES `user` (`ID`) ON DELETE CASCADE 24 ); 25 26 CREATE TABLE `UserRole` ( 27 `UserID` char(36) NOT NULL, 28 `RoleID` char(36) NOT NULL, 29 PRIMARY KEY (`UserID`,`RoleID`), 30 KEY `FK_UserRole_Role` (`RoleID`), 31 CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserID`) REFERENCES `User` (`ID`) ON DELETE CASCADE, 32 CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleID`) REFERENCES `Role` (`ID`) ON DELETE CASCADE 33 );
然后，添加Membership.MySQL.ssdl文件，其内容与Membership.SqlServer.ssdl的差别主要体现在提供程序及其版本、数据类型上，如下内容中的高亮标识所示：
1 <?xml version="1.0" encoding="utf-8" ?> 2 <Schema Namespace="MembershipModel.Store" Alias="Self" Provider="MySql.Data.MySqlClient" ProviderManifestToken="5.5" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl"> 3 <EntityContainer Name="MembershipModelStoreContainer"> 4 <EntitySet Name="Role" EntityType="MembershipModel.Store.Role" store:Type="Tables" Schema="dbo" /> 5 <EntitySet Name="User" EntityType="MembershipModel.Store.User" store:Type="Tables" Schema="dbo" /> 6 <EntitySet Name="UserDetail" EntityType="MembershipModel.Store.UserDetail" store:Type="Tables" Schema="dbo" /> 7 <EntitySet Name="UserRole" EntityType="MembershipModel.Store.UserRole" store:Type="Tables" Schema="dbo" /> 8 <AssociationSet Name="FK_Role_Role" Association="MembershipModel.Store.FK_Role_Role"> 9 <End Role="Parent" EntitySet="Role" /> 10 <End Role="Children" EntitySet="Role" /> 11 </AssociationSet> 12 <AssociationSet Name="FK_UserDetail_User" Association="MembershipModel.Store.FK_UserDetail_User"> 13 <End Role="User" EntitySet="User" /> 14 <End Role="UserDetail" EntitySet="UserDetail" /> 15 </AssociationSet> 16 <AssociationSet Name="FK_UserRole_Role" Association="MembershipModel.Store.FK_UserRole_Role"> 17 <End Role="Role" EntitySet="Role" /> 18 <End Role="UserRole" EntitySet="UserRole" /> 19 </AssociationSet> 20 <AssociationSet Name="FK_UserRole_User" Association="MembershipModel.Store.FK_UserRole_User"> 21 <End Role="User" EntitySet="User" /> 22 <End Role="UserRole" EntitySet="UserRole" /> 23 </AssociationSet> 24 </EntityContainer> 25 <EntityType Name="Role"> 26 <Key> 27 <PropertyRef Name="ID" /> 28 </Key> 29 <Property Name="ID" Type="guid" Nullable="false" /> 30 <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="40" /> 31 <Property Name="ParentID" Type="guid" /> 32 </EntityType> 33 <EntityType Name="User"> 34 <Key> 35 <PropertyRef Name="ID" /> 36 </Key> 37 <Property Name="ID" Type="guid" Nullable="false" /> 38 <Property Name="Account" Type="nvarchar" Nullable="false" MaxLength="50" /> 39 <Property Name="Password" Type="varchar" Nullable="false" MaxLength="50" /> 40 </EntityType> 41 <EntityType Name="UserDetail"> 42 <Key> 43 <PropertyRef Name="ID" /> 44 </Key> 45 <Property Name="ID" Type="guid" Nullable="false" /> 46 <Property Name="Name" Type="nvarchar" Nullable="false" MaxLength="50" /> 47 <Property Name="Sex" Type="nvarchar" MaxLength="2" /> 48 <Property Name="Birthday" Type="datetime" /> 49 </EntityType> 50 <EntityType Name="UserRole"> 51 <Key> 52 <PropertyRef Name="UserID" /> 53 <PropertyRef Name="RoleID" /> 54 </Key> 55 <Property Name="UserID" Type="guid" Nullable="false" /> 56 <Property Name="RoleID" Type="guid" Nullable="false" /> 57 </EntityType> 58 <Association Name="FK_Role_Role"> 59 <End Role="Parent" Type="MembershipModel.Store.Role" Multiplicity="0..1" /> 60 <End Role="Children" Type="MembershipModel.Store.Role" Multiplicity="*" /> 61 <ReferentialConstraint> 62 <Principal Role="Parent"> 63 <PropertyRef Name="ID" /> 64 </Principal> 65 <Dependent Role="Children"> 66 <PropertyRef Name="ParentID" /> 67 </Dependent> 68 </ReferentialConstraint> 69 </Association> 70 <Association Name="FK_UserDetail_User"> 71 <End Role="User" Type="MembershipModel.Store.User" Multiplicity="1"> 72 <OnDelete Action="Cascade" /> 73 </End> 74 <End Role="UserDetail" Type="MembershipModel.Store.UserDetail" Multiplicity="0..1" /> 75 <ReferentialConstraint> 76 <Principal Role="User"> 77 <PropertyRef Name="ID" /> 78 </Principal> 79 <Dependent Role="UserDetail"> 80 <PropertyRef Name="ID" /> 81 </Dependent> 82 </ReferentialConstraint> 83 </Association> 84 <Association Name="FK_UserRole_Role"> 85 <End Role="Role" Type="MembershipModel.Store.Role" Multiplicity="1"> 86 <OnDelete Action="Cascade" /> 87 </End> 88 <End Role="UserRole" Type="MembershipModel.Store.UserRole" Multiplicity="*" /> 89 <ReferentialConstraint> 90 <Principal Role="Role"> 91 <PropertyRef Name="ID" /> 92 </Principal> 93 <Dependent Role="UserRole"> 94 <PropertyRef Name="RoleID" /> 95 </Dependent> 96 </ReferentialConstraint> 97 </Association> 98 <Association Name="FK_UserRole_User"> 99 <End Role="User" Type="MembershipModel.Store.User" Multiplicity="1"> 100 <OnDelete Action="Cascade" /> 101 </End> 102 <End Role="UserRole" Type="MembershipModel.Store.UserRole" Multiplicity="*" /> 103 <ReferentialConstraint> 104 <Principal Role="User"> 105 <PropertyRef Name="ID" /> 106 </Principal> 107 <Dependent Role="UserRole"> 108 <PropertyRef Name="UserID" /> 109 </Dependent> 110 </ReferentialConstraint> 111 </Association> 112 </Schema>
配置文件中加入MySQL连接字符串，以及MySQL数据库提供程序工厂，添加后的应用程序配置文件内容如下：
1 <?xml version="1.0" encoding="utf-8" ?> 2 <configuration> 3 <connectionStrings> 4 <!--SQL Server--> 5 <add name="Membership" providerName="System.Data.EntityClient" connectionString="provider=System.Data.SqlClient;provider connection string=" Data Source=localhost; User Id=sa; Password=11111111; Initial Catalog=Membership; Integrated Security=False;"; metadata=..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.SqlServer.ssdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.csdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.msl"/> 6 <!--MySQL 7 <add name="Membership" providerName="System.Data.EntityClient" connectionString="provider=MySql.Data.MySqlClient; provider connection string=" Data Source=localhost; User Id=root; Password=11111111; Initial Catalog=Membership; Integrated Security=False;"; metadata=..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.MySQL.ssdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.csdl|..\..\..\Apollo.Blog.EF.Chapter4.Edm\Mapping\Membership.msl"/>--> 8 </connectionStrings> 9 <system.data> 10 <DbProviderFactories> 11 <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory,MySql.Data, Version=6.5.4.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/> 12 </DbProviderFactories> 13 </system.data> 14 </configuration>
通过切换SQL Server和MySQL连接字符串的启用和注释状态，就可以实现不同数据库的支持切换了。
补充说明
一、关于数据库生成
Entity Framework技术提供了由实体数据模型直接生成数据库的功能，示例代码如下：
1 using (var db = new EntityContext()) 2 { 3 if (!db.DatabaseExists()) 4 db.CreateDatabase(); 5 }
但是，通过该方式自动生成数据库至少存在以下两个问题或不足：
- 生成的外键不能级联删除，这会导致有外键的对象删除出错；
- 不支持复杂的DDL语句功能，比如无法生成存储过程、触发器等。
所以，我建议不要使用该方式自动生成数据库，虽然它看起来貌似很好很强大。
二、关于映射文件的生成操作
将Membership.edmx文件拆分成SSDL、CSDL和MSL三个单独的文件后，将这三个文件设为“嵌入的资源”，然后在应用配置文件的数据库连接字符串中使用如下的方式查找文件似乎行不通，总是报“无法加载指定的元数据资源”错误，所以本文选择了使用文件的相对路径进行查找：
1 metadata=res://*/Membership.csdl|res://*/Membership.ssdl|res://*/Membership.msl
总结
本文在前文深入了解实体数据模型的基础上，对该模型作了些许调整，实现了一个DIY的实体数据模型。该实体数据模型能够很好的支持POCO和多数据库，易于维护和扩展。但是该模型无法实现自动延迟加载，这算是美中不足之处。在本系列的第６篇中，将对延迟加载做专门讨论，届时将会告诉你如何使用该DIY实体数据模型实现手动延迟加载；以及如何修改该模型，使其支持延迟加载。
下一篇文章将讨论如何在本文的基础上实现关联数据的延迟加载。
