MVC 5 with EF 6 in Visual Basic - Implementing Basic CRUD Functionality

The original tutorial series, produced by Tom Dykstra and Rick Anderson ( @RickAndMSFT ) was written using the C# language. My versions keep as close to the originals as possible, changing only the coding language. The narrative text is largely unchanged from the original and is used with permission from Microsoft.

This tutorial series teaches you how to create ASP.NET MVC 5 applications using the Entity Framework 6 and Visual Studio 2013 Express for Web. This tutorial uses the Code First workflow. For information about how to choose between Code First, Database First, and Model First, see Entity Framework Development Workflows.

The tutorial series comprises 12 sections in total. They are intended to be followed sequentially as each section builds on the knowledge imparted in the previous sections. Progress through the sections is reflected in a Visual Studio Express for Web project download that accompanies each section which features the web application that you build through the series.

Download the code

The code for this section is available here. Save the .zip file to a convenient location and then extract the contents. Make sure you have an edition of Visual Studio 2013 installed (Express for Web, Professional, Premium or Ultimate) and double click the .sln file. Once the project is opened in your IDE, press Shift+Ctrl+B to build the solution. This will ensure that all packages are restored from Nuget and may take a while depending on your Internet connection speed.

The navigation path through the series is as follows:

1. Creating an Entity Framework Data Model
2. Implementing Basic CRUD Functionality
3. Sorting, Filtering and Paging
4. Connection Resiliency and Command Interception
5. Code First Migrations and Deployment
6. Creating a More Complex Data Model
7. Reading Related Data
8. Updating Related Data
9. Async and Stored Procedures
10. Handling Concurrency
11. Implementing-Inheritance
12. Advanced Entity Framework Scenarios

In this tutorial, you'll create the following web pages:

Create a Details Page

The scaffolded code for the Students Index page left out the Enrollments property, because that property holds a collection. In the Details page you'll display the contents of the collection in an HTML table.

In Controllers\StudentController.vb the action method for the Details view uses the Find method to retrieve a single Student entity.

Function Details(ByVal id As Integer?) As ActionResult
    If IsNothing(id) Then
        Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
    End If
    Dim student As Student = db.Students.Find(id)
    If IsNothing(student) Then
        Return HttpNotFound()
    End If
    Return View(student)
End Function

The key value is passed to the method as the id parameter and comes from route data in the Details hyperlink on the Index page.

 routes.MapRoute(
      name: "Default",
      url: "{controller}/{action}/{id}",
      defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
     );

http://localhost:1230/Instructor/Index/1?courseID=2021

 @Html.ActionLink("Select", "Index", new { id = item.PersonID  })

@Html.ActionLink("Select", "Index", new { courseID = item.CourseID }) 

1. Open Views\Student\Details.vbhtml. Each field is displayed using a DisplayFor helper, as shown in the following example:

   <dt>
       @Html.DisplayNameFor(Function(model) model.LastName)
   </dt>
   
   <dd>
       @Html.DisplayFor(Function(model) model.LastName)
   </dd>

2. After the EnrollmentDate field and immediately before the closing </dl> tag, add the highlighted code to display a list of enrollments, as shown in the following example:

           <dt>
               @Html.DisplayNameFor(Function(model) model.EnrollmentDate)
           </dt>
   
           <dd>
               @Html.DisplayFor(Function(model) model.EnrollmentDate)
           </dd>
           <dt>
               @Html.DisplayNameFor(Function(model) model.Enrollments)
           </dt>
           <dd>
               <table class="table">
                   <tr>
                       <th>Course Title</th>
                       <th>Grade</th>
                   </tr>
                   @For Each item In Model.Enrollments
                       @<tr>
                           <td>
                               @Html.DisplayFor(Function(model) item.Course.Title)
                           </td>
                           <td>
                               @Html.DisplayFor(Function(model) item.Grade)
                           </td>
                       </tr>
                  Next
               </table>
           </dd>
       </dl>
   </div>
   <p>
       @Html.ActionLink("Edit", "Edit", New With { .id = Model.ID }) |
       @Html.ActionLink("Back to List", "Index")
   </p>

   If code indentation is wrong after you paste the code, press CTRL-K-D to correct it.

   This code loops through the entities in the Enrollments navigation property. For each Enrollment entity in the property, it displays the course title and the grade. The course title is retrieved from the Course entity that's stored in the Course navigation property of the Enrollments entity. All of this data is retrieved from the database automatically when it's needed. (In other words, you are using lazy loading here. You did not specify eager loading for the Courses navigation property, so the enrollments were not retrieved in the same query that got the students. Instead, the first time you try to access the Enrollments navigation property, a new query is sent to the database to retrieve the data. You can read more about lazy loading and eager loading in the Reading Related Data tutorial later in this series.)

