MVC 5 with EF 6 in Visual Basic - Advanced Entity Framework Scenarios

• Performing raw SQL queries
• Performing no-tracking queries
• Examining SQL sent to the database

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

The tutorial introduces several topics with brief introductions followed by links to resources for more information:

• Repository and unit of work patterns
• Proxy classes
• Automatic change detection
• Automatic validation
• EF tools for Visual Studio
• Entity Framework source code

For most of these topics, you'll work with pages that you already created. To use raw SQL to do bulk updates you'll create a new page that updates the number of credits of all courses in the database:

And to use a no-tracking query you'll add new validation logic to the Department Edit page:

Performing Raw SQL Queries

The Entity Framework Code First API includes methods that enable you to pass SQL commands directly to the database. You have the following options:

• Use the DbSet.SqlQuery method for queries that return entity types. The returned objects must be of the type expected by the DbSet object, and they are automatically tracked by the database context unless you turn tracking off. (See the following section about the AsNoTracking method.)
• Use the Database.SqlQuery method for queries that return types that aren't entities. The returned data isn't tracked by the database context, even if you use this method to retrieve entity types.
• Use the Database.ExecuteSqlCommand for non-query commands.

One of the advantages of using the Entity Framework is that it avoids tying your code too closely to a particular method of storing data. It does this by generating SQL queries and commands for you, which also frees you from having to write them yourself. But there are exceptional scenarios when you need to run specific SQL queries that you have manually created, and these methods make it possible for you to handle such exceptions.

As is always true when you execute SQL commands in a web application, you must take precautions to protect your site against SQL injection attacks. One way to do that is to use parameterized queries to make sure that strings submitted by a web page can't be interpreted as SQL commands. In this tutorial you'll use parameterized queries when integrating user input into a query.

Calling a Query that Returns Entities

The DbSet<TEntity> class provides a method that you can use to execute a query that returns an entity of type TEntity. To see how this works you'll change the code in the Details method of the Department controller.

In DepartmentController.vb, replace the db.Departments.Find method call with a db.Departments.SqlQuery method call, as shown in the following code:

Async Function Details(ByVal id As Integer?) As Task(Of ActionResult)
    If IsNothing(id) Then
        Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
    End If
    ' Commenting out original code to show how to use a raw SQL query.
    'Dim department As Department = Await db.Departments.FindAsync(id)

    ' Create and execute raw SQL query.
    Dim query = "SELECT * FROM Department WHERE DepartmentID = @p0"
    Dim department As Department = Await db.Departments.SqlQuery(query, id).SingleOrDefaultAsync()
    If IsNothing(department) Then
        Return HttpNotFound()
    End If
    Return View(department)
End Function

To verify that the new code works correctly, select the Departments tab and then Details for one of the departments.

Calling a Query that Returns Other Types of Objects

Earlier you created a student statistics grid for the About page that showed the number of students for each enrollment date. The code that does this in HomeController.vb uses LINQ:

Dim data As IQueryable(Of EnrollmentDateGroup) = db.Students.GroupBy _
                                                         (Function(s) s.EnrollmentDate).Select _
                                                         (Function(g) New EnrollmentDateGroup With _
                                                                      {.EnrollmentDate = g.Key, .StudentCount = g.Count()})

Suppose you want to write the code that retrieves this data directly in SQL rather than using LINQ. To do that you need to run a query that returns something other than entity objects, which means you need to use the Database.SqlQuery method.

In HomeController.vb, replace the LINQ statement in the About method with a SQL statement, as shown in the following highlighted code:

'Dim data As IQueryable(Of EnrollmentDateGroup) = db.Students.GroupBy _
'                                                 (Function(s) s.EnrollmentDate).Select _
'                                                 (Function(g) New EnrollmentDateGroup With _
'                                                              {.EnrollmentDate = g.Key, .StudentCount = g.Count()})

