CRUD operation with Entity Framework 6 Code First approach in MVC 5

• Download source Code First MVC 5 - 410.8 KB

Introduction

In this article, I’ll describe how to perform basic CRUD operations with code first in a MVC5 application. We develop application with the help of Entity Framework 6 code first and MVC5.

Code First

In the Code First approach, we will not use visual model designer (EDMX) completely. We write our own POCO classes first and then create database from these POCO classes. Code First Approach provides an alternative to the Database First and Model First approaches to the Entity Data Model and creates database of us based on our classes.

Prerequisites

Visual Studio 2013 or Visual Studio Express 2013 for Web.

Create an MVC Web Application

Open Visual Studio and create a new C# Web project named "CRUDOperationCodeFirst".

In the New ASP.NET Project dialog box select the MVC template. Click Change Authentication.

In the Change Authentication dialog box, select No Authentication, and then click OK. For this application we won't be requiring users to log on or restricting access based on who's logged on.

Back in the New ASP.NET Project dialog box; click OK to create the project. 

Install Entity Framework 6

We will use Entity Framework 6. We check the version of Entity Framework in the project if the version is less than 6 then use Manage NuGet Packages for, update version of Entity Framework.

Create the Data Model

In the Models folder, create a class file named Employee.cs and replace the template code with the below code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CRUDOperationCodeFirst.Models
{
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public Nullable<system.datetime> JoiningDate { get; set; }
        public virtual ICollection<enrollment> Enrollments { get; set; }
    }
}</enrollment></system.datetime>

The ID property will become the primary key column of the database table that corresponds to this class. By default, the Entity Framework interprets a property that's named ID or classnameID as the primary key.

The Enrollments property is a navigation property. Navigation properties hold other entities that are related to this entity.

Navigation properties are typically defined as virtual so that they can take advantage of certain Entity Framework functionality such as lazy loading.

If a navigation property can hold multiple entities (as in many-to-many or one-to-many relationships), its type must be a list in which entries can be added, deleted, and updated, such as ICollection.

In the Models folder, create Enrollment.cs and replace the existing code with the below code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CRUDOperationCodeFirst.Models
{
    public class Enrollment
    {
        public int EnrollmentID { get; set; }
        public Nullable<decimal> Band { get; set; }
        public int DepartmentID { get; set; }
        public int EmployeeID { get; set; }

        public virtual Department Department { get; set; }
        public virtual Employee Employee { get; set; }
    }
}</decimal>

The EnrollmentID property will be the primary key; this entity uses the classnameID pattern instead of ID by itself as you saw in the Employee entity.

The EmployeeID property is a foreign key, and the corresponding navigation property is Employee. An Enrollment entity is associated with one Employee entity, so the property can only hold a single Employee entity

The DepartmentID property is a foreign key, and the corresponding navigation property is Department. An Enrollment entity is associated with one Department entity.

In the Models folder, create Department.cs, replacing the template code with the below code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CRUDOperationCodeFirst.Models
{
    public class Department
    {

        public int DepartmentID { get; set; }
        public string Title { get; set; }
        public Nullable<int> Credits { get; set; }

        public virtual ICollection<enrollment> Enrollments { get; set; }
    }
}</enrollment></int>

The Enrollments property is a navigation property. A Department entity can be related to any number of Enrollment entities.

Create the Database Context

The main class that coordinates Entity Framework functionality for a given data model is the database context class. You create this class by deriving from the System.Data.Entity.DbContext class.

Now create a new folder name DAL in the CRUDOperationCodeFirst project, right-click the project in Solution Explorer and click Add, and then click New Folder. Name the new folder DAL (for Data Access Layer). In that folder create a new class file named EmployeeContext cs, and replace the template code with the below code:

using CRUDOperationCodeFirst.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Web;

namespace CRUDOperationCodeFirst.DAL
{
    public class EmployeeContext : DbContext
    {
        public EmployeeContext()
            : base("EmployeeContext")
        {
        }

        public DbSet<employee> Employees { get; set; }
        public DbSet<enrollment> Enrollments { get; set; }
        public DbSet<department> Departments { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Conventions.Remove<pluralizingtablenameconvention>();
        }
    }
}</pluralizingtablenameconvention></department></enrollment></employee>

This code creates a DbSet property for each entity set. In Entity Framework terminology, an entity set typically corresponds to a database table, and an entity corresponds to a row in the table.

Specifying the connection string

Open the application Web.config file and add a connectionStrings element preceding the appSettings element, as shown in the below example.

<connectionstrings>
    <add connectionstring="Data Source=(localdb)\Projects;Initial Catalog=OrganizationEmployeesCF;Integrated Security=SSPI;" name="EmployeeContext" providername="System.Data.SqlClient">
</add></connectionstrings>

The name of the connection string is passed in to the constructor.

public EmployeeContext() : base("EmployeeContext")
        {
        }

Set up EF to initialize the database with test data:

The Entity Framework can automatically create (or drop and re-create) a database for you when the application runs. You can specify that this should be done every time your application runs or only when the model is out of sync with the existing database. You can also write a Seed method that the Entity Framework automatically calls after creating the database in order to populate it with test data.