3. Run the page by selecting the Students tab and clicking a Details link for Alexander Carson. (If you press CTRL+F5 while the Details.vbhtml file is open, you'll get an HTTP 400 error because Visual Studio tries to run the Details page but it wasn't reached from a link that specifies the student to display. In that case, just remove "Student/Details" from the URL and try again, or close the browser, right-click the project, and click View, and then click View in Browser.)

   You see the list of courses and grades for the selected student:

Update the Create Page

1. In Controllers\StudentController.vb, replace the HttpPost Create action method with the following code to add a Try-Catch block and remove ID from the Bind attribute for the scaffolded method:

   <HttpPost()>
   <ValidateAntiForgeryToken()>
   Function Create(<Bind(Include := "LastName,FirstMidName,EnrollmentDate")> ByVal student As Student) As ActionResult
       Try
           If ModelState.IsValid Then
               db.Students.Add(student)
               db.SaveChanges()
               Return RedirectToAction("Index")
           End If
       Catch dex As DataException
           'Log the error (add a line here to write a log)
           ModelState.AddModelError("", "Unable to save changes. Try again, and of the problem persists see your system administrator. ")
       End Try
       Return View(student)
   End Function

   This code adds the Student entity created by the ASP.NET MVC model binder to the Students entity set and then saves the changes to the database. (Model binder refers to the ASP.NET MVC functionality that makes it easier for you to work with data submitted by a form; a model binder converts posted form values to CLR types and passes them to the action method in parameters. In this case, the model binder instantiates a Student entity for you using property values from the Form collection.)

   You removed ID from the Bind attribute because ID is the primary key value which SQL Server will set automatically when the row is inserted. Input from the user does not set the ID value.

   Security Note: The ValidateAntiForgeryToken attribute helps prevent cross-site request forgery attacks. It requires a corresponding Html.AntiForgeryToken() statement in the view, which you'll see later.

   The Bind attribute is one way to protect against over-posting. For example, suppose the Student entity includes a Secret property that you don't want this web page to set.

   Namespace Models
       Public Class Student
           Public Property ID As Integer
           Public Property LastName As String
           Public Property FirstMidName As String
           Public Property EnrollmentDate As DateTime
           Public Property Secret As String
   
           Public Overridable Property Enrollments As ICollection(Of Enrollment)
       End Class
   End Namespace

   Even if you don't have a Secret field on the web page, a hacker could use a tool such as fiddler, or write some JavaScript to post a Secret form value. Without the Bind attribute limiting the fields that the model binder uses when it creates a Student instance, the model binder would pick up that Secret form value and use it to create the Student entity instance. Then whatever value the hacker specified for the Secret form field would be inserted into your database. The following image shows the fiddler tool adding the Secret field (with the value "OverPost") to the posted form values.

   

   The value "OverPost" would then be successfully added to the Secret property of the inserted row, although you never intended that the web page be able to set that property.

   It's a security best practice to use the Include parameter with the Bind attribute to whitelist fields. It's also possible to use the Exclude parameter to blacklist fields you want to exclude. The reason Include is more secure is that when you add a new property to the entity, the new field is not automatically protected by an Exclude list.

   You can prevent overposting in edit scenarios is by reading the entity from the database first and then calling TryUpdateModel, passing in an explicit allowed properties list. That is the method used in these tutorials.

   An alternative way to prevent overposting that is preferrred by many developers is to use view models rather than entity classes with model binding. Include only the properties you want to update in the view model. Once the MVC model binder has finished, copy the view model properties to the entity instance, optionally using a tool such as AutoMapper. Use db.Entry on the entity instance to set its state to Unchanged, and then set Property("PropertyName").IsModified to true on each entity property that is included in the view model. This method works in both edit and create scenarios.

   Other than the Bind attribute, the Try-Catch block is the only change you've made to the scaffolded code. If an exception that derives from DataException is caught while the changes are being saved, a generic error message is displayed. DataException exceptions are sometimes caused by something external to the application rather than a programming error, so the user is advised to try again. Although not implemented in this sample, a production quality application would log the exception. For more information, see the Log for insight section in Monitoring and Telemetry (Building Real-World Cloud Apps with Windows Azure).

   The code in Views\Student\Create.vbhtml is similar to what you saw in Details.vbhtml, except that EditorFor and ValidationMessageFor helpers are used for each field instead of DisplayFor. Here is the relevant code:

