Lerning Entity Framework 6 ------ Inserting, Querying, Updating, and Deleting Data

---

Creating Entities

First of all, Let's create some entities to have a test.

1. Create a project
2. Add following packages by NuGet

• EntityFramework
• MySql.Data.Entity (I'm just using MySql, it's not necessary)

1. Add some codes:

    class Class
    {
        public int ClassId { get; set; }
   
        [MaxLength(50)]
        public string ClassName { get; set; }
   
        public virtual ICollection<Student> Students { get; set; }
    }
   
    class Student
    {
        public int StudentId { get; set; }
   
        [MaxLength(50)]
        public string StudentName { get; set; }
   
        public int Age { get; set; }
   
        public virtual Class Class { get; set; }
   
        public virtual ICollection<Course> Courses { get; set; }
   
        public virtual ICollection<Phone> Phones { get; set; }
    }
   
    class Phone
    {
        public int phoneId { get; set; }
   
        [MaxLength(20)]
        public string PhoneNumber { get; set; }
    }
   
    class Course
    {
        public int CourseId { get; set; }
   
        [MaxLength(50)]
        public string CourseName { get; set; }
   
        public virtual ICollection<Student> Students { get; set; }
    }
   
    class MyContext:DbContext
    {
        public MyContext():base("name=Test")
        {
   
        }
   
        public DbSet<Class> Classes { get; set; }
   
        public DbSet<Student> Students { get; set; }
   
        public DbSet<Course> Courses { get; set; }
    }

2. Then, Execute following commands in NuGet command line

• Enalbe-Migrations
• Add-Migration init
• Update-Database

Inserting

Add some codes in main function:

static void Main(string[] args)
{
    Class class1 = new Class { ClassName = "Class One", };

    Course course1 = new Course { CourseName = "English", };

    Course course2 = new Course { CourseName = "Chinese", };

    Student s1 = new Student
    {
        Age = 18,
        Class = class1,
        Courses = new List<Course> { course1, course2 },
        Phones = new List<Phone> {
                 new Phone { PhoneNumber = "13718431702"},
                 new Phone { PhoneNumber = "13733423722" } },
        StudentName = "Joye"
    };

    Student s2 = new Student
    {
        Age = 19,
        Class = class1,
        Courses = new List<Course> { course1 },
        Phones = new List<Phone> {
                 new Phone { PhoneNumber = "13708431702"},
                 new Phone { PhoneNumber = "13783423722" } },
        StudentName = "Ross"
    };

    Student s3 = new Student
    {
        Age = 17,
        Class = class1,
        Courses = new List<Course> { course2 },
        Phones = new List<Phone> { new Phone { PhoneNumber = "13708431702" } },
        StudentName = "Monnica"
    };

    using (MyContext db = new MyContext())
    {
        db.Students.Add(s1);
        db.Students.Add(s2);
        db.Students.Add(s3);

        db.SaveChanges();
    }
}

I've created one class, two courses, three students and five phone numbers. Then, I add the three studengs to the Studengs DbSet and called the SaveChanges function. That all I did. Maybe you will say: Why don't we need to add all of the entities to the Dbset. When Entity Framework saves a entity, it also saves the whole object graph. How cool it is.

Querying

Filtering data in queries

using (MyContext db = new MyContext())
{
    var students = db.Students.Where(s => s.Age > 17);
    foreach (var item in students)
    {
        Console.WriteLine(item.StudentName + " " + item.Age);
    }
}
Console.Read();

You can do this by LINQ too.

Sorting data in queries

using (MyContext db = new MyContext())
{
    var students = db.Students
        .OrderBy(s => s.Age)
        .ThenBy(s => s.StudentName);

    foreach (var item in students)
    {
        Console.WriteLine(item.StudentName + " " + item.Age);
    }
}
Console.Read();

Working with related entities

static void Main(string[] args)
{
    using (MyContext db = new MyContext())
    {
        var students = from s in db.Students
                       where s.Courses.Any(c => c.CourseName == "Chinese")
                       select s;

        foreach (var item in students)
        {
            Console.WriteLine(item.StudentName + " " + item.Age);
        }
    }

    Console.Read();
}

Loading Related Entities

there are three ways of loading related entities:

Lazy Loading

This way is the default way of Entity Framework 6. Lazy loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed.(MSND) There are two rules you must pay attention to:

1. The model must be defined as public
2. the navigation property must be defined as virtual

For example:

using (MyContext db = new MyContext())
{
    Student oneStudent = db.Students.Find(1); //query the database for the first time
    foreach (var item in oneStudent.Phones) //query the database for the second time
    {
        Console.WriteLine(item.PhoneNumber); 
    }
}

You can turn lazy loading off by two ways:

1. Remove the public key of model or remove the virtual key of navigation property
2. Set the Configuration.LazyLoadingEnabled property of DbContext flase

Eagerly Loading

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.(MSND) For example:

using (MyContext db = new MyContext())
{
    db.Configuration.LazyLoadingEnabled = false;

    var students = from s in db.Students.Include(s => s.Phones)
                   where s.StudentName == "Joye"
                   select s;

    foreach (var s in students)
    {
        foreach (var p in s.Phones)
        {
            Console.WriteLine(p.PhoneNumber);
        }
    }
}

---

using (MyContext db = new MyContext())
{
    db.Configuration.LazyLoadingEnabled = false;

    var courses = from c in db.Courses.Include(cc => cc.Students.Select(s => s.Phones))
                  where c.CourseName == "English"
                  select c;

    Console.WriteLine(courses.First().Students.First().Phones.First().PhoneNumber);
}

Explicitly Loading

Even with lazy loading disabled it is still possible to lazily load related entities, but it must be done with an explicit call. To do so you use the Load method on the related entity’s entry.(MSND) For example:

using (MyContext db = new MyContext())
{
    db.Configuration.LazyLoadingEnabled = false;

    Course c = db.Courses.Find(1);
    db.Entry(c)
        .Collection(cc => cc.Students)
        .Load();

    Console.WriteLine(c.Students.Count);
}

If the navigation property is a single entity, please use Reference method. If the navigation property is a collection of entities, please use method Collection.

Updating

using (MyContext db = new MyContext())
{
    var sutdent = db.Students.Find(1);
    sutdent.StudentName = "Joey";
    db.SaveChanges();
}

Or:

using (MyContext db = new MyContext())
{
    var sutdent = new Student
    {
        StudentId = 1,
        StudentName = "Joeyy"
    };

    db.Entry(sutdent).State = EntityState.Modified;
    db.SaveChanges();
}

Deleting

using (MyContext db = new MyContext())
{
    var student = db.Students.Find(1);
    var course = db.Courses.Find(1);

    course.Students.Remove(student);
    db.SaveChanges();
}

After you run the codes, one of the rows of table coursestudents is deleted. If you remove a entity from db.Students, one of the rows of table people will be deleted.

You can also use entry method:

using (MyContext db = new MyContext())
{
    var phone = new Phone { phoneId = 1 };
    db.Entry(phone).State = EntityState.Deleted;
    db.SaveChanges();
}

That's all.