CRUD Operation Using Entity Framework Code-First

Introduction

We are familiar with “Creating DB first” when we see all the tutorials on Entity Framework on the web. But in big projects where concurrency is involved and for other security reasons we really don't rely on the DB first approach. EDMX is an XML file and as a mal-user anybody can change the edmx file and the code at the end will not work. So, it's always better to use the Entity Framework Code-First approach using the Fluent API.

Description

The ADO.NET Entity Framework is an Object Relational Mapper (ORM) included with the .NET Framework. It basically generates business objects and entities depending on the database tables. It provides basic CRUD operations, easily managing relationships among entities with the ability to have an inheritance relationship among entities. When using the EF we interact with an entity model instead of the application's relational database model. This abstraction allows us to focus on business behavior and the relationships among entities. We use the Entity Framework data context to do queries. When one of the CRUD operations is invoked, the Entity Framework will generate the necessary SQL to do the operation.

What is CF?

Whether you have your relational database is in place or not, you can always define your own model classes and their properties. The beauty of this approach is there are no specified configuration files to store the DB schema and no mapping. The mapping will be generated at runtime. Also, there is no mapping to Stored Procedures. I will show later how you can call Store Procedures in the CF approach in the next article.

The Code

So, we will create our Model class first and will then create the table in the database. Let's first create the two model classes as in the following:

using System.Collections.Generic;
namespace ExampleCF.Models
{
    public class Teacher
    {
        public int TeacherId { get; set; }
        public string TeacherName { get; set; }
        public string Address { get; set; }
        public ICollection<Student> Students { get; set; }
    }
}

namespace ExampleCFA.Models
{
    public class Student
    {
        public int StudentId { get; set; }
        public string Name { get; set; }
        public string Class { get; set; }
        public int TeacherId { get; set; }
        public Teacher Teacher { get; set; }
    }
}

Create DB Connection

This is the heart of Code-First Approach. First of all we need a connection string, so we can connect to our database from our application. As in traditional ASP.NET, we specify this connection in the WebConfig file as in the following:

<connectionStrings>
    <add name="DbConnectionString"
      connectionString="Data Source=mypc-PC;Initial Catalog=CodeFirst;User ID=sa;
      Password=*******" providerName="System.Data.SqlClient" />
</connectionStrings>

We will now create our DBContext class. For the code-first approach we need to inherit from the DBContext base class and then override the OnModelCreating method. OnModelCreating is the method where we specify all the mappings as shown below (note that the connection string is passed through the constructor):

using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
namespace ExampleCFA.Models
{
    public class SchoolContext :DbContext
    {
        public SchoolContext()
            : base("name=DbConnectionString")
        {
        }
        public DbSet<Teacher> Publishers { get; set; }
        public DbSet<Student> Students { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Teacher>().HasKey(t => t.TeacherId); //primary key defination
            modelBuilder.Entity<Teacher>().Property(t => t.TeacherrId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);  //identity col
            modelBuilder.Entity<Student>().HasKey(s => s.StudentId);
            modelBuilder.Entity<Student>().Property(s => s.StudentId)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
            modelBuilder.Entity<Student>().HasRequired(s => s.Student)
                .WithMany(s => s.Students).HasForeignKey(s=>s.TeacherId); //Foreign Key
            base.OnModelCreating(modelBuilder);
        }
    }
}

This DbModelBuilder class maps POCO classes to the database schema. This method is called only once when the first instance of a derived context is created. The model for that context is then cached and is for all further instances of the context in the app domain. This caching can be disabled by setting the ModelCaching property on the given ModelBuidler, but this can seriously degrade performance. More control over caching is provided through use of the DbModelBuilder and DbContext classes directly.

Create Controllers for CRUD operation:

