MVC5 Entity Framework Learning Entity Framework Advanced Features

In the previous article, you have learned how to implement a table inheritance for each hierarchy. In this section, you will learn the advanced features that you can take advantage of when you develop an ASP. NET Web application using entity Framework Code first.

In this section you will reuse the pages that you have created before, and then you need to create a new page and use the original SQL to bulk update all course credits in the database.

Add new validation logic to the department edit page and use non-tracking queries.

execute the original SQL query

The Entity Frameworkcode First API contains methods that let you send SQL commands directly to the database. Here are some ways to do this:

• Using the Dbset.sqlquery method to query and return entity types, the object types returned must be the expected Dbset objects, which are automatically tracked by the database context unless you disable the tracing feature.
• Use the Database.sqlquery method to query and return non-entity types. The returned data is not tracked by the database context, even if you use the method to retrieve the entity type.
• Use Database.executesqlcommand to execute non-query type commands.

One of the advantages of using the Entity Framework is that it avoids the high degree of coupling between code and the particular method of implementing access to the data, and it does this by automatically generating SQL queries and commands, so you don't have to write a lot of code manually. But in special cases, when you need to execute specific SQL queries, you must write them manually.

When you execute SQL commands in a Web application, you must take the necessary precautions to protect your site from SQL injection attacks. One way to do this is to use parameterized queries to ensure that strings submitted through web pages are not interpreted as SQL commands. In this section, you will learn how to use parameterized queries to handle user input.

Execute Query and return entity

The Dbset<tentity> class provides a way that you can use this method to execute a query and return an entity type of TEntity. Next you need to modify the details method in the department controller to see how the method works.

Open DepartmentController.cs, replace the Db.Departments.Find method with the Db.Departments.SqlQuery method

Public async task<actionresult> Details (int? id) {    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);}

Run the project, select the Departments tab, click the Details link, and verify that the new code is working correctly.

executes the query and returns other types of objects

Previously you added a student stats feature to the About page to show the number of students enrolled each year. Here's how LINQ is used:

var data = from student in db. Students           group student by student. EnrollmentDate into Dategroup           select New Enrollmentdategroup ()           {               enrollmentdate = Dategroup.key,               Studentcount = Dategroup.count ()           };

If you want to do this by writing SQL statements directly instead of using LINQ, you need to execute a query that returns a non-entity type object, which means you need to use the Database.sqlquery method.

Open HomeController.cs and replace it with the following code

