Model-First Development in Entity Framework 4.0 - Create a Database from a Model

Posted by: Suprotim Agarwal , on 11/15/2010, in Category Entity Framework
Views: 77669
Abstract: Developers who practice the Domain-Driven Development (DDD) style create a conceptual model first. The idea is to design the application based on a model. EF 4.0 supports creating the model first and generating a database from the model. Let us see how.

Developers who practice the Domain-Driven Development (DDD) style create a conceptual model first. The idea is to design the application based on a model. EF 4.0 supports this concept/pattern by allowing developers to create the model first and then generate a database from the model. Let us see how.

Note: If you are new to Entity Framework 4.0, I strongly recommend to read my article Entity Framework 4.0 FAQ – Getting Started Guide

Step 1: Open VS 2010 > File > New Project > Select Visual C# or Visual Basic Windows Template and select an Empty Project as shown below. I am calling this project ‘ModelFirstEF’. Make sure the target framework is ‘.NET Framework 4.0’

Visual Studio Empty Project 

Right click the Project in Solution Explorer > Add > New Item > Data Template > ADO.NET Entity Data Model. Click Add.

Entity Model 

The Entity Data Model Wizard appears. Select ‘Empty Model’ and click on Finish

Entity Model Data Wizard 

Step 2: The wizard creates a new conceptual model with an empty Data Model Designer to create your Entity Data Model (EDM).

Drag and drop an Entity from the Toolbox to the Designer as shown below. You can even right click the designer > Add Entity

Add Entityin EF
 

Rename ‘Entity1’ to ‘Employee’. The Id property you see above is the EntityKey (Primary Key) of this entity. I will rename it to ‘EmployeeId’ in the properties window. Also observe that it’s Type is set to ‘Int32’ and since this is an identity key, its ‘StoreGeneratedPattern’ is automatically set to ‘Identity’ as shown below:

EF Indentity Column 

You can add additional properties like FirstName (string), LastName (string), Age (Int16) etc. by right clicking the Entity in the designer > Add > Scalar Property

EF Scalar Property
 

The result is as shown below:

EF Entity 

Step 3: Let us add one more entity called ‘Department’. This time we will do it from the designer. Right click the designer > Add > Entity

 
The ‘Add Entity’ dialog appears. Type ‘Department’ in the Entity name field and select the box ‘Create Key Property’ to create an Entitykey. Call it ‘DepartmentId’ and set its property type as Int32.

EF Dept Entity 

Click OK and a new Department entity will appear on the design surface. Add two additional properties DeptName (string) and EmployeeId (Int32) to the Department Entity.

Step 4: Our next step is to create a One-to-many relationship between Employee and Department. Each Department can have many Employees, but one Employee can belong to only one Department. Using the Association tool in the Toolbox, select the EmployeeId property in the Employee entity, hold the left mouse button down and drag it to the DepartmentId property in the Department entity.

Update: I should have added the DepartmentId in the Employee table, but for this example just assume the opposite. It is a typo. 

  EF Relationship

Alternatively, you can also right click the Employee Entity > Add > Association to create a relationship.

Your model should look like the one shown below, with a One-to-many relationship between Employee and Department.

EF Oneto Many 

Your conceptual model is ready now! Our next step is to generate the database from the model

Generate Schema and Database from Model

Step 5: I have gone ahead and created an Empty database called ‘EFDB’ in my SQL Server box. To generate a Database from the model, right click the designer > Generate Database from Model..

EF Generate Db From Model 

Note: I find this amusing! Although the option says ‘Generate Database from Model’, an empty or an existing database ‘must’ exist before clicking this option. I think the Database must exist due to the Connection Settings that we will establish in the next step. We need to specify a target database there, so the database must exist beforehand. Also the DDL script that gets generated does not include the script to generate a new database.

Step 6: The Generate Database Wizard appears. Click on ‘New Connection’ and fill the Connection properties as shown below. Test the Connection and Click OK.

EF Connection Db 

Click Next in the wizard to preview the DDL that will be generated

EF Generate Db Wizard DDL 

Click Finish to add the generated script to your project. Once you click Finish, an EntityConnection string is added to your config file and the DDL, SSDL and MSL files are created (read my previous article Exploring the Entity Data Model (EDM) to know what SSDL and MSL are).

Step 7: All you need to do now is run the database script by clicking on the Green Arrow in a query window (Ctrl+Shift+E) to create the Employee and Department tables in the database EFDB.

Entity Designer DDL Script 

If everything’s ok, you will see the message ‘Command(s) executed successfully.

How did the DDL Scripts get generated from the model?

 If you are wondering what went behind the scenes to generate the database, then I would strongly recommend you to read my previous article Exploring the Entity Data Model (EDM) where I discussed the role of .EDMX and its files SSDL, CSDL and C-S mapping. These files play a role not only in generating a model from the database, but also vice-versa, i.e. generating a database from a model. Observe the properties of Model1.edmx as shown below, especially the DDL Generation Template property.

Ddl T4 Generation 

