Async and Stored Procedures with the Entity Framework in an ASP.NET MVC Application

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:

public async Task<ActionResult> Index()
{
    var departments = db.Departments.Include(d => d.Administrator);
    return View(await departments.ToListAsync());
}

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<ActionResult> object that is returned.
• The return type was changed from ActionResult to Task<ActionResult>. The Task<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:

public async Task<ActionResult> Details(int? id)
{
    if (id == null)
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    Department department = await db.Departments.FindAsync(id);
    if (department == null)
    {
        return HttpNotFound();
    }
    return View(department);
}

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.

public async Task<ActionResult> Create(Department department)
{
    if (ModelState.IsValid)
    {
        db.Departments.Add(department);
        await db.SaveChangesAsync();
        return RedirectToAction("Index");
    }

    ViewBag.InstructorID = new SelectList(db.Instructors, "ID", "LastName", department.InstructorID);
    return View(department);
}

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

@model IEnumerable<ContosoUniversity.Models.Department>
@{
    ViewBag.Title = "Departments";
}
<h2>Departments</h2>
<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Budget)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.StartDate)
        </th>
        <th>
            Administrator
        </th>
        <th></th>
    </tr>
@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Budget)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.StartDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Administrator.FullName)
            </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.DepartmentID }) |
            @Html.ActionLink("Details", "Details", new { id=item.DepartmentID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.DepartmentID })
        </td>
    </tr>
}
</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. 

In the Create and Edit views use the following code:

<label class="control-label col-md-2" for="InstructorID">Administrator</label>

In the Delete and Details views use the following code:

<dt>
    Administrator
</dt>

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, 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.cs, add the highlighted code to the OnModelCreating method.

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
   
       modelBuilder.Entity<Course>()
           .HasMany(c => c.Instructors).WithMany(i => i.Courses)
           .Map(t => t.MapLeftKey("CourseID")
               .MapRightKey("InstructorID")
               .ToTable("CourseInstructor"));
   
       modelBuilder.Entity<Department>().MapToStoredProcedures();
   }

   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.cs to see the code in the Up method that creates Insert, Update, and Delete stored procedures:

   public override void Up()
   {
       CreateStoredProcedure(
           "dbo.Department_Insert",
           p => new
               {
                   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])
                 VALUES (@Name, @Budget, @StartDate, @InstructorID)
                 
                 DECLARE @DepartmentID int
                 SELECT @DepartmentID = [DepartmentID]
                 FROM [dbo].[Department]
                 WHERE @@ROWCOUNT > 0 AND [DepartmentID] = scope_identity()
                 
                 SELECT t0.[DepartmentID]
                 FROM [dbo].[Department] AS t0
                 WHERE @@ROWCOUNT > 0 AND t0.[DepartmentID] = @DepartmentID"
       );
       
       CreateStoredProcedure(
           "dbo.Department_Update",
           p => new
               {
                   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]
                 SET [Name] = @Name, [Budget] = @Budget, [StartDate] = @StartDate, [InstructorID] = @InstructorID
                 WHERE ([DepartmentID] = @DepartmentID)"
       );
       
       CreateStoredProcedure(
           "dbo.Department_Delete",
           p => new
               {
                   DepartmentID = p.Int(),
               },
           body:
               @"DELETE [dbo].[Department]
                 WHERE ([DepartmentID] = @DepartmentID)"
       );
       
   }

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.

Deploy to Windows Azure

This section requires you to have completed the optional Deploying the app to Windows Azure section in the Migrations 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 Up methods 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

This completes this series of tutorials on using Entity Framework 6 in an ASP.NET MVC 5 application. We plan to add more tutorials to the series to cover additional topics in the future, including concurrency, inheritance, performance, and other advanced scenarios. Until those updates are available, you can find many of them covered in the Entity Framework 5 / MVC 4 version of this tutorial series.

Please leave feedback on how you liked this tutorial and what we could improve. You can also request new topics at Show Me How With Code.

Links to other Entity Framework resources can be found in the ASP.NET Data Access Content Map.

For more information about how to deploy your web application after you've built it, see ASP.NET Deployment Content Map in the MSDN Library. 

For information about other topics related to MVC, such as authentication and authorization, see the MVC Content Map.

Acknowledgments

• Tom Dykstra wrote the original version of this tutorial and co-authored the updates for EF 5 and EF 6 with Rick Anderson. Tom is a senior programming writer on the Microsoft Web Platform and Tools Content Team.
• Rick Anderson (twitter @RickAndMSFT) co-authored this tutorial starting with the EF 5 update. Rick is a senior programming writer for Microsoft focusing on Azure and MVC.
• Rowan Miller, Diego Vega, Arthur Vickers, Maurycy Markowski, and other members of the Entity Framework team provided code snippets, assisted with code reviews, and helped debug many issues with migrations that arose while we were updating the tutorial for EF 5.

VB

When the tutorial was originally produced, we provided both C# and VB versions of the completed download project. With this update we are providing a C# downloadable project, but due to time limitations and other priorities we did not do that for VB. If you build a VB project using these tutorials and would be willing to share that with others, please let us know.

By Tom Dykstra, Tom Dykstra is a Senior Programming Writer on Microsoft's Web Platform & Tools Content Team...

1. ← PreviousUpdating Related Data
2.  You're ViewingAsync and Stored Procedures
3.