Model First Approach Of Entity Framework

Entity Framework Model First approach

In this article, we will discuss the Model First approach of Entity Framework. This is a continuation of the previous article. Please refer to the previous article before proceeding.

• Introduction To Entity Framework 

Entity Framework supports the three approaches given below.

1. Schema First approach – Referred to  in the previous article. 
2. Model First approach – We will see in this article. 
3. Code First approach - Next Article.

Let’s start

With the Schema first approach, which is based on the underlying database tables; i.e. departments and employees tables, we can generate the department and employee entities.

In this article, we will see the Model First approach. Thus, in Model First approach, we first create the entities, i.e. the department and the employees. We will establish a relationship between them and based on these entities, we are going to generate T-SQL, which is required for  the underlying database tables i.e. the departments and employees. Let’s see how to do that. We will work with the same example on which we worked in the previous article of this series.

Now, let’s flip to VS here. In this solution, I already have employee.edmx file, delete that file and build the solution.

Now, let’s add a new item and we want to add ADO.NET Entity Data Model, as shown below.

Name it as EmployeeModel and click OK.

With the Schema First approach, we will select the first option, which is EF designer, from the database because it is based on the underlying database objects, where we want to generate our entities.

Now, we are going to use Model First approach, so select Empty EF Designer Model. We will create the entities first and based on those entities, the Entity Framework is required to generate the required T-SQL for us for the underlying database objects.

Now, click Finish, which should generate Employee Model in our solution.

Now, we will add Department entity. Thus, right click on Design surface -> Add -> Entity.

Specify the name of the entity as Department. Specify the base type as none because we are not using any inheritance here.

The Property name is ID, which is the primary key for our Departments table and property type should be INT32. Thus, click OK, which should generate Department entity for us.

Notice, we already have the scalar property, which is ID property, and notice that there is a key symbol, which means it is a primary key. For Department entities, we need the name and location of the scalar properties.

To get them, right click on Department property -> Add -> Scalar property, as shown below.

Type Name. Now, right click on the name and select Properties, as shown below.

The Name of the property is Name. Is it Nullable NO; it has been set to false. Look at the Getter and the Setter property, where both should be public properties and maximum length now is none but you can set the maximum length, as per your requirement. The type is string.

Hence, SQL which going to be generated for the department's underlying table, is going to make that column in the table as nvarchar and then the size is going to MAX as nvarchar(MAX).

Now, let's add another property location, as shown below.

Hence, it was Department entity where we will add another entity, which is employee entity.

Click OK.

Our Employee entity is generated, which is similar to our department entity. Employee entity also has ID, which is a primary key. Now, let’s add 4 scalar properties in Employee entities as Firstname,lastname, Gender and Salary, as shown below.

Thus, we are done with creating Department and Employee entities. Now, the important thing is the navigation property. Thus, there will be a one to many relationship as one department can have many employees.

Now, let us see how to add navigation property between two entities. It's very simple, as you just need to right click on the designer surface->Add New -> Association.

The moment you select Association, it is shown below.

It has automatically selected a name by default, which is DepartmentEmployee and how this association is going to be every department, where one department is going to have many instances of employees. Thus, one department can have many employees and each employee is going to have one department. Now, let’s click OK.

The moment you click OK, it’s going to create the navigation property for us. Thus, the navigation property is set between Department and Employees. It has also added Department ID as the foreign key in Employee entity.

Thus, one Department can have many employees, which is represented by *. This means your association is perfect.

Now, we have these entities and based on these entities, we want to generate SQL for our underlying database tables.

How to do that? Right click on the designer surface ->Generate database from models

Thus, based on this model, we are going to generate the database for which you need to have the database.

Select the database and click Next.

Now, DDL has been generated. This is SQL, which is going to help us for the database objects which are required. First, when you look at the DDL statement carefully, we have the script to create the department table and Employees table and when you scroll down you have the script to create the foreign key for the respective entities as well.

Thus, we have all the script to create the database object. Let’s click Finish.

It will generate a file called EmployeeModel.edmx.sql, so this contains SQL and we will execute it. Thus, right click on the file and select Execute option, as shown below.

It will ask for the credentials to connect to SSMS.

Click on Connect and execute SQL, as shown below.

As you can see from the output, it failed to execute our SQL script. Why?

Because the object Department and Employees already exists in our underlying database, so delete those objects from the database.

While deleting the database object, you can’t delete Department object. Why?

Employees table is dependent on the foreign key constraint. First delete the Employees table and then delete Departments table.

Now, flip to VS and execute the script. This time around, we should not get any error.

Now, when you look at the tables which are created they will not have any data. We need to insert some dummy data, as shown below.

Insertinto Departments values ('IT', 'Mumbai')
Insertinto Departments values ('HR', 'Mumbai')
Insertinto Departments values ('Payroll', 'Pune')
Insertinto Employees values ('Akshay', 'Phadke', 'Male', 60000, 1)
Insertinto Employees values ('Milind', 'Daware', 'Male', 45000, 3)
Insertinto Employees values ('Raghvan', 'Nadar', 'Male', 70000, 1)
Insertinto Employees values ('Mary', 'Ratnam', 'Female', 30000, 2)

Now, let's run our Application and see the output, as shown below.

In this article, we have seen Model First approach in Entity Framework. In the next article, we will see Code First approach.