How to Create a Database from a Model using Entity Framework Model First Approach

This article elaborate the steps required to create Entities, relationships, and inheritance hierarchies on the design surface of EDMX and generate a database from it.

Here you can find what is model first approach and what is the advantages of using model first approach.

Below are the steps to create a model and generate a database from the model:

Step 1: Create a class library

Open Visual Studio 2012 > File > New Project > Windows Template > Class Library > Give it a name 'EFModelFirst' > OK 

Delete the default Class1.cs that gets created in the project. 

 

Step 2: Add an Entity Data Mode(Empty model)

Right click on the project in Solution Explorer > Add > New Item Select the Data Template > ADO.NET Entity Data Model and click Add. 

 

In the Entity Data Model Wizard, choose 'Empty model' and click Finish.

 

Now we can able to see the designer surface.

Step 3: Add Entities

We can add Entities, there associations or inheritance either by drag and drop them to the design surface from the toolbox or from the context menu.

 

Here I am adding it using context menu. Right click on Designer > Add New > Entity , Then a add Entity Pop up will open. Add the Entity name and Entity Set will automatically pluralize the entity Name. Property name will describe the primary key and its default name is Id. We can change the name also. Property type will show the data type of the property. Here i am changing the Entity and Property name to Employee and Employee Id and click OK. 

Then you can see the Employee entity in the designer.

Step 4: Add scalar property (Fields)

Adding new fields to employee entity by right click on entity > Add New > Scalar Property. We can also rename the field by right click on field > rename. In property window we can find the new field has Default type as string and null able as false.

Here I have added few fields (FirstName, LastName, Gender, phoneNumber, HireDate) to the employee Entity. Similarly I have created another entity with name "Department" (Using Step 3) and property name "Department Id". Added a new scalar property Department Name.

Step 5: Add Association

We can establish relationship among different entities. Here I am going to establish a 1 to many relationship between Department and Employee.(Here 1 department has many employee so departmentId should be the foreign key in the employee table) We can achieve this by right click on any entity > Add New > Association. Then a Add association popup will open. We can define the type of relationship (1 to 1, 1 to many,many to many) on it and click OK.

 

Now our Model is ready, Now we can generate a database from the Model.Before going to generate a Database from a model we must make sure that a empty database should be exist.

Step 6: Create a empty database and connect Model to that database

Create an empty database with the name "EFModelFirstApproach" in SQL server by using this SQL query

                            CREATE DATABASE EFModelFirstApproach;

To create the database we need to go to the design surface Right click > Generate database from model. It will not generate the database rather it will generate the schema. Database need to be preexist (Here we have created EFModelFirstApproach).

If the selected database is correct then Click Next (else go for a new connection option. Select the Server name. You can either choose windows authentication or SQL server authentication.In SQL server authentication you need to provide the username and password. Then select the database name you want to connect and click on test connection. If it will show a successful message then click on OK button and proceed. In generate Database Wizard click next.) Then we will see the Script generated for creating the database. Click on Finish. We will see a file with .sql extension. "DataModel.edmx.sql" name is generated.

In the app.config we will see the connection string.

We can also see the raw metadata from the .edmx file by Right click on datamodel.edmx > Open with > XML (text) Editor > Ok > yes. We will able to see the metadata.

Step 7: Execute schema to create tables

Open DataModel.edmx.sql file. Now we can able to see the database schema. Now we will generate the database from the schema. Right click on the schema > Execute > Connect to the DB and its done.

Step 8: Database created successfully

Now go to the database refresh database Open table and you can able to see the Employee and Department table along with the fields in the Database.

 

Thats it.. Now you have successfully created a database from a model using Model first approach.

Happy coding.. :)