使用EF Model First创建edmx模型,数据库有数据的情况下,如何同时更新模型和数据库

使用"EF Model First"，我们可以很直观地在edmx文件中创建数据模型，并根据模型生成数据库，整个过程快速而高效。可当数据库中有了一定的数据量，同时，可能需要对模型中字段、表、关系等进行更新，如何把模型中的变化映射到已经有一定数据量的数据库呢？

 

本篇使用"EF Model First"创建1对多，多对多关系表，接着体验如何把模型的更新映射到数据库。edmx数据模型在如下几个方面更新：
● 修改字段
● 添加字段
● 删除字段
● 删除表
● 删除1对多关系
● 创建1对多关系
● 删除多对多关系
● 创建多对多关系

 

  一、创建edmx数据模型并生成数据库

→在MVC项目的Models文件夹下创建DataModel.edmx文件，点击"添加"
→选择"空模型",点击"完成"
→创建如下实体

UserInfo、Role与R_UserInfoRole是1对多关系。
Role与ActionInfo是多对多关系。

→右键edmx文件界面，选择"根据模型生成数据库"
→选择"新建连接"
→创建一个名称为"ModelFirstTest"的数据库,点击"确定"

→提示"ModelFirstTest不存在，是否尝试创建"，选择"是"
→在"生成数据库向导"界面，勾选"是，在连接字符串中包含敏感数据"，点击"下一步"

→提示DDL另存为"Models\DataModel.edmx.sql"，点击"完成"
→点击"执行"按钮，执行DataModel.edmx.sql中的sql语句

→跳出数据库登录界面，输入用户名和密码，点击"连接"
→打开Sql Server Management Studio，发现已经创建了ModelFirstTest数据库

并且数据库自动为存在多对多关系的Role与ActionInfo生成了中间表ActionInfoRole。

 

  二、关于DataModel.edmx.sql

第一次生成的DataModel.edmx.sql不带删除外键、删除表语句。回到DataModel.edmx界面，根据模型生成数据库的流程再走一遍，这次，DataModel.edmx.sql比较完整。

 

□ 2.1 对于数据库表中值为NULL的列处理

SET QUOTED_IDENTIFIER OFF;
GO
USE [ModelFirstTest];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

SET QUOTED_IDENTIFIER OFF意味着使用=或<>比较运算符，可以把表中列为null的行筛选出来：
● 使用WHERE column_name = NULL返回column_name列中包含空值的行。
● 使用WHERE column_name <> NULL返回column_name列中包含非空值的行。
● 使用 WHERE column_name <> XYZ_value 返回所有不为 XYZ_value 也不为NULL的行。

 

□ 2.2 删除已经存在的外键

在R_UserInfoRole有2个外键。
在ActionInfo和Role的中间表ActionInfoRole有2个外键。

