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.
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.
Right click the blank area of the designer, select Add -> Entity… to add the first entity Person.
Add inherited entity Instructor and select the Base type as Person.
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:
Now there is no information in the SSDL and MSL sections of the .edmx.
1: <!-- SSDL content -->
2: <edmx:StorageModels>
3: <Schema xmlns="https://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="https://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…
After we configure the database instance and connection string, press Next. Then the DDL to generate the database is created.
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:
https://blogs.microsoft.co.il/blogs/gilf/archive/2009/05/23/model-first-in-entity-framework-4.aspx
https://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.