MVC 5 with EF 6 in Visual Basic - Async and Stored Procedures with the Entity Framework

In this tutorial you'll also see how to use stored procedures for insert, update, and delete operations on an entity. Finally, you'll redeploy the application to Windows Azure, along with all of the database changes that you've implemented since the first time you deployed.

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 series 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

Why bother with asynchronous code?

A web server has a limited number of threads available, and in high load situations all of the available threads might be in use. When that happens, the server can’t process new requests until the threads are freed up. With synchronous code, many threads may be tied up while they aren’t actually doing any work because they’re waiting for I/O to complete. With asynchronous code, when a process is waiting for I/O to complete, its thread is freed up for the server to use for processing other requests. As a result, asynchronous code enables server resources to be use more efficiently, and the server is enabled to handle more traffic without delays.

In earlier versions of .NET, writing and testing asynchronous code was complex, error prone, and hard to debug. In .NET 4.5, writing, testing, and debugging asynchronous code is so much easier that you should generally write asynchronous code unless you have a reason not to. Asynchronous code does introduce a small amount of overhead, but for low traffic situations the performance hit is negligible, while for high traffic situations, the potential performance improvement is substantial.

For more information about asynchronous programming, see Use .NET 4.5’s async support to avoid blocking calls.

Create the Department controller

Create a Department controller the same way you did the earlier controllers, except this time select the Use async controller actions check box.

The following highlights show how what was added to the synchronous code for the Index method to make it asynchronous:

Async Function Index() As Task(Of ActionResult)
    Dim departments = db.Departments.Include(Function(d) d.Administrator)
    Return View(Await departments.ToListAsync())
End Function

Four changes were applied to enable the Entity Framework database query to execute asynchronously:

• The method is marked with the Async keyword, which tells the compiler to generate callbacks for parts of the method body and to automatically create the Task(Of ActionResult) object that is returned.
• The return type was changed from ActionResult to Task(Of ActionResult) . The Task(Of T) type represents ongoing work with a result of type T.
• The Await keyword was applied to the web service call. When the compiler sees this keyword, behind the scenes it splits the method into two parts. The first part ends with the operation that is started asynchronously. The second part is put into a callback method that is called when the operation completes.
• The asynchronous version of the ToList extension method was called.

Why is the departments.ToList statement modified but not the departments = db.Departments statement? The reason is that only statements that cause queries or commands to be sent to the database are executed asynchronously. The departments = db.Departments statement sets up a query but the query is not executed until the ToList method is called. Therefore, only the ToList method is executed asynchronously.

In the Details method and the HttpGet Edit and Delete methods, the Find method is the one that causes a query to be sent to the database, so that's the method that gets executed asynchronously:

Async Function Details(ByVal id As Integer?) As Task(Of ActionResult)
    If IsNothing(id) Then
        Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
    End If
    Dim department As Department = Await db.Departments.FindAsync(id)
    If IsNothing(department) Then
        Return HttpNotFound()
    End If
    Return View(department)
End Function

In the Create , HttpPost Edit, and DeleteConfirmed methods, it is the SaveChanges method call that causes a command to be executed, not statements such as db.Departments.Add(department) which only cause entities in memory to be modified.

Async Function Create(<Bind(Include := "DepartmentID,Name,Budget,StartDate,InstructorID")> ByVal department As Department) As Task(Of ActionResult)
    If ModelState.IsValid Then
        db.Departments.Add(department)
        Await db.SaveChangesAsync()
        Return RedirectToAction("Index")
    End If 

Open Views\Department\Index.vbhtml, and replace the template code with the following code:

@ModelType IEnumerable(Of ContosoUniversity.Models.Department)
@Code
    ViewBag.Title = "Departments"
End Code