IF OBJECT_ID(N'[dbo].[FK_UserInfoR_UserInfoRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[R_UserInfoRole] DROP CONSTRAINT [FK_UserInfoR_UserInfoRole];
GO
IF OBJECT_ID(N'[dbo].[FK_RoleR_UserInfoRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[R_UserInfoRole] DROP CONSTRAINT [FK_RoleR_UserInfoRole];
GO
IF OBJECT_ID(N'[dbo].[FK_ActionInfoRole_ActionInfo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ActionInfoRole] DROP CONSTRAINT [FK_ActionInfoRole_ActionInfo];
GO
IF OBJECT_ID(N'[dbo].[FK_ActionInfoRole_Role]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ActionInfoRole] DROP CONSTRAINT [FK_ActionInfoRole_Role];
GO

□ 2.3 删除已经存在的表

UserInfo,Role,ActionInfo,R_UserInfoRole,ActionInfoRole总共5张表。

IF OBJECT_ID(N'[dbo].[UserInfo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserInfo];
GO
IF OBJECT_ID(N'[dbo].[Role]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Role];
GO
IF OBJECT_ID(N'[dbo].[ActionInfo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActionInfo];
GO
IF OBJECT_ID(N'[dbo].[R_UserInfoRole]', 'U') IS NOT NULL
    DROP TABLE [dbo].[R_UserInfoRole];
GO
IF OBJECT_ID(N'[dbo].[ActionInfoRole]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ActionInfoRole];
GO

□ 2.4 创建5张表

创建的时候不考虑主键和外键，主键和外键是后期加上。

-- Creating table 'UserInfo'
CREATE TABLE [dbo].[UserInfo] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [LoginName] nvarchar(32)  NOT NULL,
    [LoginPwd] nvarchar(32)  NOT NULL,
    [SubTime] datetime  NOT NULL
);
GO

-- Creating table 'Role'
CREATE TABLE [dbo].[Role] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [RoleName] nvarchar(32)  NOT NULL
);
GO

-- Creating table 'ActionInfo'
CREATE TABLE [dbo].[ActionInfo] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [ActionInfoName] nvarchar(32)  NOT NULL
);
GO

-- Creating table 'R_UserInfoRole'
CREATE TABLE [dbo].[R_UserInfoRole] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [IsEnable] bit  NOT NULL,
    [UserInfoID] int  NOT NULL,
    [RoleID] int  NOT NULL
);
GO

-- Creating table 'ActionInfoRole'
CREATE TABLE [dbo].[ActionInfoRole] (
    [ActionInfo_ID] int  NOT NULL,
    [Role_ID] int  NOT NULL
);
GO

□ 2.5 创建5张表的主键约束

-- Creating primary key on [ID] in table 'UserInfo'
ALTER TABLE [dbo].[UserInfo]
ADD CONSTRAINT [PK_UserInfo]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Role'
ALTER TABLE [dbo].[Role]
ADD CONSTRAINT [PK_Role]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ActionInfo'
ALTER TABLE [dbo].[ActionInfo]
ADD CONSTRAINT [PK_ActionInfo]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'R_UserInfoRole'
ALTER TABLE [dbo].[R_UserInfoRole]
ADD CONSTRAINT [PK_R_UserInfoRole]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ActionInfo_ID], [Role_ID] in table 'ActionInfoRole'
ALTER TABLE [dbo].[ActionInfoRole]
ADD CONSTRAINT [PK_ActionInfoRole]
    PRIMARY KEY NONCLUSTERED ([ActionInfo_ID], [Role_ID] ASC);
GO

 

□ 2.6 创建外键以及非聚集索引

关于聚集索引和非聚集索引：
● 聚集索引，好比一本汉语字典中，按页搜索字，第1页、第2页......页面是连续的，把页数看作是聚集索引。
● 一个表中，聚集索引是唯一的，就好比一本汉语字典，只能按照一种顺序，即页数来排序。
● SQL Server默认为主键创建聚集索引。

● 非聚集索引，好比一本汉语字典中，按偏旁部首搜索字，偏旁部首本身是不连续的，把偏旁部首看作是非聚集索引。
● 经常被分组排序的列、外键列、频繁给更新的列、频繁修改索引的列......适合使用非聚集索引。

 

R_UserInfoRole表的UserInfoID和RoleID设置为外键和非聚集索引：

-- Creating foreign key on [UserInfoID] in table 'R_UserInfoRole'
ALTER TABLE [dbo].[R_UserInfoRole]
ADD CONSTRAINT [FK_UserInfoR_UserInfoRole]
    FOREIGN KEY ([UserInfoID])
    REFERENCES [dbo].[UserInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoR_UserInfoRole'
CREATE INDEX [IX_FK_UserInfoR_UserInfoRole]
ON [dbo].[R_UserInfoRole]
    ([UserInfoID]);
GO

-- Creating foreign key on [RoleID] in table 'R_UserInfoRole'
ALTER TABLE [dbo].[R_UserInfoRole]
ADD CONSTRAINT [FK_RoleR_UserInfoRole]
    FOREIGN KEY ([RoleID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleR_UserInfoRole'
CREATE INDEX [IX_FK_RoleR_UserInfoRole]
ON [dbo].[R_UserInfoRole]
    ([RoleID]);
GO

 

ActionInfoRole设置外键和非聚集索引：

-- Creating foreign key on [ActionInfo_ID] in table 'ActionInfoRole'
ALTER TABLE [dbo].[ActionInfoRole]
ADD CONSTRAINT [FK_ActionInfoRole_ActionInfo]
    FOREIGN KEY ([ActionInfo_ID])
    REFERENCES [dbo].[ActionInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Role_ID] in table 'ActionInfoRole'
ALTER TABLE [dbo].[ActionInfoRole]
ADD CONSTRAINT [FK_ActionInfoRole_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionInfoRole_Role'
CREATE INDEX [IX_FK_ActionInfoRole_Role]
ON [dbo].[ActionInfoRole]
    ([Role_ID]);
GO

  三、更新模型再映射到数据库

在更新模型之前，为每张表添加数据。

□ 3.1 模型修改字段

把UserInfo的LoginName改成LoginName1，并把字符串长度从nvarchar(32)变成nvarchar(64)

→在DataModel.edmx中修改

→右键界面，选择"根据模型生成数据库"
→点击"完成",生成更新过的"DataModel.edmx.sql"
但是，数据库有了数据，不能丢掉现有的表，不能在这里直接运行，必须到数据库中更新。
→右键DataModel.edmx中UserInfo表，选择"表映射"

可见，在DataModel.edmx层面已经完成了映射。
→在数据库执行修改列名、改变列名长度语句

EXEC sp_rename '[dbo].[UserInfo].[LoginName]','LoginName1'
ALTER TABLE [dbo].[UserInfo] ALTER COLUMN [LoginName1] nvarchar(64)

 

□ 3.2 模型添加字段

在UserInfo中增加类型为small int的DelFlag字段。

→在DataModel.edmx中添加

→右键界面，选择"根据模型生成数据库"
→点击"完成",生成更新过的"DataModel.edmx.sql"
但是，数据库有了数据，不能丢掉现有的表，不能在这里直接运行，必须到数据库中更新。
→右键DataModel.edmx中UserInfo表，选择"表映射"

可见，在DataModel.edmx层面已经完成了映射。
→在数据库执行添加列语句

ALTER TABLE [dbo].[UserInfo] ADD [DelFlag] smallint not null default 0

 

□ 3.3 删除字段

把UserInfo中的DelFlag字段删除。

→在DataModel.edmx中删除
→右键界面，选择"根据模型生成数据库"
→点击"完成",生成更新过的"DataModel.edmx.sql"
但是，数据库有了数据，不能丢掉现有的表，不能在这里直接运行，必须到数据库中更新。
→右键DataModel.edmx中UserInfo表，选择"表映射"

可见，在DataModel.edmx层面已经完成了映射。
→在数据库执行删除列语句
先要删除列相关的约束，再删除

ALTER TABLE [dbo].[UserInfo] DROP CONSTRAINT DF__UserInfo__DelFla__0AD2A005
ALTER TABLE [dbo].[UserInfo] DROP COLUMN [DelFlag]

 

□ 3.4 删除1对多关系

删除R_UserInfoRole与UserInfo和Role的2组1对多关系。

→在DataModel.edmx中删除2组关联
→在DataModel.edmx中把R_UserInfoRole中的UserInfoID和RoleID属性删除
→右键界面，选择"根据模型生成数据库"
→在数据库中

先删除R_UserInfoRole的外键约束：

IF OBJECT_ID(N'[dbo].[FK_UserInfoR_UserInfoRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[R_UserInfoRole] DROP CONSTRAINT [FK_UserInfoR_UserInfoRole];
GO
IF OBJECT_ID(N'[dbo].[FK_RoleR_UserInfoRole]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[R_UserInfoRole] DROP CONSTRAINT [FK_RoleR_UserInfoRole];
GO

 

然后删除R_UserInfoRole的外键UserInfoID和RoleID,先删除列的索引再删除外键：

DROP INDEX [dbo].[R_UserInfoRole].[IX_FK_UserInfoR_UserInfoRole]
ALTER TABLE [dbo].[R_UserInfoRole] DROP COLUMN [UserInfoID]

DROP INDEX [dbo].[R_UserInfoRole].[IX_FK_RoleR_UserInfoRole]
ALTER TABLE [dbo].[R_UserInfoRole] DROP COLUMN [RoleID]

□ 3.5 删除表

删除R_UserInfoRole。当然，在删除表之前首先要删除表的各种关联，

→在DataModel.edmx中删除R_UserInfoRole
→右键界面，选择"根据模型生成数据库"
→在数据库中删除表

DROP TABLE [dbo].[R_UserInfoRole]

 

□ 3.6 创建1对多关系

在DataModel.edmx中，创建R_UserInfoRole1,然后分别和UserInfo，Role建立1对多关系。

→在DataModel.edmx中，创建R_UserInfoRole1

→右键界面，选择"根据模型生成数据库"
→数据库生成R_UserInfoRole1

先生成表R_UserInfoRole1：

CREATE TABLE [dbo].[R_UserInfoRole1] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [IsEnable] bit  NOT NULL
);
GO

 

为R_UserInfoRole1增加主键：

ALTER TABLE [dbo].[R_UserInfoRole1]
ADD CONSTRAINT [PK_R_UserInfoRole1]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

→在DataModel.edmx中，创建R_UserInfoRole1与UserInfo，Role的2组关联

→右键界面，选择"根据模型生成数据库"
→在数据库中

先为R_UserInfoRole1增加UserInfoID和RoleID列：

ALTER TABLE [dbo].[R_UserInfoRole1] ADD [UserInfoID] int not null
ALTER TABLE [dbo].[R_UserInfoRole1] ADD [RoleID] int not null

 

再为R_UserInfoRole1的UserInfoID和RoleID列添加外界约束和对应的非聚集索引：

-- Creating foreign key on [UserInfoID] in table 'R_UserInfoRole1'
ALTER TABLE [dbo].[R_UserInfoRole1]
ADD CONSTRAINT [FK_UserInfoR_UserInfoRole1]
    FOREIGN KEY ([UserInfoID])
    REFERENCES [dbo].[UserInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserInfoR_UserInfoRole1'
CREATE INDEX [IX_FK_UserInfoR_UserInfoRole1]
ON [dbo].[R_UserInfoRole1]([UserInfoID]);
GO

-- Creating foreign key on [RoleID] in table 'R_UserInfoRole1'
ALTER TABLE [dbo].[R_UserInfoRole1]
ADD CONSTRAINT [FK_RoleR_UserInfoRole1]
    FOREIGN KEY ([RoleID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoleR_UserInfoRole1'
CREATE INDEX [IX_FK_RoleR_UserInfoRole1]
ON [dbo].[R_UserInfoRole1]([RoleID]);
GO

 

□ 3.7 删除多对多关系

删除ActionInfo与Role的多对多关系。

→在DataModel.edmx中，删除ActionInfo与Role的关联
→右键界面，选择"根据模型生成数据库"
→在数据库中删除ActionInfoRole中间表

DROP TABLE [dbo].[ActionInfoRole]

 

□ 3.8 创建多对多关系

→在DataModel.edmx中，重新创建ActionInfo与Role的关联
→右键界面，选择"根据模型生成数据库"
→在数据库中

先创建ActionInfo与Role的中间表ActionInfoRole：

CREATE TABLE [dbo].[ActionInfoRole] (
    [ActionInfo_ID] int  NOT NULL,
    [Role_ID] int  NOT NULL
);
GO

 

为中间表ActionInfoRole创建外键约束和非聚集索引：

-- Creating foreign key on [ActionInfo_ID] in table 'ActionInfoRole'
ALTER TABLE [dbo].[ActionInfoRole]
ADD CONSTRAINT [FK_ActionInfoRole_ActionInfo]
    FOREIGN KEY ([ActionInfo_ID])
    REFERENCES [dbo].[ActionInfo]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Role_ID] in table 'ActionInfoRole'
ALTER TABLE [dbo].[ActionInfoRole]
ADD CONSTRAINT [FK_ActionInfoRole_Role]
    FOREIGN KEY ([Role_ID])
    REFERENCES [dbo].[Role]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ActionInfoRole_Role'
CREATE INDEX [IX_FK_ActionInfoRole_Role]
ON [dbo].[ActionInfoRole]
    ([Role_ID]);
GO