Dim query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount " & _
             "FROM Person " & _
             "WHERE Discriminator = 'Student' " & _
             "GROUP BY EnrollmentDate"
Dim data As IEnumerable(Of EnrollmentDateGroup) = db.Database.SqlQuery(Of EnrollmentDateGroup)(query)
Return View(data.ToList())

Run the About page. It displays the same data it did before.

Calling an Update Query

Suppose Contoso University administrators want to be able to perform bulk changes in the database, such as changing the number of credits for every course. If the university has a large number of courses, it would be inefficient to retrieve them all as entities and change them individually. In this section you'll implement a web page that enables the user to specify a factor by which to change the number of credits for all courses, and you'll make the change by executing a SQL UPDATE statement. The web page will look like the following illustration:

In CourseContoller.vb, add UpdateCourseCredits methods for HttpGet and HttpPost:

Public Function UpdateCourseCreditsActionResult() As ActionResult
    Return View()
End Function


<HttpPost>
Public Function UpdateCourseCreditsActionResult(ByVal multiplier As Integer?) As ActionResult
    If Not IsNothing(multiplier) Then
        ViewBag.RowsAffected = db.Database.ExecuteSqlCommand("UPDATE Course SET Credits = Credits * {0}", multiplier)
    End If
    Return View()
End Function

When the controller processes an HttpGet request, nothing is returned in the ViewBag.RowsAffected variable, and the view displays an empty text box and a submit button, as shown in the preceding illustration.

When the Update button is clicked, the HttpPost method is called, and multiplier has the value entered in the text box. The code then executes the SQL that updates courses and returns the number of affected rows to the view in the ViewBag.RowsAffected variable. When the view gets a value in that variable, it displays the number of rows updated  instead of the text box and submit button, as shown in the following illustration:

In CourseController.vb, right-click one of the UpdateCourseCredits methods, and then click Add.

In Views\Course\UpdateCourseCredits.vbhtml, replace the template code with the following code:

@Code
    ViewBag.Title = "UpdateCourseCredits"
End Code

<h2>Update Course Credits</h2>

@If IsNothing(ViewBag.RowsAffected) Then

    Using Html.BeginForm()
    
        @<p>
            Enter a number to multiply every course's credits by: @Html.TextBox("multiplier")
        </p>
        @<p>
            <input type="submit" value="Update" />
        </p>
    End Using
End If
    @If Not IsNothing(ViewBag.RowsAffected) Then

    @<p>
        Number of rows updated: @ViewBag.RowsAffected
    </p>
End If
<div>
    @Html.ActionLink("Back to List", "Index")
</div>

Run the UpdateCourseCredits method by selecting the Courses tab, then adding "/UpdateCourseCredits" to the end of the URL in the browser's address bar (for example: http://localhost:50205/Course/UpdateCourseCredits). Enter a number in the text box:

Click Back to List to see the list of courses with the revised number of credits.

For more information about raw SQL queries, see Raw SQL Queries on MSDN.

No-Tracking Queries

When a database context retrieves table rows and creates entity objects that represent them, by default it keeps track of whether the entities in memory are in sync with what's in the database. The data in memory acts as a cache and is used when you update an entity. This caching is often unnecessary in a web application because context instances are typically short-lived (a new one is created and disposed for each request) and the context that reads an entity is typically disposed before that entity is used again.

You can disable tracking of entity objects in memory by using the AsNoTracking method. Typical scenarios in which you might want to do that include the following:

• A query retrieves such a large volume of data that turning off tracking might noticeably enhance performance.
• You want to attach an entity in order to update it, but you earlier retrieved the same entity for a different purpose. Because the entity is already being tracked by the database context, you can't attach the entity that you want to change. One way to handle this situation is to use the AsNoTracking option with the earlier query.

