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.

Comments (1)
  1. risma2006 says:

    This is a very complete information about Framework 4, thanks.

Comments are closed.

Skip to main content