Model First in Entity Framework 4

Last week, we walked through the FK Association in EF4. Today, we introduce another fantastic feature – Model First. It’s not difficult to guess what the feature will bring us from its name. Alex James (PM of EF team) concluded it as “creating a conceptual ‘model first‘ and then deriving a storage model, database and mappings from that”. So we can now start from a conceptual model instead of an existing database. We do think this feature should have been included in EFv1, because every time we saw this dialog in VS2008, “Empty Model” was never chosen since we don’t have Model First at that time.

image

 

Thanks to EF team’s effort, the “Empty Model” becomes the starting point of our journey today. Let’s first create the conceptual model.

 

Choose the “Empty Model” and we get a new model without any entities, associations or inheritances.

image

 

Right click the blank area of the designer, select Add -> Entity… to add the first entity Person.

image

 

 

 

Add inherited entity Instructor and select the Base type as Person.

image

 

Add other entities: Department, Course, CourseStudent, Admin, Student and BusinessStudent. Create associations including one-to-many and many-to-many associations. We also insert Complex Type in the Person entity to represent each person’s name and address. Here is the final conceptual model:

image

Now there is no information in the SSDL and MSL sections of the .edmx.

   1: <!-- SSDL content -->
  2:     <edmx:StorageModels>
  3:       <Schema xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl" Namespace="ModelFirst.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005">
  4:         <EntityContainer Name="ModelFirstTargetContainer" >
  5:         </EntityContainer>
  6:       </Schema>
  7:     </edmx:StorageModels>
  8:     <!-- CSDL content -->
  9:     <edmx:ConceptualModels>
 10:     ...
 11:     </edmx:ConceptualModels>
 12:     <!-- C-S mapping content -->
 13:     <edmx:Mappings>
 14:       <Mapping xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs" Space="C-S">
 15:         <Alias Key="Model" Value="ModelFirst" />
 16:         <Alias Key="Target" Value="ModelFirst.Store" />
 17:         <EntityContainerMapping CdmEntityContainer="ModelFirstContainer" StorageEntityContainer="ModelFirstTargetContainer">
 18:         </EntityContainerMapping>
 19:       </Mapping>
 20:     </edmx:Mappings>
 21: 

 

Right click the blank area of the designer and select Generate Database from Model…

 

image

 

After we configure the database instance and connection string, press Next. Then the DDL to generate the database is created.

image

