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'll reuse the pages you've created before, and then you'll need to create a new page and use the original SQL to bulk update all course credits in the database.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

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

run the original SQL query

The Entity Frameworkcode first API includes methods that let you send SQL commands directly to the database.

There are several ways to achieve this:

• Using the Dbset.sqlquery method to query and return entity types, the object types returned must be the expected Dbset objects, which are actively tracked by the database context themselves 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 run non-query type commands.

One of the advantages of using the Entity Framework is that it avoids a high degree of coupling between the code and the particular method of implementing access to the data. It achieves this by proactively 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 run specific SQL queries, you must write them manually.

When you run SQL commands in a Web application, you must take the necessary precautions to protect your site from SQL injection attacks. One of the ways to do this is to use a parameterized query to ensure that a string submitted through a Web page is not interpreted as a SQL command. In this section, you will learn how to work with user input using a query that is counted.

run the query and return the entity

The Dbset<tentity> class provides a way for you to use this method to run a query and return an entity type of TEntity. Next you need to change 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);}

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

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

run the query and return 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 run a query that returns objects of a non-entity type. This means that 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 ());}

Execute the project. Open the About page. The data displayed is the same as before.

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

Run the update query

If Contoso University administrator want to be able to run bulk operations in the database. For example, change each course credit. However, if the school has a large number of course, for each course to update the efficiency is very low. In this section, you will create a Web page that allows the user to choose whether to change all course credits, which you can do by running the SQL UPDATE statement.

Open CourseController.cs. Adding 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 include 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, and then the SQL statement that updates course is run and the number of affected rows returned is assigned 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. Select the Add View

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">

Open views\course\updatecoursecredits.cshtml. Replace with the following code

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

Click Update to see the number of affected course returned

Click Back to List and view the changed credits
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
non-tracking queries
When the database context retrieves a data row and creates an entity object and renders it. By default, it keeps track of whether entities in memory are synchronized with the database. In-memory data is cached and used when updating entities, so caching is not usually necessary in Web applications. Because the life of the context instance is typically short-lived (each request creates a new instance and finally destroys it), the context is often destroyed by reading the entities and before using them again.
The ability to use the Asnotracking method to disable tracing for a memory entity object. In a few typical scenarios below. You may need 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 is already being 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.
in detail. You will be forced to run a single instructor business rule that cannot act as a administrator for multiple department.
(based on the functionality of the Department page you've already completed, there may already be multiple department with the same administrator, in a production environment, you need to run a new rule to handle the data that already exists.) However, it is not necessary in this demo sample. ）
The 
 opens DepartmentController.cs. Add a new method. and call it in the edit and create methods to ensure that no multiple department have 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;
Execute the project, open the Department edit page, change a department administrator to a administrator that already has a department instructor, view the error message
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
Execute the Department edit page again. Change the budget. Click Save and you will see the 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 中文版 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. This 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 English department there is also an entity.
 
  
 
 
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. Since you do not need to update the entity or read it again 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 change department's budget, this time the operation will be successful.
check the SQL sent to the database
Sometimes it is useful to look at the SQL that is actually sent to the database. You've learned how to use interceptors to do this, and then you'll show you how to do that without using interceptors. As an attempt. You'll check what's going to happen by adding features like pre-loading, filtering, and sorting.
Open the Controllers/coursecontroller and change the index method. Temporarily disable pre-loading
Public ActionResult Index () {    var courses = db. Courses;    var sql = courses. ToString ();    Return View (courses. ToList ());}
Then set a breakpoint on the return statement and press F5 to execute the project in debug mode, open the course index page, check the query variable when executing to the breakpoint, and you will see the statement sent to SQL Server. It 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
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
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 headings to sort them. and specify the Department navigation property as pre-loaded.
Open CourseController.cs and Change 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, assuming that no matter what option is selected, the parameter is null.
A SelectList collection that includes all department is passed to the View drop-down list.
The number of parameters passed to the SelectList constructor specifies the value field name, the text field name, and the selected option.
The Get method for the course warehouse. The code specifies the filter expression, sort, and delay loading for the department navigation property. Assume that the drop-down table below does not have any options selected. 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 starts the tag.
@using (Html.BeginForm ()) {    <p>select Department: @Html. DropDownList ("Selecteddepartment", "all")       <input type= "Submit" value= "Filter"/></P>}
Executes the project, opens the course index page, and resumes execution when a breakpoint is encountered in order to display the page. Select a department from the drop-down list and click Filter

The first time you execute to a breakpoint, the code is querying the department data for the drop-down list. Skip this breakpoint and view 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 a join connection query is included in the 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. It can also promote self-motivated 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:

 
 

  
  
 
   
The EF context class itself isolates your code from a particular data store.
 
   
The EF context class is able to update the database as a unit of work class, just as it does with EF.
 
   
The attributes in 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 run a query), it always creates an instance of the dynamically generated derived object and acts as the 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 of type student 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.
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvam9obnnvbmjsb2c=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/southeast ">
When an entity's properties are visited, the proxy class overrides some of the entity's virtual properties to actively insert the hooks for the run action, and one of the functions of that mechanism is to delay loading.
Most of the time you don't have to care about the use of such proxies. But there are exceptions:

 
 

  
  
 
   
In some cases, you may want to prevent the Entity Framework from creating proxy instances. Like what. Typically you 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 transport data object (DTOS) instead of the entity object, and one way 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 your own active tracking capabilities.
Usually you do not need to use lazy loading because you are creating a new entity that is not in the database. And suppose you explicitly mark an entity as added, you don't usually need to change the trace. However, assuming you need to use deferred 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.
 
  
 
 
Self-motivated change detection
The Entity Framework determines whether the entities are changed by comparing the current and original values of the entity. The original value is stored when the entity is queried or attached. Some of the ways that can lead to self-change monitoring are as follows:

 
 

  
  
 
   
Dbset.find
 
   
Dbset.local
 
   
Dbset.remove
 
   
Dbset.add
 
   
Dbset.attach
 
   
Dbcontext.savechanges
 
   
Dbcontext.getvalidationerrors
 
   
Dbcontext.entry
 
   
Dbchangetracker.entries
 
  
 
 
Suppose you are tracking a large number of entities. And you call these methods multiple times in a loop, by using the Autodetectchangesenabled property to temporarily disable your own active-change monitoring to achieve a significant improvement in program performance.
Self-proactively verify
When you call the SaveChanges method, by default. The Entity Framework verifies all the properties in all the changed entities before updating the data to the database.
Suppose you have updated a large number of entities and have verified the data. This operation is not necessary. You can reduce the processing time to save these changes by temporarily disabling validation. You can use the Validateonsaveenabled property to do this.
Entity Framework Power Tools
Entity Framework Power Tools is a Visual Studio extension. You can use it to create a data model diagram. Other features of the tool. For example, build an entity class based on an existing database table. After the tool is installed. You'll see some additional options in the context menu, for example, when you right-click on the Solution Explorer, you'll find an option to generate a chart. You can't change 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/. In addition to the source code, you can get nightly builds, problem tracking, feature specifications, design meeting notes and more, and you can submit bugs and contribute your own enhancements.
Although the source code is open, the entity Framework is fully supported by Microsoft. The Microsoft Entity Framework Team is constantly receiving feedback and testing all the code changes to ensure the quality of each published version number.
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 code: 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