Introduction to Entity Framework in ASP.NET (Creating an EF Model in Visual Studio 2013)

What is Entity Framework ?

According to Microsoft's Official Website Entity Framework may be defined as below.
Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.
Entity Framework enables you to create a mirror of your database in your codding environment. Entity is the object that holds the database objects(tables). For more theoretical information you can visit wikipedia.

In this article I will be demonstrating how to create a model from our existing AdventureWorks Database. And in the next article we will be consuming the data from a web application to show it in a grid.

Let's Set up The Entity Framework Model in VS 2013

Open your visual studio and create an empty project first. Then right click on the project and add a new item. Then the below pop up will appear where you have to select the Data tab from the right side tree in installed templates. There select the ADO.NET Entity Data Model. Now give a name to your project. Here I have given a name of AWModel to it.
Model Project Creation
Now after clicking add button another screen will appear like below asking you what you really want to do ? Are you going to create an empty model or you are going to generate a model from database.
Generate Model From Database.

For time being we will be generating a model from our database. Click on the Next button now.
This will take you to a dialog where you need to select your database connections, from where we need to generate our model.
Select Data base connection
Click on the New Connection button. Where you can find the below screen which will prompt you to give your database details, login details and database name. I have given a "." in place of server name as I am using the my local data base to create a model. You can provide server name where there, where you have hosted your database.

Things to do in this step:
  • Give your server name in place of  "Server name" text box. If you are connecting to your local just put a "." there or else put the server name.
  • Then in the next section you have to select which authentication you are going to use. Is it windows authentication or SQL authentication. Select accordingly. If you select windows authentication then no need to give any credentials there. If you select the SQL authentication then you need to provide your login credentials in the below section of username and password textboxes.
  • Now choose a data base name from the drop-down of Connect to database section.
  • Then click on Test connection to check if the connection is ok or not if its ok then just click on the ok button to finish this setup.

Select server name and database names
Here I have used my local database and windows authentication type and selected the Adventureworks database. You can provide your details accordingly.

When you click on the ok button of the above screen, if your entered data are all good then you will be able to see the structure of your database  in a tree view where you have can see the the tables, stored procedures  and the views. Select the objects accordingly.
Select database objects
After selecting the required the objects from the dialog then just check the 3 check boxes available at the bottom of the dialog. and give a name for the namespace of this Model. Here I have given it a name of Adventureswoks2012Model.

Now click on the finish button and wait for sometime, you may go and have a coffee in the mean time while VS is generating you database objects in the model. Ok, its done now. Here is the screen how our model looks like.
model for AdventureWorks2012 database
This is having the all the table objects of your database. Click on any table objects and then click on the any column name. This is editable you can change the column name here and this will be effective throughout your project. For example lets modify one column name of an object.

Select the person table and in the bottom window you can see the below screen.
Changing the column name
Here you can see that I am changing the Title column name of the Person table to Title_Demo. What happens in the background is This Model keeps track of the original column name and the alias you have written for it. It maps the alias to the original name and allows you to use the alias instead of the original name. Whenever it finds the alias name in use it just replace the cal with the original name of that column.

Hm, Up to this we have successfully created a model form an existing database. You can browse the project and find a AWModel.edmx file inside it which is the model containing the all the database objects. Just expand the and you will be able to see all the database tables has been transformed into classes. Here is a screenshot of the Address table of AdventureWorks 2012 database.
Address class
 Go inside the app config file where you can see that VS has automatically added a connection string in the config file. and the connection string is a bit different from usual ones. Find it below.

AdventureWorks Connectionstring
That's it, Now build the solution and you can use the dll of your project anywhere to access the database.
  • Entity Framework is better to handle small data, I mean all the basic CRUD(Creation, Read, Updation, Deletion) operation. Its not good to use for more highlevel and complex thigs like cursor.
  • From my personal point of view I don't like EF model, because each time I change the databae I need to update the model of my project. Also I have to deploy it in the server if I commit any changes to the database. Where this overhead is not there while using the SQL Server directly.
 In the next article I will be showing how to consume this model in a web application.

Happy Coding...

No comments:

Post a Comment