In the DAL folder, create a new class file named DepartmentInitializer.cs and replace the template code with the following code, which causes a database to be created when needed and loads test data into the new database.

using CRUDOperationCodeFirst.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace CRUDOperationCodeFirst.DAL
{
    public class DepartmentInitializer : System.Data.Entity.DropCreateDatabaseIfModelChanges<employeecontext>
    {
        protected override void Seed(EmployeeContext context)
        {

            var Departments = new List<department>
            {
            new Department{DepartmentID=1,Title="Microsoft",Credits=3,},
            new Department{DepartmentID=2,Title="Java",Credits=3,},
            new Department{DepartmentID=3,Title="Php",Credits=4,}            
            };
            Departments.ForEach(s => context.Departments.Add(s));
            context.SaveChanges();

            var Employees = new List<employee>
            {
            new Employee{FirstName="Ark",LastName="Roop",JoiningDate=DateTime.Parse("2005-09-01")},
            new Employee{FirstName="Akash",LastName="Gupta",JoiningDate=DateTime.Parse("2002-09-01")},
            new Employee{FirstName="Saurabh",LastName="Gupta",JoiningDate=DateTime.Parse("2003-09-01")},
           
            };
            Employees.ForEach(s => context.Employees.Add(s));
            context.SaveChanges();

            var enrollments = new List<enrollment>
            {
            new Enrollment{EmployeeID=1,DepartmentID=1,Band=Convert.ToDecimal(2.00)},
            new Enrollment{EmployeeID=2,DepartmentID=1,Band=Convert.ToDecimal(3.00)},
            new Enrollment{EmployeeID=3,DepartmentID=2,Band=Convert.ToDecimal(4.00)},
           
            };
            enrollments.ForEach(s => context.Enrollments.Add(s));
            context.SaveChanges();
        }
    }
}</enrollment></employee></department></employeecontext>

The Seed method takes the database context object as an input parameter, and the code in the method uses that object to add new entities to the database. For each entity type, the code creates a collection of new entities, adds them to the appropriate DbSet property, and then saves the changes to the database. It isn't necessary to call the SaveChanges method after each group of entities, as is done here, but doing that helps you locate the source of a problem if an exception occurs while the code is writing to the database.

To tell Entity Framework to use your initializer class, add an element to the entityFramework element in the application Web.config file (the one in the root project folder), as shown in the below example:

<entityframework>

    <contexts>

      <context type="CRUDOperationCodeFirst.DAL.EmployeeContext, CRUDOperationCodeFirst">

        <databaseinitializer type="CRUDOperationCodeFirst.DAL.DepartmentInitializer, CRUDOperationCodeFirst">

      </databaseinitializer></context>

    </contexts>

The context type specifies the fully qualified context class name and the assembly it's in, and the databaseinitializer type specifies the fully qualified name of the initializer class and the assembly it's in.

The application is now set up so that when you access the database for the first time in a given run of the application, the Entity Framework compares the database to the model  If there's a difference, the application drops and re-creates the database.

Creating Controller and Views:

Step 1: Add the new controller to the existing Controllers folder. Right-click the Controllers folder, and select Add – New Scaffolded Item.

Step 2: Select the MVC 5 Controller with views, using Entity Framework option. This option will generate the controller and views for updating, deleting, creating and displaying the data in your model.

Step 3: Add the controller name as EmployeeController  then select Employee for the model class, and select the OrganizationEmployeeEntities for the context class now click add.

If you receive an error, it may be because you did not build the project in the previous section. If so, try building the project, and then add the scaffolded item again.

After the code generation process is complete, you will see a new controller and views in your project.

Perform the same steps again, but add a scaffold for the Enrollment class. When finished, you should have an EnrollmentsController.cs file, and a folder under Views named Enrollments with the Create, Delete, Details, Edit and Index views.

Step 4: We see our employee controller prepared with all the CRUD operation actions as shown below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using OrganizationDetails.Models;

namespace OrganizationDetails.Controllers
{
    public class EmployeeController : Controller
    {
        private OrganizationEmployeeEntities db = new OrganizationEmployeeEntities();

        // GET: /Employee/
        public ActionResult Index()
        {
            return View(db.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()
        {
            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,LastName,FirstName,JoiningDate")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Employees.Add(employee);
                db.SaveChanges();
                return RedirectToAction("Index");
            }

            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();
            }
            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,LastName,FirstName,JoiningDate")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                db.Entry(employee).State = EntityState.Modified;
                db.SaveChanges();
                return RedirectToAction("Index");
            }
            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);
        }
    }
}

Add links to new views

To make it easier for you to navigate to your new views, you can add a couple of hyperlinks to the Index views for students and enrollments. Open the file at Views/Home/Index.cshtml, which is the home page for your site. Add the following code.

@Html.ActionLink("List of employee", "Index", "Employee") @Html.ActionLink("List of enrollments", "Index", "Enrollment")

Conclusion

In this tutorial, we learnt to setup environment for MVC 5 and Entity Framework 6 Code First and perform CRUD operations on Employee and Enrollment model.