In this section you'll implement business logic that illustrates the second of these scenarios. Specifically, you'll enforce a business rule that says that an instructor can't be the administrator of more than one department. (Depending on what you've done with the Departments page so far, you might already have some departments that have the same administrator. In a production application you would apply a new rule to existing data also, but for this tutorial that isn't necessary.)

In DepartmentController.vb, add a new method that you can call from the Edit and Create methods to make sure that no two departments have the same administrator:

Private Sub ValidateOneAdministratorAssignmentPerInstructor(department As Department)
    If department.InstructorID IsNot Nothing Then
        Dim duplicateDepartment As Department = db.Departments.Include("Administrator").Where(Function(d) d.InstructorID = department.InstructorID).FirstOrDefault()
        If duplicateDepartment IsNot Nothing AndAlso duplicateDepartment.DepartmentID <> department.DepartmentID Then
            Dim errorMessage As String = String.Format("Instructor {0} {1} is already administrator of the {2} department.", duplicateDepartment.Administrator.FirstMidName, duplicateDepartment.Administrator.LastName, duplicateDepartment.Name)
            ModelState.AddModelError(String.Empty, errorMessage)
        End If
    End If
End Sub

Add code in the try block of the HttpPost Edit method to call this new method if there are no validation errors. The try block now looks like the following example:

<HttpPost()>
<ValidateAntiForgeryToken()>
Async Function Edit(<Bind(Include:="DepartmentID,Name,Budget,StartDate,RowVersion,InstructorID")> ByVal department As Department) As Task(Of ActionResult)
    Try
        If ModelState.IsValid Then
            ValidateOneAdministratorAssignmentPerInstructor(department)
        End If
        If ModelState.IsValid Then
            db.Entry(department).State = EntityState.Modified
            db.SaveChanges()
            Return RedirectToAction("Index")
        End If
    Catch ex As DbUpdateConcurrencyException
        Dim entry = ex.Entries.Single()
        Dim clientValues = DirectCast(entry.Entity, Department)

Run the Department Edit page and try to change a department's administrator to an instructor who is already the administrator of a different department. You get the expected error message:

Now run the Department Edit page again and this time change the Budget amount. When you click Save, you see an error page that results from the code you added in ValidateOneAdministratorAssignmentPerInstructor:

The exception error message is:

Attaching an entity of type 'ContosoUniversity.Models.Department' failed because another entity of the same type already has the same primary key value. This can happen when using the 'Attach' method or setting the state of an entity to 'Unchanged' or 'Modified' if any entities in the graph have conflicting key values. This may be because some entities are new and have not yet received database-generated key values. In this case use the 'Add' method or the 'Added' entity state to track the graph and then set the state of non-new entities to 'Unchanged' or 'Modified' as appropriate.

This happened because of the following sequence of events:

• The Edit method calls the ValidateOneAdministratorAssignmentPerInstructor method, which retrieves all departments that have Kim Abercrombie as their administrator. That causes the English department to be read. As a result of this read operation, the English department entity that was read from the database is now being tracked by the database context.
• The Edit method tries to set the Modified flag on the English department entity created by the MVC model binder, which implicitly causes the context to try to attach that entity. But the context can't attach the entry created by the model binder because the context is already tracking an entity for the English department.

One solution to this problem is to keep the context from tracking in-memory department entities retrieved by the validation query. There's no disadvantage to doing this, because you won't be updating this entity or reading it again in a way that would benefit from it being cached in memory.

In DepartmentController.vb, in the ValidateOneAdministratorAssignmentPerInstructor method, specify no tracking, as shown in the following:

Dim duplicateDepartment As Department = db.Departments _
                                        .Include("Administrator") _
                                        .AsNoTracking() _
                                        .Where(Function(d) d.InstructorID = department.InstructorID) _
                                        .FirstOrDefault()

Repeat your attempt to edit the Budget amount of a department. This time the operation is successful, and the site returns as expected to the Departments Index page, showing the revised budget value.

Examining SQL sent to the database

