Entity Framework in ASP.NET MVC

Back to: ASP.NET MVC Tutorial

Entity Framework in ASP.NET MVC

In this article, I am going to discuss how to use Entity Framework in ASP.NET MVC Application to interact with SQL Server Database to perform the CRUD operation. In the ASP.NET MVC application, the controller responds to the incoming HTTP request, then gets the data from a model and hands it over to the view. The view then renders the data. The model can be entities or business objects. Here, in this article, we are going to discuss how to retrieve the data from the SQL Server Database using the Entity Framework Database First Approach in ASP.NET MVC Application. In our upcoming article, we will discuss using business objects as our model.

Creating the Database with Required Table and Data:

Please use below SQL Script to create the  MVC_DB Database, Employee table and populate the Employee table with test data:

--Create a database called MVC_DB
CREATE DATABASE MVC_DB
GO

--Use MVC_DB
USE MVC_DB
GO

--Create a Table called Employee
CREATE TABLE Employee
(
  EmployeeId int Primary Key Identity(1,1),
  Name nvarchar(50),
  Gender nvarchar(10),
  City nvarchar(50),
  Salary decimal(18,2)
)
GO

--Insert some values into Employee Table
INSERT INTO Employee values('Pranaya','Male','Mumbai', 2000)
INSERT INTO Employee values('Sambit','Male','Chennai', 3000)
INSERT INTO Employee values('Priyanka','Female','Hydrabad', 4000)
INSERT INTO Employee values('Anurag','Male','Cheenai', 4000)
INSERT INTO Employee values('Subrat','Male','Mumbai', 3000)
INSERT INTO Employee values('Preety','Female','Mumbai', 2000)
INSERT INTO Employee values('Trupti','Female','Hyderabad', 5000)
GO

Let us understand how to use Entity Framework in ASP.NET MVC Application step by step.

Step1: Create a new Empty ASP.NET MVC Application

Open Visual Studio and click on the New Project link on the Startup page as shown below. Alternatively, you can also select the File menu -> New Project option from the Context menu.

From the New Project window as shown in the below image, expand the Visual C# node and select Web from the left pane. From the middle pane select ASP.NET Web Application. Provide a meaningful name to your project such as CRUD_OperationsInMVC. Select the location where you want to create the project by clicking on the Browse button. Finally, click on the OK button as shown in the below image.

Once you click on the OK button a new dialog will pop up for selecting project Templates as shown in the below image. From the below window, we are going to choose the Empty Project Template and Select MVC from Add Folders and core reference section.

You can also change the authentication by clicking on the Change Authentication button. You can select the appropriate authentication mode for your application. Here, we are not going to have any authentication for our application. So select the No Authentication radio button and click OK. (By default, Individual User Accounts would be selected to authenticate users stored in the SQL Server database.)

Wait for some time till Visual Studio creates a simple MVC project using the default template.

Step2: Adding ADO.NET Entity Data Model in ASP.NET MVC Application

Right-Click on Models Folder, and then select Add => New Item from the context menu which will open the Add New Item window. From the “Add New Item” window, from the left pane expand Installed => Visual C# => Data option. From the middle pane select the ADO.NET Entity Data Model template. Provide a meaningful name to your data model such as EmployeeDataModel and finally click on the Add button as shown in the below image.

From the next Entity Data Model Wizard screen, as we are going to use the Entity Framework Database First approach, so select EF Designer from Database option and click on the Next button as shown in the image below.

In the next step, click on the new connection From Choose your data connection wizard as shown below.

Provide the necessary details to communicate with the database such as Server name, select the Authentication Type, select the Database and click on the Ok button as shown below.

Then provide a meaningful name for the connection string that is going to save in the web.config file. Here, I am naming the connection string as EmployeeDBContext and then click on the Next button as shown in the below image.

Select the version of Entity Framework

Here I am going to use Entity Framework 6.x, so I am selecting the Entity Framework 6.x radio button and click on the Next button as shown in the below image.

Selecting the Database Objects

Then we need to select the database object for our application. As our database has one table, so we need to select that Employee Table. Provide a meaningful namespace to your EDMX file and finally click on the Finish button as shown in the below image.

Once you click on the Finish button, let’s see what the things are created by Entity Framework. It will add the reference to the Entity Framework in the reference folder.

The framework will create the EDMX file within the Models folder.

It will create the Employee Model.

To see the Employee Model click on Employee.cs file, this is inside the EmployeeDataModel.tt File

namespace CRUD_OperationsInMVC.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
        public Nullable<decimal> Salary { get; set; }
    }
}

It will create the connection string in the web.config file as shown below.

It will create the DBContext class for us whose name is the same as the name of the connection string that is EmployeeDBContext. The EmployeeDBContext class derives from the DbContext class and is responsible for establishing a connection to the database. The  EmployeeDataModel.Context.cs which is inside EmployeedataModel.Context.tt as sown below.

Below is the auto-generated code for the EmployeeDBContext class

namespace CRUD_OperationsInMVC.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    public partial class EmployeeDBContext : DbContext
    {
        public EmployeeDBContext()
            : base("name=EmployeeDBContext")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<Employee> Employees { get; set; }
    }
}

That’s it. We successfully created our Entity Data Model. Now it’s time to use this model in our application.

Creating Controller:

Right-Click on Controllers Folder. Select Add => Controller => Select MVC5 Controller Empty and then click on add button as shown in the below image.

In the next Screen provide the controller name as EmployeeController and click on the Add button as shown below.

Let’s modify the default route in the RouteConfig class so that when the application runs for the first time it will redirect to the Index Action method of Employee Controller.

After modifying the RouteConfig class the codes looks like as shown below

namespace CRUD_OperationsInMVC
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Employee", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

Let’s modify the index action method of employee controller to use entity framework as shown below

namespace CRUD_OperationsInMVC.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index()
        {
            EmployeeDBContext dbContext = new EmployeeDBContext();
            List<Employee> empList = dbContext.Employees.ToList();
            return View(empList);
        }
    }
}

Let’s create the index view.

Right-click on the Index Action Method and click on Add View. Then paste the following code in the Index,cshtml file.

@model IEnumerable<CRUD_OperationsInMVC.Models.Employee>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Gender)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.City)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Salary)
        </th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Gender)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.City)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Salary)
        </td>
    </tr>
}
</table>

That’s it. Now run the application and you should get the result as expected as shown in the below image.

Here, in this article, we just see how to use Entity Framework in ASP.NET MVC Application. In our upcoming articles, we will discuss how to perform CRUD operations in ASP.NET MVC using Entity Framework with a real-time example. In the next article, I am going to discuss how to create Hyperlink in ASP.NET MVC application.