CRUD Operations using Entity Framework

CRUD Operations using Entity Framework in ASP.NET MVC

In this article and few upcoming articles, I will discuss how to perform CRUD operations using Entity Framework in ASP.NET MVC application.

We will be going to use table Department and Employee for this demo

Use below SQL script to create and populate these 2 tables
Create table Department
(
  Id int primary key identity,
  Name nvarchar(50)
)

Insert into Department values('IT')
Insert into Department values('HR')
Insert into Department values('Payroll')

Create table Employee
(
  EmployeeId int Primary Key Identity(1,1),
  Name nvarchar(50),
  Gender nvarchar(10),
  City nvarchar(50),
  Salary decimal(18,2),
  DepartmentId int
)

Alter table Employee add foreign key (DepartmentId) references Department(Id)

Insert into Employee values('Mark','Male','London',1000,1)
Insert into Employee values('John','Male','Chennai',2000,3)
Insert into Employee values('Mary','Female','New York',3000,3)
Insert into Employee values('Mike','Male','Sydeny',4000,2)
Insert into Employee values('Scott','Male','London',3000,1)
Insert into Employee values('Pam','Female','Falls Church',2000,2)
Insert into Employee values('Todd','Male','Sydney',1000,1)
Insert into Employee values('Ben','Male','New Delhi',4000,2)
Insert into Employee values('Sara','Female','London',5000,1)
Create a new asp.net MVC 5 web application with the name “CRUD_Using_EF”.

File => New =>Project

Select Installed => Templates => Visual C# => Select ASP.NET Web Application

Give the project name and provide the location where you want to save your application as shown below

CRUD operations using Entity Framework in ASP.NET MVC

From the next screen that is from select a template screen

Select Empty as the template

From add folder and core reference section check the MVC checkbox and click on OK as shown below

CRUD operations using Entity Framework in ASP.NET MVC

It will take some time to create the solution as shown below

CRUD operations using Entity Framework in ASP.NET MVC

Right-click on the “Models” folder and add “ADO.NET Entity Data Model”. Set Name = EmployeeDataModel.  

Right click on Models folder then select Add => New Item.

Select the Data tab form the left panel and then choose ADO.NET Entity Data Model from the middle panel as shown below

CRUD operations using Entity Framework in ASP.NET MVC

On the subsequent screen, select “Generate from database” option and click “Next” as shown below.

CRUD operations using Entity Framework in ASP.NET MVC

On “Choose your data connection screen” and click on “New Connection” button.

Specify the SQL server name. From,”Select or enter a database name” dropdown list select the Database name and click “OK” as shown below.

CRUD operations using Entity Framework in ASP.NET MVC

 Then provide a meaningful connection string name “EmployeeDBContext” and
Click “Next” as shown below.

CRUD operations using Entity Framework in ASP.NET MVC

In the screen choose the version of entity framework you want and click on next as shown below

CRUD operations using Entity Framework in ASP.NET MVC

On “Choose your database objects” screen, expand “Tables” and select “Department” and “Employee” tables. Set “Model Namespace=Models” and click “Finish” as shown below

CRUD operations using Entity Framework in ASP.NET MVC  

At this point, we should have Department and Employee entities generated as shown below.

CRUD operations using Entity Framework in ASP.NET MVC  

Build the solution.

Right-click on the “Controllers” folder and select Add – Controller.

Select MVC 5 Controller with views, using Entity Framework and click on Add as shown below

CRUD operations using Entity Framework in ASP.NET MVC

On the next screen set the below details

Model class = Employee (CRUD_Using_EF.Models)

Data Context Class = EmployeeDBContext(CRUD_Using_EF.Models)

Controller Name = EmployeeController

Rest values are as it is and click on Add as shown below

CRUD operations using Entity Framework in ASP.NET MVC

At this point, we should have the following files automatically added.

EmployeeController.cs file in “Controllers” folder

Index, Create, Edit, Detail and Delete views in “Employee” folder.

Below is the EmployeeController code
namespace CRUD_Using_EF.Controllers
{
public class EmployeeController : Controller
{
private EmployeeDBContext db = new EmployeeDBContext();
// GET: Employee
public ActionResult Index()
{
var employees = db.Employees.Include(e => e.Department);
return View(employees.ToList());
}
// GET: Employee/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
// GET: Employee/Create
public ActionResult Create()
{
ViewBag.DepartmentId = new SelectList(db.Departments, "Id", "Name");
return View();
}
// POST: Employee/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "EmployeeId,Name,Gender,City,Salary,DepartmentId")] Employee employee)
{
if (ModelState.IsValid)
{
db.Employees.Add(employee);
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.DepartmentId = new SelectList(db.Departments, "Id", "Name", employee.DepartmentId);
return View(employee);
}
// GET: Employee/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
ViewBag.DepartmentId = new SelectList(db.Departments, "Id", "Name", employee.DepartmentId);
return View(employee);
}
// POST: Employee/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for 
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "EmployeeId,Name,Gender,City,Salary,DepartmentId")] Employee employee)
{
if (ModelState.IsValid)
{
db.Entry(employee).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
ViewBag.DepartmentId = new SelectList(db.Departments, "Id", "Name", employee.DepartmentId);
return View(employee);
}
// GET: Employee/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
// POST: Employee/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Employee employee = db.Employees.Find(id);
db.Employees.Remove(employee);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}

At this point if we run the application we will get an error stating – The resource cannot be found. This is because by default the application goes to “HOME” controller and “Index” action.

To fix this, 

Open “RouteConfig.cs” file from “App_Start” folder

Set Controller = “Employee”

Run the application again. Notice that all the employees are listed on the index view. We can also create a new employee, edit an employee, view their full details and delete an employee as well. However, there are a few issues with each of the views which we will address in our upcoming articles.

In our next article, I will discuss what are the issues associated with the Index view and how will solve this

SUMMARY
In this article, I try to explain how to perform CRUD operations using Entity Framework in ASP.NET MVC application step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

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