<h2>Departments</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(Function(model) model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(Function(model) model.Budget)
        </th>
        <th>
            @Html.DisplayNameFor(Function(model) model.StartDate)
        </th>
        <th>
            Administrator
        </th>
        <th></th>
    </tr>

@For Each item In Model
    @<tr>
        <td>
            @Html.DisplayFor(Function(modelItem) item.Name)
        </td>
        <td>
            @Html.DisplayFor(Function(modelItem) item.Budget)
        </td>
        <td>
            @Html.DisplayFor(Function(modelItem) item.StartDate)
        </td>
        <td>
             @Html.DisplayFor(Function(modelItem) item.Administrator.FullName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", New With {.id = item.DepartmentID }) |
            @Html.ActionLink("Details", "Details", New With {.id = item.DepartmentID }) |
            @Html.ActionLink("Delete", "Delete", New With {.id = item.DepartmentID })
        </td>
    </tr>
Next

</table>

This code changes the title from Index to Departments, moves the Administrator name to the right, and provides the full name of the administrator.

In the Create, Delete, Details, and Edit views, change the caption for the InstructorID field to "Administrator" the same way you changed the department name field to "Department" in the Course views. 

Run the application, and click the Departments tab.

Everything works the same as in the other controllers, but in this controller all of the SQL queries are executing asynchronously.

Some things to be aware of when you are using asynchronous programming with the Entity Framework:

• The async code is not thread safe. In other words, don't try to do multiple operations in parallel using the same context instance.
• If you want to take advantage of the performance benefits of async code, make sure that any library packages that you're using (such as for paging), also use async if they call any Entity Framework methods that cause queries to be sent to the database.

Use stored procedures for inserting, updating, and deleting

Some developers and DBAs prefer to use stored procedures for database access. In earlier versions of Entity Framework you can retrieve data using a stored procedure by executing a raw SQL query, but you can't instruct EF to use stored procedures for update operations. In EF 6 it's easy to configure Code First to use stored procedures.

1. In  DAL\SchoolContext.vb, add the highlighted code to the OnModelCreating method.

   Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
       modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()
       modelBuilder.Entity(Of Course)() _
           .HasMany(Function(c) c.Instructors).WithMany(Function(i) i.Courses) _
           .Map(Function(t) t.MapLeftKey("CourseID") _
           .MapRightKey("InstructorID") _
           .ToTable("CourseInstructor"))
       modelBuilder.Entity(Of Department)().MapToStoredProcedures()
   End Sub

   This code instructs Entity Framework to use stored procedures for insert, update, and delete operations on the Department entity.

2. In Package Manage Console, enter the following command:

   add-migration DepartmentSP

   Open Migrations\<timestamp>_DepartmentSP.vb to see the code in the Up method that creates Insert, Update, and Delete stored procedures:

   Public Overrides Sub Up()
       CreateStoredProcedure(
           "dbo.Department_Insert",
           Function(p) New With
               {
                   .Name = p.String(maxLength:=50),
                   .Budget = p.Decimal(precision:=19, scale:=4, storeType:="money"),
                   .StartDate = p.DateTime(),
                   .InstructorID = p.Int()
               },
           body:=
               "INSERT [dbo].[Department]([Name], [Budget], [StartDate], [InstructorID])" & vbCrLf & _
               "VALUES (@Name, @Budget, @StartDate, @InstructorID)" & vbCrLf & _
               "" & vbCrLf & _
               "DECLARE @DepartmentID int" & vbCrLf & _
               "SELECT @DepartmentID = [DepartmentID]" & vbCrLf & _
               "FROM [dbo].[Department]" & vbCrLf & _
               "WHERE @@ROWCOUNT > 0 AND [DepartmentID] = scope_identity()" & vbCrLf & _
               "" & vbCrLf & _
               "SELECT t0.[DepartmentID]" & vbCrLf & _
               "FROM [dbo].[Department] AS t0" & vbCrLf & _
               "WHERE @@ROWCOUNT > 0 AND t0.[DepartmentID] = @DepartmentID"
       )
   
       CreateStoredProcedure(
           "dbo.Department_Update",
           Function(p) New With
               {
                   .DepartmentID = p.Int(),
                   .Name = p.String(maxLength:=50),
                   .Budget = p.Decimal(precision:=19, scale:=4, storeType:="money"),
                   .StartDate = p.DateTime(),
                   .InstructorID = p.Int()
               },
           body:=
               "UPDATE [dbo].[Department]" & vbCrLf & _
               "SET [Name] = @Name, [Budget] = @Budget, [StartDate] = @StartDate, [InstructorID] = @InstructorID" & vbCrLf & _
               "WHERE ([DepartmentID] = @DepartmentID)"
       )
   
       CreateStoredProcedure(
           "dbo.Department_Delete",
           Function(p) New With
               {
                   .DepartmentID = p.Int()
               },
           body:=
               "DELETE [dbo].[Department]" & vbCrLf & _
               "WHERE ([DepartmentID] = @DepartmentID)"
       )
   
   End Sub

3. In Package Manage Console, enter the following command:

   update-database

4. Run the application in debug mode, click the Departments tab, and then click Create New.

5. Enter data for a new department, and then click Create.

   

6. In Visual Studio, look at the logs in the Output window to see that a stored procedure was used to insert the new Department row.

   

Code First creates default stored procedure names. If you are using an existing database, you might need to customize the stored procedure names in order to use stored procedures already defined in the database. For information about how to do that, see Entity Framework Code First Insert/Update/Delete Stored Procedures .

If you want to customize what generated stored procedures do, you can edit the scaffolded code for the migrations Up method that creates the stored procedure. That way your changes are reflected whenever that migration is run and will be applied to your production database when migrations runs automatically in production after deployment.

If you want to change an existing stored procedure that was created in a previous migration, you can use the Add-Migration command to generate a blank migration, and then manually write code that calls the AlterStoredProcedure method.

Deploy to Windows Azure

This section requires you to have completed the optional Deploying the app to Windows Azure section in theMigrations and Deployment tutorial of this series. If you had migrations errors that you resolved by deleting the database in your local project, skip this section.

1. In Visual Studio, right-click the project in Solution Explorer and select Publish from the context menu.

2. Click Publish.

   Visual Studio deploys the application to Windows Azure, and the application opens in your default browser, running in Windows Azure.

3. Test the application to verify it's working.

   The first time you run a page that accesses the database, the Entity Framework runs all of the migrations Upmethods required to bring the database up to date with the current data model. You can now use all of the web pages that you added since the last time you deployed, including the Department pages that you added in this tutorial.

Summary

In this tutorial you saw how to improve server efficiency by writing code that executes asynchronously, and how to use stored procedures for insert, update, and delete operations. In the next tutorial, you'll see how to prevent data loss when multiple users try to edit the same record at the same time.