Entity Framework in ASP.NET MVC

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 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 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 required MVC_DB Database, Employee table and populate the Employee table with some 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 an Empty ASP.NET MVC Project called CRUD_OperationsInMVC

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

Creating an Empty MVC Project

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.

Selecting The Project Type as ASP.NET Web Application

Once you click on the OK button a new dialog will pop up with for selecting project Templates as shown in the below image.

Selecting MVC Template

From the above 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 appropriate authentication mode for your application. Here, we are not going to have any authentication for our application. So select No Authentication radio button and click OK. (By default, Individual User Accounts would be selected to authenticate users stored in the SQL Server database.)

Selecting the Authentication Type in ASP.NET MVC Application

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

Step2: Adding ADO.NET Entity Data Model

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.

Adding ADO.NET Entity Data Model in MVC Application

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

Selecting Entity Framework Database First Approach in ASP.NET MVC Application

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

Creating new connection in Entity Framework Database First Approach

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.

Providing Connection Details for SQL Server Database

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 click on Next button as shown below.

Creating Connection String in Entity Framework Database First Apprach in MVC Application

Select the version of Entity Framework

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

Selecting the Entity Framework Version

Selecting the Database Objects

Here, 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 below.

Selecting the Database Object in Entity Framework

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.

Entity Framework References in ASP.NET MVC Application

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

Edmx File in 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 web.config file as shown below.

Connection String in Web Config File

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.

DbContext Class in ASP.NET MVC

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 Controller Folder. Select Add => Controller => Select MVC5 Controller Empty => Click on add button as shown in the below image.

Creating MVC 5 Controller

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

Providing Controller Name

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 View

@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. Run the application and see the result as expected.

Entity Framework in ASP.NET MVC

In this article, we just see how to use Entity Framework in ASP.NET MVC Application step by step. 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 will discuss how to create Hyperlink in ASP.NET MVC application.

Leave a Reply

Your email address will not be published. Required fields are marked *