Sometimes it's helpful to be able to see the actual SQL queries that are sent to the database. In an earlier tutorial you saw how to do that in interceptor code; now you'll see some ways to do it without writing interceptor code. To try this out, you'll look at a simple query and then look at what happens to it as you add options such eager loading, filtering, and sorting.

In Controllers/CourseController, replace the Index method with the following code, in order to temporarily stop eager loading:

Function Index() As ActionResult
    Dim courses = db.Courses
    Dim sql = courses.ToString()
    Return View(courses.ToList())
End Function

Now set a breakpoint on the Return statement (F9 with the cursor on that line). Press F5 to run the project in debug mode, and select the Course Index page. When the code reaches the breakpoint, examine the sql variable. You see the query that's sent to SQL Server. It's a simple Select statement.

SELECT 
    [Extent1].[CourseID] AS [CourseID], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[Credits] AS [Credits], 
    [Extent1].[DepartmentID] AS [DepartmentID] 
    FROM [dbo].[Course] AS [Extent1]

Click the magnifying class to see the query in the Text Visualizer.

Now you'll add a drop-down list to the Courses Index page so that users can filter for a particular department. You'll sort the courses by title, and you'll specify eager loading for the Department navigation property.

In CourseController.vb, replace the Index method with the following code:

Function Index(ByVal SelectedDepartment As Integer?) As ActionResult
    Dim departments = db.Departments.OrderBy(Function(q) q.Name).ToList()
    ViewBag.SelectedDepartment = New SelectList(departments, "DepartmentID", "Name", SelectedDepartment)
    Dim departmentID = SelectedDepartment.GetValueOrDefault()

    Dim courses As IQueryable(Of Course) = db.Courses _
        .Where(Function(c) Not SelectedDepartment.HasValue Or c.DepartmentID = departmentID) _
        .OrderBy(Function(d) d.CourseID) _
        .Include(Function(d) d.Department)
    Return View(courses.ToList())
End Function

Restore the breakpoint on the Return statement.

The method receives the selected value of the drop-down list in the SelectedDepartment parameter. If nothing is selected, this parameter will be null.

A SelectList collection containing all departments is passed to the view for the drop-down list. The parameters passed to the SelectList constructor specify the value field name, the text field name, and the selected item.

For the Get method of the Course repository, the code specifies a filter expression, a sort order, and eager loading for the Department navigation property. The filter expression always returns true if nothing is selected in the drop-down list (that is, SelectedDepartment is null).

In Views\Course\Index.vbhtml, immediately before the opening table tag, add the following code to create the drop-down list and a submit button:

@Using Html.BeginForm()
    @<p>Select Department: @Html.DropDownList("SelectedDepartment","All")
    <input type="submit" value="Filter" /></p>
End Using

This time the first breakpoint will be for the departments query for the dropdown list. Skip that and view the sql variable the next time the code reaches the breakpoint in order to see what the Course query now looks like. You'll see something like the following:

You can see that the query is now a JOIN query that loads Department data along with the Course data, and that it includes a WHERE clause.

Remove the Dim sql = courses.ToString() line.

Repository and unit of work patterns

Many developers write code to implement the repository and unit of work patterns as a wrapper around code that works with the Entity Framework. These patterns are intended to create an abstraction layer between the data access layer and the business logic layer of an application. Implementing these patterns can help insulate your application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD). However, writing additional code to implement these patterns is not always the best choice for applications that use EF, for several reasons:

• The EF context class itself insulates your code from data-store-specific code.
• The EF context class can act as a unit-of-work class for database updates that you do using EF.
• The EF DbSet class is already an implementation of the repository pattern.
• Features introduced in Entity Framework 6 make it easier to implement TDD without writing your own repository code.

