1
12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Mon Jul 06, 2015 8:16 am
Admin
![Admin](https://2img.net/u/3016/32/87/44/avatars/1-37.jpg)
Admin
![Admin Admin](https://2img.net/h/i100.photobucket.com/albums/m19/VODANHAFC/admin.gif)
![Loading](https://2img.net/h/farm04.go.vn/photo/data/20111012/10798851/946D73AB7E9F949624C2BF756D1ED794.gif)
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
The tutorial also includes the following sections:
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:
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Up](http://i1.asp.net/media/49064/up.png?cdn_id=2015-07-02-001)
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
- Code:
DbSet
- 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
- Code:
TEntity
- Code:
Details
- Code:
Department
In DepartmentController.cs, in the
- Code:
Details
- Code:
db.Departments.FindAsync
- Code:
db.Departments.SqlQuery
public async Task
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
// Commenting out original code to show how to use a raw SQL query.
//Department department = await db.Departments.FindAsync(id);
// Create and execute raw SQL query.
string query = "SELECT * FROM Department WHERE DepartmentID = @p0";
Department department = await db.Departments.SqlQuery(query, id).SingleOrDefaultAsync();
if (department == null)
{
return HttpNotFound();
}
return View(department);
}
To verify that the new code works correctly, select the Departments tab and then Details for one of the departments.
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Dept](http://i3.asp.net/media/49048/dept.png?cdn_id=2015-07-02-001)
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.cs uses LINQ:
var data = from student in db.Students
group student by student.EnrollmentDate into dateGroup
select new EnrollmentDateGroup()
{
EnrollmentDate = dateGroup.Key,
StudentCount = dateGroup.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.cs, replace the LINQ statement in the
- Code:
About
public ActionResult About()
{
// Commenting out LINQ to show how to do the same thing in SQL.
//IQueryable
// group student by student.EnrollmentDate into dateGroup
// select new EnrollmentDateGroup()
// {
// EnrollmentDate = dateGroup.Key,
// StudentCount = dateGroup.Count()
// };
// SQL version of the above LINQ code.
string query = "SELECT EnrollmentDate, COUNT(*) AS StudentCount "
+ "FROM Person "
+ "WHERE Discriminator = 'Student' "
+ "GROUP BY EnrollmentDate";
IEnumerable
return View(data.ToList());
}
Run the About page. It displays the same data it did before.
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Abt](http://i1.asp.net/media/49037/abt.png?cdn_id=2015-07-02-001)
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
- Code:
UPDATE
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Up](http://i1.asp.net/media/49064/up.png?cdn_id=2015-07-02-001)
In CourseContoller.cs, add
- Code:
UpdateCourseCredits
- Code:
HttpGet
- Code:
HttpPost
public ActionResult UpdateCourseCredits()
{
return View();
}
[HttpPost]
public ActionResult UpdateCourseCredits(int? multiplier)
{
if (multiplier != null)
{
ViewBag.RowsAffected = db.Database.ExecuteSqlCommand("UPDATE Course SET Credits = Credits * {0}", multiplier);
}
return View();
}
When the controller processes an
- Code:
HttpGet
- Code:
ViewBag.RowsAffected
When the Update button is clicked, the
- Code:
HttpPost
- Code:
multiplier
- Code:
ViewBag.RowsAffected
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Up7](http://i1.asp.net/media/49065/up7.png?cdn_id=2015-07-02-001)
In CourseController.cs, right-click one of the
- Code:
UpdateCourseCredits
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Addview](http://i2.asp.net/media/49038/addview.png?cdn_id=2015-07-02-001)
In Views\Course\UpdateCourseCredits.cshtml, replace the template code with the following code:
@model ContosoUniversity.Models.Course
@{
ViewBag.Title = "UpdateCourseCredits";
}
Update Course Credits
@if (ViewBag.RowsAffected == null)
{
using (Html.BeginForm())
{
Enter a number to multiply every course's credits by: @Html.TextBox("multiplier")
}
}
@if (ViewBag.RowsAffected != null)
{
Number of rows updated: @ViewBag.RowsAffected
}
@Html.ActionLink("Back to List", "Index")
Run the
- Code:
UpdateCourseCredits
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Up](http://i1.asp.net/media/49064/up.png?cdn_id=2015-07-02-001)
Click Update. You see the number of rows affected:
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Up7](http://i1.asp.net/media/49065/up7.png?cdn_id=2015-07-02-001)
Click Back to List to see the list of courses with the revised number of credits.
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Cred](http://i2.asp.net/media/49040/cred.png?cdn_id=2015-07-02-001)
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
- Code:
AsNoTracking
For an example that demonstrates how to use the AsNoTracking method, see the earlier version of this tutorial. This version of the tutorial doesn't set the Modified flag on a model-binder-created entity in the Edit method, so it doesn't need
- Code:
AsNoTracking
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
- Code:
Index
public ActionResult Index()
{
var courses = db.Courses;
var sql = courses.ToString();
return View(courses.ToList());
}
Now set a breakpoint on the
- Code:
return
- Code:
sql
- Code:
Select
{SELECT
[Extent1].[CourseID] AS [CourseID],
[Extent1].[Title] AS [Title],
[Extent1].[Credits] AS [Credits],
[Extent1].[DepartmentID] AS [DepartmentID]
FROM [Course] AS [Extent1]}
Click the magnifying class to see the query in the Text Visualizer.
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Watch](http://i3.asp.net/media/49066/watch.png?cdn_id=2015-07-02-001)
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
- Code:
Department
In CourseController.cs, replace the
- Code:
Index
public ActionResult Index(int? SelectedDepartment)
{
var departments = db.Departments.OrderBy(q => q.Name).ToList();
ViewBag.SelectedDepartment = new SelectList(departments, "DepartmentID", "Name", SelectedDepartment);
int departmentID = SelectedDepartment.GetValueOrDefault();
IQueryable
.Where(c => !SelectedDepartment.HasValue || c.DepartmentID == departmentID)
.OrderBy(d => d.CourseID)
.Include(d => d.Department);
var sql = courses.ToString();
return View(courses.ToList());
}
Restore the breakpoint on the
- Code:
return
The method receives the selected value of the drop-down list in the
- Code:
SelectedDepartment
A
- Code:
SelectList
- Code:
SelectList
For the
- Code:
Get
- Code:
Course
- Code:
Department
- Code:
true
- Code:
SelectedDepartment
In Views\Course\Index.cshtml, immediately before the opening
- Code:
table
@using (Html.BeginForm())
{
Select Department: @Html.DropDownList("SelectedDepartment","All")
}
With the breakpoint still set, run the Course Index page. Continue through the first times that the code hits a breakpoint, so that the page is displayed in the browser. Select a department from the drop-down list and click Filter:
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Fi](http://i1.asp.net/media/49052/fi.png?cdn_id=2015-07-02-001)
This time the first breakpoint will be for the departments query for the drop-down list. Skip that and view the
- Code:
query
- Code:
Course
SELECT
[Project1].[CourseID] AS [CourseID],
[Project1].[Title] AS [Title],
[Project1].[Credits] AS [Credits],
[Project1].[DepartmentID] AS [DepartmentID],
[Project1].[DepartmentID1] AS [DepartmentID1],
[Project1].[Name] AS [Name],
[Project1].[Budget] AS [Budget],
[Project1].[StartDate] AS [StartDate],
[Project1].[InstructorID] AS [InstructorID],
[Project1].[RowVersion] AS [RowVersion]
FROM ( SELECT
[Extent1].[CourseID] AS [CourseID],
[Extent1].[Title] AS [Title],
[Extent1].[Credits] AS [Credits],
[Extent1].[DepartmentID] AS [DepartmentID],
[Extent2].[DepartmentID] AS [DepartmentID1],
[Extent2].[Name] AS [Name],
[Extent2].[Budget] AS [Budget],
[Extent2].[StartDate] AS [StartDate],
[Extent2].[InstructorID] AS [InstructorID],
[Extent2].[RowVersion] AS [RowVersion]
FROM [dbo].[Course] AS [Extent1]
INNER JOIN [dbo].[Department] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID]
WHERE @p__linq__0 IS NULL OR [Extent1].[DepartmentID] = @p__linq__1
) AS [Project1]
ORDER BY [Project1].[CourseID] ASC
You can see that the query is now a
- Code:
JOIN
- Code:
Department
- Code:
Course
- Code:
WHERE
Remove the
- Code:
var sql = courses.ToString()
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.
- Features introduced in Entity Framework 6 make it easier to implement TDD without writing 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. 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
- Code:
student
- Code:
Student
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Proxy1](http://i3.asp.net/media/49059/proxy1.png?cdn_id=2015-07-02-001)
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Proxy2](http://i2.asp.net/media/49060/proxy2.png?cdn_id=2015-07-02-001)
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
- Code:
new
- Code:
Added
- Code:
DbSet
- You might want to get an actual entity type from a proxy type. You can use the GetObjectType method of the
- Code:
ObjectContext
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:
-
- Code:
DbSet.Find
-
- Code:
DbSet.Local
-
- Code:
DbSet.Remove
-
- Code:
DbSet.Add
-
- Code:
DbSet.Attach
-
- Code:
DbContext.SaveChanges
-
- Code:
DbContext.GetValidationErrors
-
- Code:
DbContext.Entry
-
- Code:
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
- Code:
SaveChanges
Entity Framework Power Tools
Entity Framework Power Tools is a Visual Studio add-in that was used to create the data model diagrams shown in these tutorials. The tools can also do other function such as generate entity classes based on the tables in an existing database so that you can use the database with Code First. 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.
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Pt1](http://i1.asp.net/media/49061/pt1.png?cdn_id=2015-07-02-001)
![12_Advanced Entity Framework 6 Scenarios for an MVC 5 Web Application Pt2](http://i1.asp.net/media/49062/pt2.png?cdn_id=2015-07-02-001)
Entity Framework source code
The source code for Entity Framework 6 is available at http://entityframework.codeplex.com/. Besides source code, you can get [url=https://entityframework.codeplex.com/wikipage?title=Nightly Builds] nightly builds[/url], issue tracking, feature specs, [url=http://entityframework.codeplex.com/wikipage?title=Design Meeting Notes] design meeting notes[/url], 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 an 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.
For more information about how to deploy your web application after you've built it, see ASP.NET Web Deployment - Recommended Resources in the MSDN Library.
For information about other topics related to MVC, such as authentication and authorization, see the ASP.NET MVC - Recommended Resources.
Acknowledgments
- Tom Dykstra wrote the original version of this tutorial, co-authored the EF 5 update, and wrote the EF 6 update. Tom is a senior programming writer on the Microsoft Web Platform and Tools Content Team.
- Rick Anderson (twitter @RickAndMSFT) did most of the work updating the tutorial for EF 5 and MVC 4 and co-authored the EF 6 update. Rick is a senior programming writer for Microsoft focusing on Azure and MVC.
- Rowan Miller and other members of the Entity Framework team assisted with code reviews and helped debug many issues with migrations that arose while we were updating the tutorial for EF 5 and EF 6.
VB
When the tutorial was originally produced for EF 4.1, we provided both C# and VB versions of the completed download project. Due to time limitations and other priorities we have not done that for this version. If you build a VB project using these tutorials and would be willing to share that with others, please let us know.
Common errors, and solutions or workarounds for them
Cannot create/shadow copy
Error Message:
Cannot create/shadow copy '' when that file already exists.
Solution
Wait a few seconds and refresh the page.
Update-Database not recognized
Error Message (from the
- Code:
Update-Database
The term 'Update-Database' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
Solution
Exit Visual Studio. Reopen project and try again.
Validation failed
Error Message (from the
- Code:
Update-Database
Validation failed for one or more entities. See 'EntityValidationErrors' property for more details.
Solution
One cause of this problem is validation errors when the
- Code:
Seed
- Code:
Seed
HTTP 500.19 error
Error Message:
HTTP Error 500.19 - Internal Server Error
The requested page cannot be accessed because the related configuration data for the page is invalid.
Solution
One way you can get this error is from having multiple copies of the solution, each of them using the same port number. You can usually solve this problem by exiting all instances of Visual Studio, then restarting the project you're working on. If that doesn't work, try changing the port number. Right click on the project file and then click properties. Select the Web tab and then change the port number in the Project Url text box.
Error locating SQL Server instance
Error Message:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Solution
Check the connection string. If you have manually deleted the database, change the name of the database in the construction string.
This article was originally created on February 14, 2014