using System.Linq;
using System.Web.Mvc;
using ExampleCodeFirstApproch.Models;
namespace ExampleCFA.Controllers
{
    public class TeacherController : Controller
    {
        SchoolContext objContext;
        public TeacherController()
        {
            objContext = new SchoolContext();
        }
        #region List and Details Teacher
        public ActionResult Index()
        {
            var teachers = objContext.Teachers.ToList();
            return View(teachers);
        }
        public ViewResult Details(int id)
        {
            Teacher teacher =
              objContext.Teachers.Where(x=>x.TeacherId==id).SingleOrDefault();
            return View(teacher);
        }
        #endregion
        #region Create Teacher
        public ActionResult Create()
        {
            return View(new Teacher());
        }
        [HttpPost]
        public ActionResult Create(Teacher teacher)
        {
            objContext.Teachers.Add(teacher);
            objContext.SaveChanges();
            return RedirectToAction("Index");
        }
        #endregion
        #region edit teacher
        public ActionResult Edit(int id)
        {
            Teacher teacher = objContext.Teachers.Where(
              x => x.TeacherId == id).SingleOrDefault();
            return View(teacher);
        }
        [HttpPost]
        public ActionResult Edit(Teacher model)
        {
            Teacher teacher = objContext.Teachers.Where(
              x => x.TeacherId == model.TeacherId).SingleOrDefault();
            if (teacher != null)
            {
                objContext.Entry(teacher).CurrentValues.SetValues(model);
                objContext.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(model);
        }
       #endregion
        #region Delete Teacher
        public ActionResult Delete(int id)
        {
            Teacher teacher = objContext.Teachers.Find(id);

            return View(publisher);
        }
        [HttpPost]
        public ActionResult Delete(int id, Teacher model)
        {
           var teacher =
             objContext.Teachers.Where(x => x.TeacherId == id).SingleOrDefault();
           if (teacher != null)
            {
                objContext.Teachers.Remove(teacher);
                objContext.SaveChanges();
            }
            return RedirectToAction("Index");
        }
        #endregion
    }
}

using System.Linq;
using System.Web.Mvc;
using ExampleCodeFirstApproch.Models;
namespace ExampleCFA.Controllers
{
    public class StudentController : Controller
    {
       SchoolContext objContext;
       public StudentController()
        {
            objContext = new SchoolContext();
        }
        #region List and Details Students
        public ActionResult Index()
        {
            var studs = objContext.Students.ToList();
            return View(studs);
        }
        public ViewResult Details(int id)
        {
            Student stud = objContext.Students.Where(x=>x.StudentId==id).SingleOrDefault();
            return View(stud);
        }
        #endregion
        #region Create Student
        public ActionResult Create()
        {
            return View(new Student());
        }
        [HttpPost]
        public ActionResult Create(Student stud)
        {
            objContext.Students.Add(stud);
            objContext.SaveChanges();
            return RedirectToAction("Index");
        }
        #endregion
        #region Edit Student
        public ActionResult Edit(int id)
        {
            Student stud = objContext.Students.Where(x => x.StudentId == id).SingleOrDefault();
            return View(stud);
        }
        [HttpPost]
        public ActionResult Edit(Student model)
        {
            Student stud = objContext.Students.Where(x => x.StudentId == model.StudentId).SingleOrDefault();
            if (stud != null)
            {
                objContext.Entry(stud).CurrentValues.SetValues(model);
                objContext.SaveChanges();
                return RedirectToAction("Index");
            }
            return View(stud);
        }
       #endregion
        #region Delete Student
        public ActionResult Delete(int id)
        {
            Student stud = objContext.Students.Find(id);
            return View(book);
        }
        [HttpPost]
        public ActionResult Delete(int id, Student model)
        {
           var stud = objContext.Students.Where(x => x.StudentId == id).SingleOrDefault();
           if (stud != null)
            {
                objContext.Students.Remove(stud);
                objContext.SaveChanges();
            }
            return RedirectToAction("Index");
        }
        #endregion
    }
}

I hope this helps.