Entity Framework技术系列之4：灵活应用实体数据模型

时间：2022-11-28 20:38:36

前言

上一篇文章中，我详细解析了使用Database First和Model First开发模式自动生成的Entity Framework实体数据模型的内容结构，如下图所示：

 Entity Framework技术系列之4：灵活应用实体数据模型

图1实体数据模型内容结构图

本文将就实体数据模型内容作文章，将其拆分为多个文件，并作适当的调整，实现一个DIY的实体数据模型，其内容结构如下图所示：

 Entity Framework技术系列之4：灵活应用实体数据模型

图2调整后的实体数据模型内容结构图

做这些调整的目的有两个，其一是使用POCO替换实体数据模型中繁琐的实体类，使代码更整洁并易于维护；其二是使程序支持多种数据库，并且易于扩展。

 

E-R模型

E-R模型继续沿用上一篇文章中使用的模型，如下图所示：

Entity Framework技术系列之4：灵活应用实体数据模型 

图3 E-R模型

对应的SQL Server 数据库DDL语句如下：

Entity Framework技术系列之4：灵活应用实体数据模型
 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
Entity Framework技术系列之4：灵活应用实体数据模型

使用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方言，替换相应的数据类型。

一、实体上下文环境

Entity Framework技术系列之4：灵活应用实体数据模型
 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 }
Entity Framework技术系列之4：灵活应用实体数据模型

二、实体类

Role实体类：

Entity Framework技术系列之4：灵活应用实体数据模型
 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 }
Entity Framework技术系列之4：灵活应用实体数据模型

User实体类：

Entity Framework技术系列之4：灵活应用实体数据模型
 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 }
Entity Framework技术系列之4：灵活应用实体数据模型

UserDetail实体类：

Entity Framework技术系列之4：灵活应用实体数据模型
 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 }
Entity Framework技术系列之4：灵活应用实体数据模型

三、CSDL

Entity Framework技术系列之4：灵活应用实体数据模型
 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>
Entity Framework技术系列之4：灵活应用实体数据模型

四、MSL

Entity Framework技术系列之4：灵活应用实体数据模型
 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>
Entity Framework技术系列之4：灵活应用实体数据模型

五、SSDL

Membership.SqlServer.ssdl

Entity Framework技术系列之4：灵活应用实体数据模型
  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>
Entity Framework技术系列之4：灵活应用实体数据模型

 

模型应用

首先需要在应用程序配置文件中配置数据连接：

Entity Framework技术系列之4：灵活应用实体数据模型
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=&quot; Data Source=localhost; User Id=sa; Password=11111111; Initial Catalog=Membership; Integrated Security=False;&quot;; 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>
Entity Framework技术系列之4：灵活应用实体数据模型

然后就可以使用该实体数据模型了，测试示例代码如下：

Entity Framework技术系列之4：灵活应用实体数据模型
 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 }
Entity Framework技术系列之4：灵活应用实体数据模型

 

多数据库支持

下面以扩展对MySQL数据库支持为例，讲解该实体数据模型如何实现对多种数据库的支持。需要做以下三件事情：

首先，编写MySQL数据库的DDL语句，并执行它以生成数据库（我是使用Navicat工具连接MySQL，创建Membership数据库，然后执行DDL语句）。DDL语句如下：

Entity Framework技术系列之4：灵活应用实体数据模型
 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 );
Entity Framework技术系列之4：灵活应用实体数据模型

然后，添加Membership.MySQL.ssdl文件，其内容与Membership.SqlServer.ssdl的差别主要体现在提供程序及其版本、数据类型上，如下内容中的高亮标识所示：

Entity Framework技术系列之4：灵活应用实体数据模型
  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>
Entity Framework技术系列之4：灵活应用实体数据模型

配置文件中加入MySQL连接字符串，以及MySQL数据库提供程序工厂，添加后的应用程序配置文件内容如下：

Entity Framework技术系列之4：灵活应用实体数据模型
 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=&quot; Data Source=localhost; User Id=sa; Password=11111111; Initial Catalog=Membership; Integrated Security=False;&quot;; 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=&quot; Data Source=localhost; User Id=root; Password=11111111; Initial Catalog=Membership; Integrated Security=False;&quot;; 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>
Entity Framework技术系列之4：灵活应用实体数据模型

通过切换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实体数据模型实现手动延迟加载；以及如何修改该模型，使其支持延迟加载。

下一篇文章将讨论如何在本文的基础上实现关联数据的延迟加载。

标签：

相关文章