Working With Entity Framework Using Database First Approach

What is an Entity Framework?

An Entity Framework enables the developers to interact with the database, which is just like ADO.NET but in a more compact and shorter way. I have been using ADO.NET before, but once I got a hold of Entity Framework, I found this interesting and easy to use. Unlike ADO.NET, in an Entity Framework; you don’t need to write long and tedious code to connect with the database. You can use LINQ to issue queries, retrieve and manipulate the data. An Entity Framework is just more enhanced way to use ADO.NET code.

In this article, we will see how to add an Entity Framework in our project, using Database First approach. In Database First approach, we would prepare our database first, like creating the table before accessing it, using EF.

Now, let’s get started. First create your own database by right clicking on the database, followed by New Database in SQL Server. I have named my database as demoEcommerce. Now, execute the the scripts given below to create your tables.

Create table scripts

tblState 

CREATE TABLE [dbo].[tblState](
    [stateid] [int] NOT NULL,
    [statename] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [stateid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

tblCity 

CREATE TABLE [dbo].[tblCity](
    [Cityid] [int] NOT NULL,
    [CityName] [nvarchar](50) NULL,
    [stateid] [int] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblCity]  WITH CHECK ADD FOREIGN KEY([stateid])
REFERENCES [dbo].[tblState] ([stateid])
GO

After you have executed the scripts, create a folder in your project. I have named it “Entity Framework”. Now, right click on your Entity Framework folder and select Add ->ddd new Item.

Now, select Data and ADo.NET Entity Framework data model. It gives a name to your Entity Framework, as I had given demoEFmodel.

Click Add. After clicking Add; a new pop up Window will open, as shown below.

Now, select the EF designer from the database and select Next. Now, after clicking next, you will see a new popup, as shown below.

Select New Connection option. You should see the name of your project given below. After clicking, New connection Window given below should open.

In the Server name, give the Server name of your database and click Refresh. In the dropdown, you should see your database name. In this case, it is demoEcommerce.

Click Test Connection and if the connection succeeds, it will give a pop up message, which says test succeeded. Now, click OK at last. After clicking OK, the popup given below should open.

Select Tables, followed by DBO and now, we should see our table names. In this case, these are tblCity and tblState. My database previously had more table, so it is showing accordingly. I am selecting my two tables. You can also select all the tables, if you wish to. Now, click Finish.

Now, you should see you EDMX file has been created.

Now, you can create an object of demoEcommerceEntities, access all your tables in your project and perform your DML operations.

Now, in my Controller, I have created an object of an Entity Framework and as you can see, I can access both the tables (tblCity & tblState). Now, I would be able to perform any DML operations in these tables.

Thus, you have just configured your Entity Framework, using Database First approach in your project.