Updating Related Data with the Entity Framework in an ASP.NET MVC Application Chapter6

In the previous tutorial you displayed related data; in this tutorial you'll update related data. For most relationships, this can be done by updating the appropriate foreign key fields. For many-to-many relationships, the Entity Framework doesn't expose the join table directly, so you must explicitly add and remove entities to and from the appropriate navigation properties.
The following illustrations show the pages that you'll work with.

Customizing the Create and Edit Pages for Courses

When a new course entity is created, it must have a relationship to an existing department. To facilitate this, the scaffolded code includes controller methods and Create and Edit views that include a drop-down list for selecting the department. The drop-down list sets the Course.DepartmentID foreign key property, and that is all the Entity Framework needs in order to load the Department navigation property with the appropriate Department entity. You'll use the scaffolded code, but change it slightly to add error handling and sort the drop-down list.
In CourseController.cs, delete the four Edit and Create methods and replace them with the following code:

public ActionResult Create()
{
    PopulateDepartmentsDropDownList();
    return View();
}
[HttpPost]
public ActionResult Create(Course course)
{
    try
    {
        if (ModelState.IsValid)
        {
            db.Courses.Add(course);
            db.SaveChanges();
            return RedirectToAction("Index");
        }
    }
    catch (DataException)
    {
        //Log the error (add a variable name after DataException)
        ModelState.AddModelError("", "Unable to save changes. Try again, and if the 

problem persists, see your system administrator.");
    }
    PopulateDepartmentsDropDownList(course.DepartmentID);
    return View(course);
}
public ActionResult Edit(int id)
{
    Course course = db.Courses.Find(id);
    PopulateDepartmentsDropDownList(course.DepartmentID);
    return View(course);
}
[HttpPost]
public ActionResult Edit(Course course)
{
    try
    {
        if (ModelState.IsValid)
        {
            db.Entry(course).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index");
        }
    }
    catch (DataException)
    {
        //Log the error (add a variable name after DataException)
        ModelState.AddModelError("", "Unable to save changes. Try again, and if the 

problem persists, see your system administrator.");
    }
    PopulateDepartmentsDropDownList(course.DepartmentID);
    return View(course);
}
private void PopulateDepartmentsDropDownList(object selectedDepartment = null)
{
    var departmentsQuery = from d in db.Departments
                           orderby d.Name
                           select d;
    ViewBag.DepartmentID = new SelectList(departmentsQuery, "DepartmentID", "Name", 

selectedDepartment);
}

The PopulateDepartmentsDropDownList method gets a list of all departments sorted by name, creates a SelectList collection for a drop-down list, and passes the collection to the view in a ViewBag property. The method accepts a parameter that allows the caller to optionally specify the item that will be selected initially when the drop-down list is rendered.
The HttpGet Create method calls the PopulateDepartmentsDropDownList method without setting the selected item, because for a new course the department is not established yet:

public ActionResult Create()
{
    PopulateDepartmentsDropDownList();
    return View();
}

The HttpGet Edit method sets the selected item, based on the ID of the department that is already assigned to the course being edited:

public ActionResult Edit(int id)
{
    Course course = db.Courses.Find(id);
    PopulateDepartmentsDropDownList(course.DepartmentID);
    return View(course);
}

The HttpPost methods for both Create and Edit also include code that sets the selected item when they redisplay the page after an error:

 catch (DataException)
{
    //Log the error (add a variable name after DataException)
    ModelState.AddModelError("", "Unable to save changes. Try again, and if the 

problem persists, see your system administrator.");
}
PopulateDepartmentsDropDownList(course.DepartmentID);
return View(course);

This code ensures that when the page is redisplayed to show the error message, whatever department was selected stays selected.
In Views\Course\Create.cshtml, add a new field before the Title field to allow the user to enter the course number. As explained in an earlier tutorial, primary key fields aren't scaffolded, but this primary key is meaningful, so you want the user to be able to enter the key value.

<div class="editor-label">
    @Html.LabelFor(model => model.CourseID)</div>
<div class="editor-field">
    @Html.EditorFor(model => model.CourseID)
    @Html.ValidationMessageFor(model => model.CourseID)</div>

In Views\Course\Edit.cshtml, Views\Course\Delete.cshtml, and Views\Course\Details.cshtml, add a new field before the Title field to display the course number. Because it's the primary key, it's displayed, but it can't be changed.

<div class="editor-label">
    @Html.LabelFor(model => model.CourseID)</div>
<div class="editor-field">
    @Html.DisplayFor(model => model.CourseID)</div>

Run the Create page (display the Course Index page and click Create New) and enter data for a new course:

Click Create. The Course Index page is displayed with the new course added to the list. The department name in the Index page list comes from the navigation property, showing that the relationship was established correctly.

Run the Edit page (display the Course Index page and click Edit on a course).

Change data on the page and click Save. The Course Index page is displayed with the updated course data.

Adding an Edit Page for Instructors

When you edit an instructor record, you want to be able to update the instructor's office assignment. The Instructor entity has a one-to-zero-or-one relationship with the OfficeAssignment entity, which means you must handle the following situations:

• If the user clears the office assignment and it originally had a value, you must remove and delete the OfficeAssignment entity.
• If the user enters an office assignment value and it originally was empty, you must create a new OfficeAssignment entity.
• If the user changes the value of an office assignment, you must change the value in an existing OfficeAssignment entity.

Open InstructorController.cs and look at the HttpGet Edit method:

public ActionResult Edit(int id)
{
    Instructor instructor = db.Instructors.Find(id);
    ViewBag.InstructorID = new SelectList(db.OfficeAssignments, "InstructorID", 

 "Location", instructor.InstructorID);
    return View(instructor);
}

The scaffolded code here isn't what you want. It's setting up data for a drop-down list, but you what you need is a text box. Replace this method with the following code:

public ActionResult Edit(int id)
{
    Instructor instructor = db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses)
        .Where(i => i.InstructorID == id)
        .Single();
    return View(instructor);
}

This code drops the ViewBag statement and adds eager loading for associated OfficeAssignment and Course entities. (You don't need Courses now, but you'll need it later.) You can't perform eager loading with the Find method, so the Where and Single methods are used instead to select the instructor.
Replace the HttpPost Edit method with the following code. which handles office assignment updates:

[HttpPost]
public ActionResult Edit(int id, FormCollection formCollection)
{
    var instructorToUpdate = db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses)
        .Where(i => i.InstructorID == id)
        .Single();
    if (TryUpdateModel(instructorToUpdate, "", null, new string[] { "Courses" }))
    {
        try
        {
            if (String.IsNullOrWhiteSpace(instructorToUpdate.OfficeAssignment.Location))
            {
                instructorToUpdate.OfficeAssignment = null;
            }

            db.Entry(instructorToUpdate).State = EntityState.Modified;
            db.SaveChanges();

            return RedirectToAction("Index");
        }
        catch (DataException)
        {
            //Log the error (add a variable name after DataException)
            ModelState.AddModelError("", "Unable to save changes. Try again, 

and if the problem persists, see your system administrator.");
            return View();
        }
    }
    return View(instructorToUpdate);
}

The code does the following:

• Gets the current Instructor entity from the database using eager loading for the OfficeAssignment and Courses navigation properties. This is the same as what you did in the HttpGet Edit method.
  
• Updates the retrieved Instructor entity with values from the model binder, excluding the Courses navigation property:
  

  If (TryUpdateModel(instructorToUpdate, "", null, new string[] { "Courses" }))

  (The second and third parameters specify no prefix on the property names and no list of properties to include.) If validation fails, TryUpdateModel returns false, and the code falls through to the return View statement at the end of the method.
  
• If the office location is blank, sets the Instructor.OfficeAssignment property to null so that the related row in the OfficeAssignment table will be deleted.
  

  if (String.IsNullOrWhiteSpace(instructorToUpdate.OfficeAssignment.Location))
  {
      instructorToUpdate.OfficeAssignment = null;
  }

• Saves the changes to the database.

In Views\Instructor\Edit.cshtml, after the div elements for the Hire Date field, add a new field for editing the office location:

<div class="editor-label">
    @Html.LabelFor(model => model.OfficeAssignment.Location)</div>
<div class="editor-field">
    @Html.EditorFor(model => model.OfficeAssignment.Location)
    @Html.ValidationMessageFor(model => model.OfficeAssignment.Location)</div>

Run the page (select the Instructors tab and then click Edit on an instructor).

Change the Office Location and click Save.

The new location appears on the Index page, and you can see the table row when you open the OfficeAssignment table in Server Explorer.

Return to the Edit page, clear the Office Location and click Save. The Index page shows a blank office location and Server Explorer shows that the row has been deleted.

Return to the Edit page, enter a new value in the Office Location and click Save. The Index page shows the new location, and Server Explorer shows that a row has been created.

Adding Course Assignments to the Instructor Edit Page

Instructors may teach any number of courses. You'll now enhance the Instructor Edit page by adding the ability to change course assignments using a group of check boxes, as shown in the following screen shot:

The relationship between the Course and Instructor entities is many-to-many, which means you do not have direct access to the join table or foreign key fields. Instead, you will add and remove entities to and from the Instructor.Courses navigation property.
The UI that enables you to change which courses an instructor is assigned to is a group of check boxes. A check box for every course in the database is displayed, and the ones that the instructor is currently assigned to are selected. The user can select or clear check boxes to change course assignments. If the number of courses were much greater, you probably would want to use a different method of presenting the data in the view, but you'd use the same method of manipulating navigation properties in order to create or delete relationships.
To provide data to the view for the list of check boxes, you'll use a view model class. Create AssignedCourseData.cs in the ViewModels folder and replace the existing code with the following code:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ContosoUniversity.ViewModels
{
    public class AssignedCourseData
    {
        public int CourseID { get; set; }
        public string Title { get; set; }
        public bool Assigned { get; set; }
    }
}

In InstructorController.cs, in the HttpGet Edit method, call a new method that provides information for the check box array using the new view model class, as shown in the following example:

public ActionResult Edit(int id)
{
    Instructor instructor = db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses)
        .Where(i => i.InstructorID == id)
        .Single();
    PopulateAssignedCourseData(instructor);
    return View(instructor);
}
private void PopulateAssignedCourseData(Instructor instructor)
{
    var allCourses = db.Courses;
    var instructorCourses = new HashSet<int>(instructor.Courses.Select(c => c.CourseID));
    var viewModel = new List<AssignedCourseData>();
    foreach (var course in allCourses)
    {
        viewModel.Add(new AssignedCourseData
        {
            CourseID = course.CourseID,
            Title = course.Title,
            Assigned = instructorCourses.Contains(course.CourseID)
        });
    }
    ViewBag.Courses = viewModel;
}

The code in the new method reads through all Course entities in order to load a list of courses using the view model class. For each course, the code checks whether the course exists in the instructor's Courses navigation property. To create efficient lookup when checking whether a course is assigned to the instructor, the courses assigned to the instructor are put into a HashSet collection. The Assigned property of courses that are assigned to the instructor is set to true. The view will use this property to determine which check boxes must be displayed as selected. Finally, the list is passed to the view in a ViewBag property.
Next, add the code that's executed when the user clicks Save. Replace the HttpPost Edit method with the following code, which calls a new method that updates the Courses navigation property of the Instructor entity.

[HttpPost]
public ActionResult Edit(int id, FormCollection formCollection, string[] selectedCourses)
{
    var instructorToUpdate = db.Instructors
        .Include(i => i.OfficeAssignment)
        .Include(i => i.Courses)
        .Where(i => i.InstructorID == id)
        .Single();
    if (TryUpdateModel(instructorToUpdate, "", null, new string[] { "Courses" }))
    {
        try
        {
            if (String.IsNullOrWhiteSpace(instructorToUpdate.OfficeAssignment.Location))
            {
                instructorToUpdate.OfficeAssignment = null;
            }

            UpdateInstructorCourses(selectedCourses, instructorToUpdate);

            db.Entry(instructorToUpdate).State = EntityState.Modified;
            db.SaveChanges();

            return RedirectToAction("Index");
        }
        catch (DataException)
        {
            //Log the error (add a variable name after DataException)
            ModelState.AddModelError("", "Unable to save changes. Try again, 

and if the problem persists, see your system administrator.");
       }
    }
    PopulateAssignedCourseData(instructorToUpdate);
    return View(instructorToUpdate);
}
private void UpdateInstructorCourses(string[] selectedCourses, Instructor instructorToUpdate)
{
    if (selectedCourses == null)
    {
        instructorToUpdate.Courses = new List<Course>();
        return;
    }

    var selectedCoursesHS = new HashSet<string>(selectedCourses);
    var instructorCourses = new HashSet<int>
        (instructorToUpdate.Courses.Select(c => c.CourseID));
    foreach (var course in db.Courses)
    {
        if (selectedCoursesHS.Contains(course.CourseID.ToString()))
        {
            if (!instructorCourses.Contains(course.CourseID))
            {
                instructorToUpdate.Courses.Add(course);
            }
        }
        else
        {
            if (instructorCourses.Contains(course.CourseID))
            {
                instructorToUpdate.Courses.Remove(course);
            }
        }
    }
}

If no check boxes were selected, the code in UpdateInstructorCourses initializes the Courses navigation property with an empty collection:

if (selectedCourses == null)
{
    instructorToUpdate.Courses = new List();
    return;
}

The code then loops through all courses in the database. If the check box for a course was selected but the course isn't in the Instructor.Courses navigation property, the course is added to the collection in the navigation property.

if (selectedCoursesHS.Contains(course.CourseID.ToString()))
{
    if (!instructorCourses.Contains(course.CourseID))
    {
        instructorToUpdate.Courses.Add(course);
    }
}

If a course wasn't selected, but the course is in the Instructor.Courses navigation property, the course is removed from the navigation property.

else
{
    if (instructorCourses.Contains(course.CourseID))
    {
        instructorToUpdate.Courses.Remove(course);
    }
}

In Views\Instructor\Edit.cshtml, add a Courses field with an array of check boxes by adding the following code immediately after the div elements for the OfficeAssignment field:

<div class="editor-field">
    <table>
        <tr>
            @{
                int cnt = 0;
                List<ContosoUniversity.ViewModels.AssignedCourseData> 

courses = ViewBag.Courses;

                foreach (var course in courses) {
                    if (cnt++ % 3 == 0) {
                        @:  </tr> <tr> 
                    }
                    @: <td> 
                        <input type="checkbox" 
                               name="selectedCourses" 
                               value="@course.CourseID" 
                               @(Html.Raw(course.Assigned ? "checked=\"checked\"" : "")) /> 
                        @course.CourseID @:  @course.Title
                    @:</td>
                }
                @: </tr>
            }
    </table>
</div>

This code creates an HTML table that has three columns. In each column is a check box followed by a caption that consists of the course number and title. The check boxes all have the same name ("selectedCourses"), which informs the model binder that they are to be treated as a group. The value attribute of each check box is set to the value of CourseID. When the page is posted, the model binder passes an array to the controller that consists of the CourseID values for only the check boxes which are selected.
When the check boxes are initially rendered, those that are for courses already assigned to the instructor have checked attributes, which selects them.
After changing course assignments, you'll want to be able to verify the changes when the site returns to the Index page. Therefore, you need to add a column to the table in that page. In this case you don't need to use the ViewBag object, because the information you want to display is already in the Courses navigation property of the Instructor entity that you're passing to the page as the model.
In Views\Instructor\Index.cshtml, add a <th>Courses</th> heading cell immediately following the <th>Office</th> heading, as shown in the following example:

<tr> 
    <th></th> 
    <th>Last Name</th> 
    <th>First Name</th> 
    <th>Hire Date</th> 
    <th>Office</th>
    <th>Courses</th>
</tr> 

Then add a new detail cell immediately following the office location detail cell:

<td>
    @{
        foreach (var course in item.Courses)
        {
            @course.CourseID @:  @course.Title <br />
        }
    }</td>

Run the Instructor Index page to see the courses assigned to each instructor:

Click Edit on an instructor to see the Edit page.

Change some course assignments and click Save. The changes you make are reflected on the Index page.
You have now completed this introduction to working with related data. So far in these tutorials you've done a full range of CRUD operations, but you haven't dealt with concurrency issues. The next tutorial will introduce the topic of concurrency, explain options for handling it, and add concurrency handling to the CRUD code you've already written for one entity type.