This property specifies a T4 template (.tt) file. The T4 Templates reads the .edmx with the help of the TablePerTypeStrategy.xaml (windows workflow file) and generates the T-SQL code (DDL script) that we just saw.

As given in the msdn documentation “T4 is shorthand for the Text Template Transformation Toolkit and is a Microsoft technology explicitly designed to make text file creation such as code generation straightforward using templates. It is a mixture of processing logic and text to emit with the idea that the text can be created from the template by substituting values in it programmatically. This is similar in principle to XSLT but T4 allows the blending of .NET code and text mark-up to be combined seamlessly and it is used extensively in Visual Studio 2010

The best part is that you can modify this template and customize the DDL generated. You can download the Entity Designer Database Generation Power Pack which contains Windows Workflows and T4 Templates to manage database generation strategies. Anyways we will explore this topic again in one of the future articles.

Limitations of the Model-First development

There are two major limitations of the Model-First development that you should keep in mind (we will also see how to overcome them)

  1. You cannot update the database and expect the changes to be updated in the model. Currently it’s not possible out-of-the-box through EF 4.0.
  2. If you go ahead and populate the database with data and then at a later stage change your model, then the database will be dropped including all your data when you recreate the DDL scripts and execute them.

However remember that you can extend the DDL generation capabilities of the Entity Framework. The good news is that these two limitation can be overcome using the Entity Designer Database Generation Power Pack. Using this power pack, you can update an existing database and synchronize the model with it and also make changes to the model and deploy the changes back to the database without data loss. Hopefully in the next version, this will be added to the core of EF.

Conclusion

I hope you are excited seeing the Model-first development in EF 4.0. It was one of the most requested feature in EF 4.0 and was readily provided by the EF team.

You must be eager to check out the schema generated in your database. Open SQL Server Management Studio and expand the EFDB database to look at the two tables, the association, keys, constraints etc created.

The entire source code of this article can be downloaded over here

I hope you liked the article and I thank you for viewing it.

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles
Suprotim Agarwal, ASP.NET Architecture MVP, MCSD, MCAD, MCDBA, MCSE, is the CEO of A2Z Knowledge Visuals Pvt. He primarily works as an Architect Consultant and provides consultancy on how to design and develop .NET centric database solutions.

Suprotim is the founder and primary contributor to DotNetCurry, DNC .NET Magazine, SQLServerCurry and DevCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls. and is authoring another one at The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by H. McCreight on Wednesday, November 24, 2010 5:31 AM
Ever designed a database with SQL server ? Decide on your application entities (tables), give each one its properties(columns),get a list of all the transactions and use that to work out the table relationships (1-M,M-M,1-1). If your table model (database tables) don't
let you handle all the possible transactions , then you have to review the table design and relationships. Entity Frameworks adds nothing
new to this process as far as I can see.
Comment posted by Nasir on Thursday, November 25, 2010 3:47 AM
Hmcreight Seems like you have never worked with Entity Framework but just read about it somewhere. EF lets you do all that you just mentioned - define properties, relationships and manage transactions. Ever heard of TransactionScope?
Comment posted by Uffe on Friday, December 17, 2010 2:54 AM
Just have to ask about the sentence "select the EmployeeId property in the Employee entity, hold the left mouse button down and drag it to the ****DepartmentId*** property in the Department entity."

Shouldnä't it read: "select the EmployeeId property in the Employee entity, hold the left mouse button down and drag it to the EmployeeId property in the Department entity.
Comment posted by fatih on Friday, December 17, 2010 10:39 AM
Your model is definitely wrong. You should add departmentID into employees model, but you added employeeID into department. In your model each employee has many departments and each department has 1 employee.
Comment posted by Suprotim Agarwal on Tuesday, December 21, 2010 3:43 AM
fatih: Yes you are right. It is a typo.
Comment posted by Andy on Monday, May 16, 2011 11:12 AM
SA, there is definitely something wrong in your model. Luckily there are folks who can identify the problem and some, like me, cannot. Please refrain from misleading people who are trying to learn from these articles. Your article is pretty useless and misleading.
Comment posted by Suprotim Agarwal on Wednesday, May 18, 2011 3:32 AM
Andy: Thanks for the feedback. It's just a field that should have been in the other table. And it has been recitified and mentioned clearly. So I am not sure what you mean by misleading
Comment posted by Meena Damwani on Friday, August 31, 2012 3:12 AM
Hi SA,

Your article is good for learning base of model first in ef. Some mistake is there but it doesn't matter.Thanks
Comment posted by Radmehr on Thursday, August 15, 2013 4:24 PM
hi
why we have two EmployeeId in Department table
Comment posted by santosh on Monday, October 14, 2013 2:18 AM
Suprotim Hi,

Many many thanks for the Learn ADO.NET Entity Framework 4 – Free Tutorials series.

I have just started learning Entity Framework,obviously google many article--ah My 2-3 days gone for exercising example and did not complete any of them due to one reason or another --finally i find this article and understood the Basic from this article.
thanks again

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel