Entity Framework - Database Operations

---

In the previous chapters, you learned three different ways of defining an entity data model.

• Two of them, Database First and Model First, depended on the Entity Framework designer combined with code generation.

• The third, Code First, lets you skip a visual designer and just write your own code.

• Regardless of which path you choose, you'll end up with domain classes and one or more Entity Framework DbContext classes allows you to retrieve and persist data relevant to those classes.

The DbContext API in your applications is used as a bridge between your classes and your database. The DbContext is one of the most important classes in the Entity Framework.

• It enables to express and execute queries.

• It takes query results from the database and transforms them into instances of our model classes.

• It can keep track of changes to entities, including adding and deleting, and then triggers the creation of insert, update and delete statements that are sent to the database on demand.

Following are the domain ad context classes on which we will be performing different operations in this chapter. This is the same example which we have created in the chapater, Database First Approach.

Context Class Implementation

using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Objects;
using System.Linq;

namespace DatabaseFirstDemo {

   public partial class UniContextEntities : DbContext {

      public UniContextEntities(): base("name = UniContextEntities") {}

      protected override void OnModelCreating(DbModelBuilder modelBuilder) {
         throw new UnintentionalCodeFirstException();
      }

      public virtual DbSet<Course> Courses { get; set; }
      public virtual DbSet<Enrollment> Enrollments { get; set; }
      public virtual DbSet<Student> Students { get; set; }
   }
}

Domain Classes Implementation

Course class

namespace DatabaseFirstDemo {

   using System;
   using System.Collections.Generic;
	
   public partial class Course {

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
         "CA2214:DoNotCallOverridableMethodsInConstructors")]

      public Course() {
         this.Enrollments = new HashSet<Enrollment>();
      }
	
      public int CourseID { get; set; }
      public string Title { get; set; }
      public int Credits { get; set; }
	
      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
         "CA2227:CollectionPropertiesShouldBeReadOnly")]
			
      public virtual ICollection<Enrollment> Enrollments { get; set; }
   }
}

Student class

namespace DatabaseFirstDemo {

   using System;
   using System.Collections.Generic; 

   public partial class Student {

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
         "CA2214:DoNotCallOverridableMethodsInConstructors")]

      public Student() {
         this.Enrollments = new HashSet<Enrollment>();
      }

      public int ID { get; set; }
      public string LastName { get; set; }
      public string FirstMidName { get; set; }
      public System.DateTime EnrollmentDate { get; set; }

      [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", 
         "CA2227:CollectionPropertiesShouldBeReadOnly")]
			
      public virtual ICollection<Enrollment> Enrollments { get; set; }
   }
}

Enrollment class

namespace DatabaseFirstDemo {

   using System;
   using System.Collections.Generic; 

   public partial class Enrollment {

      public int EnrollmentID { get; set; }
      public int CourseID { get; set; }
      public int StudentID { get; set; }
      public Nullable<int> Grade { get; set; }
		
      public virtual Course Course { get; set; }
      public virtual Student Student { get; set; }
   }
}

Create Operation

Adding a new object with Entity Framework is as simple as constructing a new instance of your object and registering it using the Add method on DbSet. The following code lets you add a new student to the database.

class Program {

   static void Main(string[] args) {

      var newStudent = new Student();

      //set student name

      newStudent.FirstMidName = "Bill";
      newStudent.LastName = "Gates";
      newStudent.EnrollmentDate = DateTime.Parse("2015-10-21");
      newStudent.ID = 100;

      //create DBContext object

      using (var dbCtx = new UniContextEntities()) {

         //Add Student object into Students DBset
         dbCtx.Students.Add(newStudent);

         // call SaveChanges method to save student into database
         dbCtx.SaveChanges();
      }
   }
}

Update Operation

Changing existing objects is as simple as updating the value assigned to the property(s) you want changed and calling SaveChanges. For example, the following code is used to change the last name of Ali from Khan to Aslam.

using (var context = new UniContextEntities()) {

   var student = (from d in context.Students where d.FirstMidName == "Ali" select d).Single();
   student.LastName = "Aslam";
   context.SaveChanges();
}

Delete Operation

To delete an entity using Entity Framework, you use the Remove method on DbSet. Remove works for both existing and newly added entities. Calling Remove on an entity that has been added but not yet saved to the database will cancel the addition of the entity. The entity is removed from the change tracker and is no longer tracked by the DbContext. Calling Remove on an existing entity that is being change-tracked will register the entity for deletion the next time SaveChanges is called. The following example is of a code where the student is removed from the database whose first name is Ali.

using (var context = new UniContextEntities()) {
   var bay = (from d in context.Students where d.FirstMidName == "Ali" select d).Single();
   context.Students.Remove(bay);
   context.SaveChanges();
}

Read Operation

Reading the existing data from the database is very simple. Following is the code in which all the data from the Student table are retrieved and then a program will be displayed with the students’ first and last name in alphabetical order.

using (var db = new UniContextEntities()) {

   var query = from b in db.Students orderby b.FirstMidName select b;
   Console.WriteLine("All All student in the database:");

   foreach (var item in query) {
      Console.WriteLine(item.FirstMidName +" "+ item.LastName);
   }

   Console.WriteLine("Press any key to exit...");
   Console.ReadKey();
}