After we press the Finish button, the SSDL and MSL sections of the .edmx will be updated. Besides, we get a db script file which contains the DDL to generate the database.

   1: SET QUOTED_IDENTIFIER OFF;
  2: SET ANSI_NULLS ON;
  3: GO
  4: 
  5: USE [ModelFirstGeneratedDB]
  6: GO
  7: IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]')
  8: GO
  9: 
 10: -- --------------------------------------------------
 11: -- Dropping existing FK constraints
 12: -- --------------------------------------------------
 13: 
 14: IF OBJECT_ID(N'[dbo].[FK_DepartmentCourse]', 'F') IS NOT NULL
 15:     ALTER TABLE [dbo].[Courses] DROP CONSTRAINT [FK_DepartmentCourse]
 16: GO
 17: IF OBJECT_ID(N'[dbo].[FK_CourseInstructor_Course]', 'F') IS NOT NULL
 18:     ALTER TABLE [dbo].[CourseInstructor] DROP CONSTRAINT [FK_CourseInstructor_Course]
 19: GO
 20: IF OBJECT_ID(N'[dbo].[FK_CourseInstructor_Instructor]', 'F') IS NOT NULL
 21:     ALTER TABLE [dbo].[CourseInstructor] DROP CONSTRAINT [FK_CourseInstructor_Instructor]
 22: GO
 23: IF OBJECT_ID(N'[dbo].[FK_StudentCourseStudent]', 'F') IS NOT NULL
 24:     ALTER TABLE [dbo].[CourseStudents] DROP CONSTRAINT [FK_StudentCourseStudent]
 25: GO
 26: IF OBJECT_ID(N'[dbo].[FK_CourseCourseStudent]', 'F') IS NOT NULL
 27:     ALTER TABLE [dbo].[CourseStudents] DROP CONSTRAINT [FK_CourseCourseStudent]
 28: GO
 29: IF OBJECT_ID(N'[dbo].[FK_Instructor_inherits_Person]', 'F') IS NOT NULL
 30:     ALTER TABLE [dbo].[People_Instructor] DROP CONSTRAINT [FK_Instructor_inherits_Person]
 31: GO
 32: IF OBJECT_ID(N'[dbo].[FK_Student_inherits_Person]', 'F') IS NOT NULL
 33:     ALTER TABLE [dbo].[People_Student] DROP CONSTRAINT [FK_Student_inherits_Person]
 34: GO
 35: IF OBJECT_ID(N'[dbo].[FK_Admin_inherits_Person]', 'F') IS NOT NULL
 36:     ALTER TABLE [dbo].[People_Admin] DROP CONSTRAINT [FK_Admin_inherits_Person]
 37: GO
 38: IF OBJECT_ID(N'[dbo].[FK_BusinessStudent_inherits_Student]', 'F') IS NOT NULL
 39:     ALTER TABLE [dbo].[People_BusinessStudent] DROP CONSTRAINT [FK_BusinessStudent_inherits_Student]
 40: GO
 41: 
 42: -- --------------------------------------------------
 43: -- Dropping existing tables
 44: -- --------------------------------------------------
 45: 
 46: IF OBJECT_ID(N'[dbo].[People]', 'U') IS NOT NULL
 47:     DROP TABLE [dbo].[People];
 48: GO
 49: IF OBJECT_ID(N'[dbo].[Courses]', 'U') IS NOT NULL
 50:     DROP TABLE [dbo].[Courses];
 51: GO
 52: IF OBJECT_ID(N'[dbo].[Departments]', 'U') IS NOT NULL
 53:     DROP TABLE [dbo].[Departments];
 54: GO
 55: IF OBJECT_ID(N'[dbo].[CourseStudents]', 'U') IS NOT NULL
 56:     DROP TABLE [dbo].[CourseStudents];
 57: GO
 58: IF OBJECT_ID(N'[dbo].[People_Instructor]', 'U') IS NOT NULL
 59:     DROP TABLE [dbo].[People_Instructor];
 60: GO
 61: IF OBJECT_ID(N'[dbo].[People_Student]', 'U') IS NOT NULL
 62:     DROP TABLE [dbo].[People_Student];
 63: GO
 64: IF OBJECT_ID(N'[dbo].[People_Admin]', 'U') IS NOT NULL
 65:     DROP TABLE [dbo].[People_Admin];
 66: GO
 67: IF OBJECT_ID(N'[dbo].[People_BusinessStudent]', 'U') IS NOT NULL
 68:     DROP TABLE [dbo].[People_BusinessStudent];
 69: GO
 70: IF OBJECT_ID(N'[dbo].[CourseInstructor]', 'U') IS NOT NULL
 71:     DROP TABLE [dbo].[CourseInstructor];
 72: GO
 73: 
 74: -- --------------------------------------------------
 75: -- Creating all tables
 76: -- --------------------------------------------------
 77: 
 78: -- Creating table 'People'
 79: CREATE TABLE [dbo].[People] (
 80:     [PersonID] int  NOT NULL,
 81:     [Name_FirstName] nvarchar(max)  NOT NULL,
 82:     [Name_LastName] nvarchar(max)  NOT NULL,
 83:     [Address_Country] nvarchar(max)  NULL,
 84:     [Address_City] nvarchar(max)  NULL,
 85:     [Address_Zipcode] nvarchar(max)  NULL
 86: );
 87: GO
 88: -- Creating table 'Courses'
 89: CREATE TABLE [dbo].[Courses] (
 90:     [CourseID] int  NOT NULL,
 91:     [Title] nvarchar(max)  NOT NULL,
 92:     [Credits] int  NOT NULL,
 93:     [DepartmentID] int  NOT NULL
 94: );
 95: GO
 96: -- Creating table 'Departments'
 97: CREATE TABLE [dbo].[Departments] (
 98:     [DepartmentID] int  NOT NULL,
 99:     [Name] nvarchar(max)  NOT NULL,
100:     [Budget] decimal(18,0)  NULL,

101:     [StartDate] datetime  NOT NULL

102: );

103: GO

104: -- Creating table 'CourseStudents'

105: CREATE TABLE [dbo].[CourseStudents] (

106:     [PersonID] int  NOT NULL,

107:     [CourseID] int  NOT NULL,

108:     [Score] int  NULL

109: );

110: GO

111: -- Creating table 'People_Instructor'

112: CREATE TABLE [dbo].[People_Instructor] (

113:     [HireDate] datetime  NOT NULL,

114:     [PersonID] int  NOT NULL

115: );

116: GO

117: -- Creating table 'People_Student'

118: CREATE TABLE [dbo].[People_Student] (

119:     [EnrollmentDate] datetime  NOT NULL,

120:     [Degree] int  NULL,

121:     [Credits] int  NULL,

122:     [PersonID] int  NOT NULL

123: );