For more information about how to implement the repository and unit of work patterns, see the Entity Framework 5 version of this tutorial series (C#). For information about ways to implement TDD in Entity Framework 6, see the following resources:

• How EF6 Enables Mocking DbSets more easily
• Testing with a mocking framework
• Testing with your own test doubles

Proxy classes

When the Entity Framework creates entity instances (for example, when you execute a query), it often creates them as instances of a dynamically generated derived type that acts as a proxy for the entity. For example, see the following two debugger images. In the first image, you see that the student variable is the expected Student type immediately after you instantiate the entity. In the second image, after EF has been used to read a student entity from the database, you see the proxy class.

This proxy class overrides some virtual properties of the entity to insert hooks for performing actions automatically when the property is accessed. One function this mechanism is used for is lazy loading.

Most of the time you don't need to be aware of this use of proxies, but there are exceptions:

• In some scenarios you might want to prevent the Entity Framework from creating proxy instances. For example, when you're serializing entities you generally want the POCO classes, not the proxy classes. One way to avoid serialization problems is to serialize data transfer objects (DTOs) instead of entity objects, as shown in the Using Web API with Entity Framework tutorial. Another way is to  disable proxy creation.
• When you instantiate an entity class using the new operator, you don't get a proxy instance. This means you don't get functionality such as lazy loading and automatic change tracking. This is typically okay; you generally don't need lazy loading, because you're creating a new entity that isn't in the database, and you generally don't need change tracking if you're explicitly marking the entity as Added. However, if you do need lazy loading and you need change tracking, you can create new entity instances with proxies using the Create method of the DbSet class.
• You might want to get an actual entity type from a proxy type. You can use the GetObjectType method of the ObjectContext class to get the actual entity type of a proxy type instance.

For more information, see Working with Proxies on MSDN.

Automatic change detection

The Entity Framework determines how an entity has changed (and therefore which updates need to be sent to the database) by comparing the current values of an entity with the original values. The original values are stored when the entity is queried or attached. Some of the methods that cause automatic change detection are the following:

• DbSet.Find
• DbSet.Local
• DbSet.Remove
• DbSet.Add
• DbSet.Attach
• DbContext.SaveChanges
• DbContext.GetValidationErrors
• DbContext.Entry
• DbChangeTracker.Entries

If you're tracking a large number of entities and you call one of these methods many times in a loop, you might get significant performance improvements by temporarily turning off automatic change detection using the AutoDetectChangesEnabled property. For more information, see Automatically Detecting Changes on MSDN.

Automatic validation

When you call the SaveChanges method, by default the Entity Framework validates the data in all properties of all changed entities before updating the database. If you've updated a large number of entities and you've already validated the data, this work is unnecessary and you could make the process of saving the changes take less time by temporarily turning off validation. You can do that using the ValidateOnSaveEnabled property. For more information, see Validation on MSDN.

Entity Framework Power Tools

Entity Framework Power Tools is a Visual Studio add-in that was used to create some of the data model diagrams shown in these tutorials. The tools can also do other functions such as generate entity classes based on the tables in an existing database so that you can use the database with Code First.

Note: This tool is NOT compatible with Visual Studio Express for Web, or any other Express edition of Visual Studio.

After you install the tools, some additional options appear in context menus. For example, when you right-click your context class in Solution Explorer, you get an option to generate a diagram. When you're using Code First you can't change the data model in the diagram, but you can move things around to make it easier to understand.

Entity Framework source code

The source code for Entity Framework 6 is available at http://entityframework.codeplex.com/. Besides source code, you can get nightly builds, issue tracking, feature specs, design meeting notes, and more. You can file bugs, and you can contribute your own enhancements to the EF source code.

Although the source code is open, Entity Framework is fully supported as a Microsoft product. The Microsoft Entity Framework team keeps control over which contributions are accepted and tests all code changes to ensure the quality of each release. 

Summary

This completes this series of tutorials on using the Entity Framework in a Visual Basic ASP.NET MVC application. For more information about how to work with data using the Entity Framework, see the EF documentation page on MSDN and ASP.NET Data Access - Recommended Resources.