<div class="form-group">
    @Html.LabelFor(Function(model) model.LastName, New With { .class = "control-label col-md-2" })
    <div class="col-md-10">
        @Html.EditorFor(Function(model) model.LastName)
        @Html.ValidationMessageFor(Function(model) model.LastName)
    </div>
</div>

Create.vbhtml also includes @Html.AntiForgeryToken(), which works with the ValidateAntiForgeryToken attribute in the controller to help prevent cross-site request forgery attacks.

No changes are required in Create.vbhtml. 

2. Run the page by selecting the Students tab and clicking Create New.

3. Enter names and an invalid date and click Create to see the error message.

   

   This is server-side validation that you get by default; in a later tutorial you'll see how to add attributes that will generate code for client-side validation also. The following highlighted code shows the model validation check in the Create method.

   If ModelState.IsValid Then
       db.Students.Add(student)
       db.SaveChanges()
       Return RedirectToAction("Index")
   End If

4. Change the date to a valid value and click Create to see the new student appear in the Index page.

   

Update the Edit HttpPost Page

In Controllers\StudentController.vb, the HttpGet Edit method (the one without the HttpPost attribute) uses the Find method to retrieve the selected Student entity, as you saw in the Details method. You don't need to change this method.

However, replace the HttpPost Edit action method with the following code:

<HttpPost(), ActionName("Edit")>
<ValidateAntiForgeryToken()>
Function EditPost(ByVal id? As Integer) As ActionResult
    If id Is Nothing Then
        Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
    End If
    Dim studentToUpdate = db.Students.Find(id)
    If TryUpdateModel(studentToUpdate, "", New String() {"LastName", "FirstMidName", "EnrollmentDate"}) Then
        Try

            db.Entry(studentToUpdate).State = EntityState.Modified
            db.SaveChanges()
            Return RedirectToAction("Index")
        Catch Dex As DataException
            'Log the error (uncomment dex variable name and add a line here to write a log.
            ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.")
        End Try
    End If
    Return View(studentToUpdate)
End Function

These changes implement a security best practice to prevent overposting,  The scaffolder generated a Bind attribute and added the entity created by the model binder to the entity set with a Modified flag. That code is no longer recommended because the Bind attribute clears out any pre-existing data in fields not listed in the Include parameter. In the future, the MVC controller scaffolder will be updated so that it doesn't generate Bind attributes for Edit methods.

The new code reads the existing entity and calls TryUpdateModel to update fields from user input in the posted form data. It then sets a flag on the entity indicating it has been changed. When the SaveChanges method is called, the Modified flag causes the Entity Framework to create SQL statements to update the database row. Concurrency conflicts are ignored, and all columns of the database row are updated, including those that the user didn't change. (A later tutorial shows how to handle concurrency conflicts, and if you only want individual fields to be updated in the database, you can set the entity to Unchanged and set individual fields to Modified.)

As a best practice to prevent overposting, the fields that you want to be updateable by the Edit page are whitelisted in the TryUpdateModel parameters. Currently there are no extra fields that you're protecting, but listing the fields that you want the model binder to bind ensures that if you add fields to the data model in the future, they're automatically protected until you explicitly add them here.

As a result of these changes, the method signature of the HttpPost Edit method is the same as the HttpGet edit method; therefore you've renamed the method EditPost. 

The HTML and Razor code in Views\Student\Edit.vbhtml is similar to what you saw in Create.vbhtml, and no changes are required.

Run the page by selecting the Students tab and then clicking an Edit hyperlink.

Change some of the data and click Save.

You see the changed data in the Index page.

Updating the Delete Method

In Controllers\StudentController.vb, the template code for the HttpGet Delete method uses the Find method to retrieve the selected Student entity, as you saw in the Details and Edit methods. However, to implement a custom error message when the call to SaveChanges fails, you'll add some functionality to this method and its corresponding view.

As you saw for update and create operations, delete operations require two action methods. The method that is called in response to a GET request displays a view that gives the user a chance to approve or cancel the delete operation. If the user approves it, a POST request is created. When that happens, the HttpPost Delete method is called and then that method actually performs the delete operation.

You'll add a Try-Catch block to the HttpPost Delete method to handle any errors that might occur when the database is updated. If an error occurs, the HttpPost Delete method calls the HttpGet Delete method, passing it a parameter that indicates that an error has occurred. The HttpGet Delete method then redisplays the confirmation page along with the error message, giving the user an opportunity to cancel or try again.