124: GO

125: -- Creating table 'People_Admin'

126: CREATE TABLE [dbo].[People_Admin] (

127:     [AdminDate] datetime  NOT NULL,

128:     [PersonID] int  NOT NULL

129: );

130: GO

131: -- Creating table 'People_BusinessStudent'

132: CREATE TABLE [dbo].[People_BusinessStudent] (

133:     [BusinessCredits] int  NULL,

134:     [PersonID] int  NOT NULL

135: );

136: GO

137: -- Creating table 'CourseInstructor'

138: CREATE TABLE [dbo].[CourseInstructor] (

139:     [Courses_CourseID] int  NOT NULL,

140:     [Instructors_PersonID] int  NOT NULL

141: );

142: GO

143: 

144: -- --------------------------------------------------

145: -- Creating all Primary Key Constraints

146: -- --------------------------------------------------

147: 

148: -- Creating primary key on [PersonID] in table 'People'

149: ALTER TABLE [dbo].[People] WITH NOCHECK 

150: ADD CONSTRAINT [PK_People]

151:     PRIMARY KEY CLUSTERED ([PersonID] ASC)

152:     ON [PRIMARY]

153: GO

154: -- Creating primary key on [CourseID] in table 'Courses'

155: ALTER TABLE [dbo].[Courses] WITH NOCHECK 

156: ADD CONSTRAINT [PK_Courses]

157:     PRIMARY KEY CLUSTERED ([CourseID] ASC)

158:     ON [PRIMARY]

159: GO

160: -- Creating primary key on [DepartmentID] in table 'Departments'

161: ALTER TABLE [dbo].[Departments] WITH NOCHECK 

162: ADD CONSTRAINT [PK_Departments]

163:     PRIMARY KEY CLUSTERED ([DepartmentID] ASC)

164:     ON [PRIMARY]

165: GO

166: -- Creating primary key on [PersonID], [CourseID] in table 'CourseStudents'

167: ALTER TABLE [dbo].[CourseStudents] WITH NOCHECK 

168: ADD CONSTRAINT [PK_CourseStudents]

169:     PRIMARY KEY CLUSTERED ([PersonID], [CourseID] ASC)

170:     ON [PRIMARY]

171: GO

172: -- Creating primary key on [PersonID] in table 'People_Instructor'

173: ALTER TABLE [dbo].[People_Instructor] WITH NOCHECK 

174: ADD CONSTRAINT [PK_People_Instructor]

175:     PRIMARY KEY CLUSTERED ([PersonID] ASC)

176:     ON [PRIMARY]

177: GO

178: -- Creating primary key on [PersonID] in table 'People_Student'

179: ALTER TABLE [dbo].[People_Student] WITH NOCHECK 

180: ADD CONSTRAINT [PK_People_Student]

181:     PRIMARY KEY CLUSTERED ([PersonID] ASC)

182:     ON [PRIMARY]

183: GO

184: -- Creating primary key on [PersonID] in table 'People_Admin'

185: ALTER TABLE [dbo].[People_Admin] WITH NOCHECK 

186: ADD CONSTRAINT [PK_People_Admin]

187:     PRIMARY KEY CLUSTERED ([PersonID] ASC)

188:     ON [PRIMARY]

189: GO

190: -- Creating primary key on [PersonID] in table 'People_BusinessStudent'

191: ALTER TABLE [dbo].[People_BusinessStudent] WITH NOCHECK 

192: ADD CONSTRAINT [PK_People_BusinessStudent]

193:     PRIMARY KEY CLUSTERED ([PersonID] ASC)

194:     ON [PRIMARY]

195: GO

196: -- Creating primary key on [Courses_CourseID], [Instructors_PersonID] in table 'CourseInstructor'

197: ALTER TABLE [dbo].[CourseInstructor] WITH NOCHECK 

198: ADD CONSTRAINT [PK_CourseInstructor]

199:     PRIMARY KEY NONCLUSTERED ([Courses_CourseID], [Instructors_PersonID] ASC)

200:     ON [PRIMARY]

201: GO

202: 

203: -- --------------------------------------------------

204: -- Creating all Foreign Key Constraints

205: -- --------------------------------------------------

206: 

207: -- Creating foreign key on [DepartmentID] in table 'Courses'

208: ALTER TABLE [dbo].[Courses] WITH NOCHECK 

209: ADD CONSTRAINT [FK_DepartmentCourse]

210:     FOREIGN KEY ([DepartmentID])

211:     REFERENCES [dbo].[Departments]

212:         ([DepartmentID])