Public ActionResult About () {//commenting-out LINQ to show how to does the    same thing in SQL.    iqueryable<enrollmentdategroup> = from student in db. Students    //           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"        + "WHERE discriminator = ' Stu Dent ' "        +" GROUP by enrollmentdate ";    ienumerable<enrollmentdategroup> data = db. database.sqlquery<enrollmentdategroup> (query);    return View (data. ToList ());}

Run the project, open the About page, and display the same data as before.

Perform an update query

Assume that Contoso University administrator wants to be able to perform bulk operations in the database, such as modifying the credits for each course. However, if the school has a large number of course, it is very inefficient to update each course separately. In this section, you will create a Web page to enable users to choose whether to modify all course credits, which you can do by executing SQL UPDATE statements.

Open CourseController.cs, add HttpGet and HttpPost updatecoursecredits methods

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 the HttpGet request, the viewbag.rowsaffected variable does not contain any values, and an empty text box and a Submit button are displayed in the view.

When the Update button is clicked, the HttpPost method is called, multiplier contains the value entered in the text box, next executes the SQL statement that updates course and assigns the number of affected rows returned to the viewbag.rowsaffected variable. When the view obtains the value of the variable, it is displayed.

Open CourseController.cs, right-click on the Updatecoursecredits method and select Add View

Open views\course\updatecoursecredits.cshtml and replace it with the following code

@model contosouniversity.models.course@{    viewbag.title = "Updatecoursecredits";} 
Run the project, select the Courses tab, run the Updatecoursecredits method

Click Update to see the number of affected course returned

Click Back to List and view the modified credits

non-tracking queries
When a database context retrieves a data row and creates an entity object and renders it, by default it keeps track of whether the entity in memory is synchronized with the database. In-memory data is cached and used when updating entities, which is typically not required in a Web application because the life of the context instance is usually short-lived (each request creates a new instance and eventually destroys it), and the context is often destroyed before the entities are read and reused.
You can use the Asnotracking method to disable trace functionality for memory entity objects. In the following typical scenarios, you may want to disable the tracing feature:

 
 

  
  
 
   
A query needs to retrieve a large amount of data, and disabling tracing may significantly improve performance.
 
   
You want to attach an entity to update it, but you've already acquired the same entity object for different purposes, because the entity has been tracked by the database context, so you can't attach the entity you want to change. One way to handle this is to use the asnotracking option in the query.
 
  
 
 
In this section you will implement the business logic for the second scenario above. Specifically, you will enforce a business rule that instructor cannot act as a administrator for multiple department. (based on the functionality of the Department page you have already completed, there may already be multiple department with the same administrator, in a production environment you need to execute a new rule to handle the data that already exists, but not required in this example.) ）
 Open DepartmentController.cs, add a new method, and invoke it in the edit and create methods to ensure that no more than one department has the same administrator  
 
private void Validateoneadministratorassignmentperinstructor (Department Department) {    if (Department. Instructorid = null)    {        Department duplicatedepartment = db. Departments            . Include ("Administrator")            . Where (d = = D.instructorid = = Department. Instructorid)            . FirstOrDefault ();        if (duplicatedepartment! = NULL && duplicatedepartment.departmentid! = department. DepartmentID)        {            String errormessage = String.Format (                "instructor {0} {1} is already administrator of the {2} D Epartment. ",                DuplicateDepartment.Administrator.FirstMidName,                DuplicateDepartment.Administrator.LastName,                duplicatedepartment.name);            Modelstate.addmodelerror (String. Empty, errormessage);}}}    
Add code in the try code block in the HttpPost edit method to invoke the method without a validation error
[HttpPost] [Validateantiforgerytoken]public ActionResult Edit (   [Bind (Include = "DepartmentID, Name, Budget, StartDate, RowVersion, PersonID ")]    Department Department) {   try   {      if (modelstate.isvalid)      {         Validateoneadministratorassignmentperinstructor (department);      }      if (modelstate.isvalid)      {         db. Entry (department). state = entitystate.modified;         Db. SaveChanges ();         Return redirecttoaction ("Index");      }   }   catch (Dbupdateconcurrencyexception ex)   {      var entry = ex. Entries.single ();      var clientvalues = (Department) entry. Entity;
Run the project, open the Department edit page, change one of the department administrator to administrator that is already another department instructor, and view the error message

Run the Department edit page again, change budget, click Save, and you will see an error message raised by the Validateoneadministratorassignmentperinstructor method in the page
Exception information:
Attaching an entity of type ' ContosoUniversity.Models.Department ' failed because another entity of the same type already h As the same primary key value. This can happen when using the ' Attach ' method or setting the state of a entity to ' unchanged ' or ' Modified ' if any entit IES in the graph has conflicting key values. This could be because some entities is new and has not yet received database-generated key values. The ' Add ' method or the ' Added ' entity State to track the graph and then set the state of Non-new Entitie S to ' unchanged ' or ' Modified ' as appropriate.
The error is caused by the following sequence of events:

 
 

  
  
 
   
The Edit method calls the Validateoneadministratorassignmentperinstructor method to retrieve all department that are used by Kim Abercrombie as Administrator, This causes the English department to be read, because of this read operation, the English department entity is being tracked by the database context.
 
   
The Edit method attempts to set the flag bit of the English Department entity created by the model binder, which causes the context to attempt to attach the entity. However, the context cannot attach the entity created by the model binder because the context is tracking another entity in English department.
 
  
 
 
One way to solve this problem is to keep track of the context of the department entity retrieved by the validation query in memory, but it makes no sense to do so because you do not need to update the entity or re-read it from memory.
Open DepartmentController.cs, specified as non-tracked in the Validateoneadministratorassignmentperinstructor method
Department duplicatedepartment = db. Departments   . Include ("Administrator")   . Where (d = = D.personid = = Department. PersonID)   . Asnotracking ()   . FirstOrDefault ();
Try again to modify department's budget, this time the operation will be successful.
check the SQL sent to the database
Sometimes it's useful to see what SQL is actually sent to the database, and you've learned how to use interceptors to do this, and then show you how to do that without using interceptors. As an attempt, you'll check what's going to happen by adding things like preload, filtering, and sorting.
Open Controllers/coursecontroller, modify the index method, temporarily disable preload
Public ActionResult Index () {    var courses = db. Courses;    var sql = courses. ToString ();    Return View (courses. ToList ());}
Then set a breakpoint on the return statement, press F5 to run the project in debug mode, open the course index page, and when you run to the breakpoint, examine the query variable, and you will see a SQL Server queries statement, which is a simple SELECT statement.
{SELECT [Extent1]. [CourseID] As [CourseID], [Extent1]. [Title] As [Title], [Extent1]. [Credits] As [Credits], [Extent1]. [DepartmentID] As [Departmentid]from [Course] as [Extent1]}
Click the amp icon to view the query statement in the text visualizer
Next you need to add a drop-down list to the course index page so that the user can use it to filter specific department. You can use the title to sort and specify the department navigation property as preloaded.
Open CourseController.cs and modify the index method:
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<course> courses = db. Courses        . Where (c =!) Selecteddepartment.hasvalue | | C.departmentid = = DepartmentID)        . (d = d.courseid)        . Include (d = d.department);    var sql = courses. ToString ();    Return View (courses. ToList ());}
The breakpoint is still set on return.
The method receives the value from the selected drop-down list in the Selecteddepartment parameter, which is null if no options are selected.
A drop-down list that contains all department SelectList collections that are passed to the view. The arguments passed to the SelectList constructor specify the value field name, the text field name, and the selected option.
For the Get method of the course warehouse, the code specifies the filter expression, sort, and lazy load for the department navigation property. If no option is selected in the drop-down table, the filter expression always returns True (that is, the selecteddepartment value is null).
Open views\course\index.cshtml, add a drop-down list and a Submit button before the table start tag.
@using (Html.BeginForm ()) {    <p>select Department: @Html. DropDownList ("Selecteddepartment", "all")       <input type= "Submit" value= "Filter"/></P>}
Run the project, open the Course index page, continue running when a breakpoint is encountered to display the page, select a department from the drop-down list and click Filter

The first time you run to a breakpoint, the code is querying the department data for the drop-down list, skipping this breakpoint and viewing the query variable at the next breakpoint.
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 contains a join connection query to load the department and course data.
Delete the var sql = conrses in the code. ToString ();
Warehouses and JobsUnitMode
Many developers implement warehouse and work cell patterns as wrappers, which tend to create an abstraction layer between the application's data access layer and the business logic layer. Implementing these patterns helps isolate the application from changes in the data store and can facilitate automated unit testing or test-driven development (TDD). However, using EF to write code to implement these patterns is not the best choice. There are several reasons for this:

 
 

  
  
 
   
The EF context class itself isolates your code from a particular data store.
 
   
The EF context class can be used as a work unit class for database updates, just as it does with EF.
 
   
The attributes in the Entity Framework 6 allow you to implement TDD without having to write warehouse code.
 
  
 
 
proxy class
When the Entity Framework creates a solid instance (for example, when you execute a query), it always creates an instance of a dynamically generated derived object and acts as a proxy for the entity object. For example, the following two debuggers, in the first one, you can see the student variable that is expected to be the student type after instantiating the entity, and in the second, you can see the proxy class after the student entity is read from the database using EF.

When an entity's properties are accessed, the proxy class overrides some of the entity's virtual properties to automatically insert hooks for the execution of the action, one of the functions of which is to delay loading.
Most of the time you don't care about the use of this agent, but there are exceptions:

 
 

  
  
 
   
In some cases, you may want to prevent the Entity Framework from creating proxy instances. For example, you typically want to serialize an entity that is a Poco class instead of a proxy class. One way to avoid serialization problems is to serialize the data Transfer object (DTOS) instead of the entity object, and the other is to disable proxy creation.
 
   
When you instantiate an entity class with the new operator, you get not a proxy instance, which means that you cannot use such things as lazy loading and automatic tracing. Typically you do not need to use lazy loading because you are creating a new entity that is not in the database, and if you explicitly mark the entity as added, you typically do not need change tracking. However, if you need to use lazy loading and change tracking, you can create a new entity instance proxy by using the Create method of the Dbset class.
 
   
You might want to get a real entity type from a proxy object, and you can use the Getobjecttype method of the ObjectContext class to get the actual entity type of the instance of the proxy type.
 
  
 
 
Automatic change detection
The Entity Framework determines whether the entity is changed by comparing its current and original values, and the original value is stored when the entity is queried or attached. Some of the methods that can lead to automatic change monitoring are as follows:

 
 

  
  
 
   
Dbset.find
 
   
Dbset.local
 
   
Dbset.remove
 
   
Dbset.add
 
   
Dbset.attach
 
   
Dbcontext.savechanges
 
   
Dbcontext.getvalidationerrors
 
   
Dbcontext.entry
 
   
Dbchangetracker.entries
 
  
 
 
If you are tracking a large number of entities, and you call these methods multiple times in a loop, you can gain a significant improvement in program performance by using the Autodetectchangesenabled property to temporarily disable automatic change monitoring.
Automatic validation
When you call the SaveChanges method, by default, the Entity Framework validates all the attributes in all the changed entities before updating the data to the database. If you have updated a large number of entities and have verified the data, the operation is unnecessary, and you can reduce the processing time for saving these changes by temporarily disabling validation, which you can do with the Validateonsaveenabled property.
Entity Framework Power Tools
Entity Framework Power Tools is a Visual Studio extension that you can use to create a data model diagram. The tool also has some other features, such as generating entity classes based on existing database tables. After installing the tool, you will see some additional options in the context menu, for example, when you right-click on the Solution Explorer, you will find an option to generate the chart. You can't modify the data model in a chart when you're using code first, but you can move them to make it easier to understand.


Entity Framework Source code
You can get the source code for entity Framework 6 from http://entityframework.codeplex.com/, and you can get the features of nightly builds, problem tracking, feature specifications, design meeting notes, and more, in addition to the source code. You can submit a bug and contribute your own enhancements.
Although the source code is open, the entity Framework is fully supported by Microsoft. The Microsoft Entity Framework Team continuously receives feedback and tests all code changes to ensure the quality of each release version.
Original: Advanced Entity Framework 6 Scenarios for an MVC 5 Web application
Welcome reprint, please indicate the source of the article: http://blog.csdn.net/johnsonblog/article/details/39560037
Project Source: https://github.com/johnsonz/MvcContosoUniversity
Also everyone a healthy network environment, from you and I start
The END
MVC5 Entity Framework Learning Entity Framework Advanced Features