1. Replace the HttpGet Delete action method with the following code, which manages error reporting:

   Function Delete(ByVal id As Integer?, Optional ByVal saveChangesError As Boolean? = False) As ActionResult
       If IsNothing(id) Then
           Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
       End If
       If saveChangesError.GetValueOrDefault() Then
           ViewBag.Message = "Delete failed. Try again, and if the problem persists see your system administrator."
       End If
       Dim student As Student = db.Students.Find(id)
       If IsNothing(student) Then
           Return HttpNotFound()
       End If
       Return View(student)
   End Function

   This code accepts an optional parameter that indicates whether the method was called after a failure to save changes. This parameter is False when the HttpGet Delete method is called without a previous failure. When it is called by the HttpPost Delete method in response to a database update error, the parameter value is True and an error message is passed to the view.

2. Replace the HttpPost Delete action method (named DeleteConfirmed) with the following code, which performs the actual delete operation and catches any database update errors.

   <HttpPost()>
   <ActionName("Delete")>
   <ValidateAntiForgeryToken()>
   Function Delete(ByVal id As Integer) As ActionResult
       Try
           Dim student As Student = db.Students.Find(id)
           db.Students.Remove(student)
           db.SaveChanges()
       Catch dex As DataException
           'Log the error (uncomment dex variable name and add a line here to write a log.
           Return RedirectToAction("Delete", New With {.id = id, .saveChangesError = True})
       End Try
       Return RedirectToAction("Index")
   End Function

   This code retrieves the selected entity, then calls the Remove method to set the entity's status to Deleted. When SaveChanges is called, a SQL DELETE command is generated. You have also changed the action method name from DeleteConfirmed to Delete. The scaffolded code named the HttpPost Delete method DeleteConfirmed to give the HttpPost  method a unique signature. ( The CLR requires overloaded methods to have different method parameters.) Now that the signatures are unique, you can stick with the MVC convention and use the same name for the HttpPost and HttpGet delete methods.

   If improving performance in a high-volume application is a priority, you could avoid an unnecessary SQL query to retrieve the row by replacing the lines of code that call the Find and Remove methods with the following code:

   Dim student As Student = New Student With {.ID = id}
   db.Entry(student).State = EntityState.Deleted

   This code instantiates a Student entity using only the primary key value and then sets the entity state to Deleted. That's all that the Entity Framework needs in order to delete the entity.

   As noted, the HttpGet Delete method doesn't delete the data. Performing a delete operation in response to a GET request (or for that matter, performing any edit operation, create operation, or any other operation that changes data) creates a security risk. For more information, see ASP.NET MVC Tip #46 — Don't use Delete Links because they create Security Holes on Stephen Walther's blog.

3. In Views\Student\Delete.vbhtml, add an error message between the h2 heading and the h3 heading, as shown in the following example:

   <h2>Delete</h2>
   <p class="error">@ViewBag.ErrorMessage</p>
   <h3>Are you sure you want to delete this?</h3>

   Run the page by selecting the Students tab and clicking a Delete hyperlink:

   

4. Click Delete. The Index page is displayed without the deleted student. (You'll see an example of the error handling code in action in the concurrency tutorial.)

Ensuring that Database Connections Are Not Left Open

To make sure that database connections are properly closed and the resources they hold freed up, you have to dispose the context instance when you are done with it. That is why the scaffolded code provides a Dispose method at the end of the StudentController class in StudentController.vb, as shown in the following example:

Protected Overrides Sub Dispose(ByVal disposing As Boolean)
    If (disposing) Then
        db.Dispose()
    End If
    MyBase.Dispose(disposing)
End Sub

The base Controller class already implements the IDisposable interface, so this code simply adds an override to the Dispose(bool) method to explicitly dispose the context instance.

Handling Transactions

By default the Entity Framework implicitly implements transactions. In scenarios where you make changes to multiple rows or tables and then call SaveChanges, the Entity Framework automatically makes sure that either all of your changes succeed or all fail. If some changes are done first and then an error happens, those changes are automatically rolled back. For scenarios where you need more control - for example, if you want to include operations done outside of Entity Framework in a transaction - see Working with Transactions on MSDN.

Summary

You now have a complete set of pages that perform simple CRUD operations for Student entities. You used MVC helpers to generate UI elements for data fields. For more information about MVC helpers, see Rendering a Form Using HTML Helpers (the page is for MVC 3 and uses the Web Forms templating syntax, but is still relevant for MVC 5 and should be easily translated across to Razor).

In the next tutorial you'll expand the functionality of the Index page by adding sorting and paging.