213:     ON DELETE NO ACTION ON UPDATE NO ACTION

214: GO

215: -- Creating foreign key on [Courses_CourseID] in table 'CourseInstructor'

216: ALTER TABLE [dbo].[CourseInstructor] WITH NOCHECK 

217: ADD CONSTRAINT [FK_CourseInstructor_Course]

218:     FOREIGN KEY ([Courses_CourseID])

219:     REFERENCES [dbo].[Courses]

220:         ([CourseID])

221:     ON DELETE NO ACTION ON UPDATE NO ACTION

222: GO

223: -- Creating foreign key on [Instructors_PersonID] in table 'CourseInstructor'

224: ALTER TABLE [dbo].[CourseInstructor] WITH NOCHECK 

225: ADD CONSTRAINT [FK_CourseInstructor_Instructor]

226:     FOREIGN KEY ([Instructors_PersonID])

227:     REFERENCES [dbo].[People_Instructor]

228:         ([PersonID])

229:     ON DELETE NO ACTION ON UPDATE NO ACTION

230: GO

231: -- Creating foreign key on [PersonID] in table 'CourseStudents'

232: ALTER TABLE [dbo].[CourseStudents] WITH NOCHECK 

233: ADD CONSTRAINT [FK_StudentCourseStudent]

234:     FOREIGN KEY ([PersonID])

235:     REFERENCES [dbo].[People_Student]

236:         ([PersonID])

237:     ON DELETE NO ACTION ON UPDATE NO ACTION

238: GO

239: -- Creating foreign key on [CourseID] in table 'CourseStudents'

240: ALTER TABLE [dbo].[CourseStudents] WITH NOCHECK 

241: ADD CONSTRAINT [FK_CourseCourseStudent]

242:     FOREIGN KEY ([CourseID])

243:     REFERENCES [dbo].[Courses]

244:         ([CourseID])

245:     ON DELETE NO ACTION ON UPDATE NO ACTION

246: GO

247: -- Creating foreign key on [PersonID] in table 'People_Instructor'

248: ALTER TABLE [dbo].[People_Instructor] WITH NOCHECK 

249: ADD CONSTRAINT [FK_Instructor_inherits_Person]

250:     FOREIGN KEY ([PersonID])

251:     REFERENCES [dbo].[People]

252:         ([PersonID])

253:     ON DELETE NO ACTION ON UPDATE NO ACTION

254: GO

255: -- Creating foreign key on [PersonID] in table 'People_Student'

256: ALTER TABLE [dbo].[People_Student] WITH NOCHECK 

257: ADD CONSTRAINT [FK_Student_inherits_Person]

258:     FOREIGN KEY ([PersonID])

259:     REFERENCES [dbo].[People]

260:         ([PersonID])

261:     ON DELETE NO ACTION ON UPDATE NO ACTION

262: GO

263: -- Creating foreign key on [PersonID] in table 'People_Admin'

264: ALTER TABLE [dbo].[People_Admin] WITH NOCHECK 

265: ADD CONSTRAINT [FK_Admin_inherits_Person]

266:     FOREIGN KEY ([PersonID])

267:     REFERENCES [dbo].[People]

268:         ([PersonID])

269:     ON DELETE NO ACTION ON UPDATE NO ACTION

270: GO

271: -- Creating foreign key on [PersonID] in table 'People_BusinessStudent'

272: ALTER TABLE [dbo].[People_BusinessStudent] WITH NOCHECK 

273: ADD CONSTRAINT [FK_BusinessStudent_inherits_Student]

274:     FOREIGN KEY ([PersonID])

275:     REFERENCES [dbo].[People_Student]

276:         ([PersonID])

277:     ON DELETE NO ACTION ON UPDATE NO ACTION

278: GO

279: 

280: -- --------------------------------------------------

281: -- Script has ended

282: -- --------------------------------------------------

283: 

 

Note that the inheritance strategy of Model First is Table-per-Type inheritance. We can learn from the above DDL that each inherited entity generates a single table. You will learn detailed information about the inheritance strategy next time.

 

All the sample codes in this post can be found in the samples CSEFModelFirst (C#) and VBEFModelFirst (VB.NET) in All-In-One Code Framework which is an open-source project delineating the framework and skeleton of Microsoft development techniques.

 

Additional references:
http://blogs.microsoft.co.il/blogs/gilf/archive/2009/05/23/model-first-in-entity-framework-4.aspx
http://geekswithblogs.net/iupdateable/archive/2009/11/17/getting-started-with-entity-framework-4-ndash-simple-model-first.aspx

 

Watch for our “under the hood” exploration of Model First in the coming weeks.