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’
Right click the Project in Solution Explorer > Add > New Item > Data Template > ADO.NET Entity Data Model. Click Add.
The Entity Data Model Wizard appears. Select ‘Empty Model’ and click on Finish
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
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:
You can add additional properties like FirstName (string), LastName (string), Age (Int16) etc. by right clicking the Entity in the designer > Add > Scalar Property
The result is as shown below:
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.
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.
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.
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..
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.
Click Next in the wizard to preview the DDL that will be generated
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.
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.
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)
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.
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.
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.