Translation [MVC 5 + EF 6] 9: Async and stored procedures

Original: Async and Stored procedures with the Entity Framework in an ASP. NET MVC Application

1. Why asynchronous code is used :

The number of threads available to a server is limited, and all available threads may be in use under high load conditions. In this scenario, the server cannot process the new request until the thread is freed. In the synchronization code, many threads are occupied but do not actually do anything because they are waiting for I/O to complete. In asynchronous code, when a process waits for the I/O to complete, its threads are freed up to handle other requests. In this way, asynchronous code can use server resources more efficiently and allow the server to handle more accesses without delay.

In the previous. NET version, writing and testing asynchronous code is complex, error-prone, and difficult to debug. In. NET 4.5, writing, testing, and debugging asynchronous code becomes very simple, and usually we need to write asynchronous code unless we have a reason to not do so. Asynchronous code introduces a small amount of overhead, which can be negligible for low-traffic situations, while the potential for performance improvement in high-traffic situations is enormous.

For more information on asynchronous programming, see: Use. NET 4.5 's async support to avoid blocking calls.

2. Create a new department controller :

To view the code for the resulting index method:

 public  async  TASK<ACTIONRESULT>   var  departments = db.    Departments.include (d => D.administrator);  return  View ( await   departments. Tolistasync  ());}  

• The method async uses a keyword tag to tell the compiler to generate callbacks for parts of the method body and to automatically create Task<ActionResult> the returned object.
• The return type ActionResult changes Task<ActionResult> from.
• awaitThe keyword is used for Web service calls. When the compiler encounters this keyword, the method is divided into two parts behind the scenes. The first part ends when the asynchronous operation begins. The second part is placed in the callback method that is called after the operation is completed.
• The asynchronous version of the ToList extension is called.

Why departments.ToList is the statement departments = db.Departments语句 modified without being modified? Because only commands that execute queries or are sent to the database execute asynchronously. The query is generated, but departments.ToList the query is executed before the statement. departments = db.Departments语句 Therefore, only the ToList method is executed asynchronously. The same reason is also in the following code:

 Public AsyncTask<actionresult> Details (int?ID) {    if(id = =NULL)    {        return NewHttpstatuscoderesult (httpstatuscode.badrequest); } Department Department=awaitdb.    Departments.findAsync(ID); if(Department = =NULL)    {        returnHttpnotfound (); }    returnView (department);}

 Public Async Task<actionresult> Create (Department Department) {    if  (modelstate.isvalid)    {        db. Departments.add (department);     await db. SaveChangesAsync();         return Redirecttoaction ("Index");    }

Modify views\department\index.cshtml:

@model IEnumerable<contosouniversity. Models.department>@{viewbag.title = "departments";}<H2>Departments</H2><P>@Html. ActionLink ("Create New", "create")</P><Tableclass= "Table">    <TR>        <th>@Html. displaynamefor (model = model. Name)</th>        <th>@Html. displaynamefor (model = model. Budget)</th>        <th>@Html. displaynamefor (model = model. StartDate)</th>    <th>Administrator</th>        <th></th>    </TR>@foreach (var item in Model) {<TR>        <TD>@Html. displayfor (ModelItem = Item. Name)</TD>        <TD>@Html. displayfor (ModelItem = Item. Budget)</TD>        <TD>@Html. displayfor (ModelItem = Item. StartDate)</TD>    <TD>@Html. displayfor (ModelItem = Item. Administrator.fullname)</TD>        <TD>@Html. ActionLink ("edit", "edit", new {Id=item.            DepartmentID}) | @Html. ActionLink ("Details", "details", new {Id=item.            DepartmentID}) | @Html. ActionLink ("delete", "delete", new {Id=item. DepartmentID})</TD>    </TR>}</Table>

In the Create, Delete, details, and edit views InstructorID , change the title to "Administrator":

In the Create and edit views:

<class= "Control-label col-md-2"  for= "Instructorid"> Administrator</label>

In the Delete and details view:

< DT >     Administrator</dt>

Run:

Issues to be aware of when using EF Async programming:

• Async code is thread insecure. Therefore, do not attempt to process multiple operations in parallel using the same context instance.
• If you want to take advantage of the performance benefits of asynchronous code, you need to ensure that the library packages we use, such as paging, also use asynchrony when calling any EF method to query the database.

3. Insert, update, and delete using stored procedures :

In earlier versions of EF, we could use stored procedures to query data by executing native SQL queries, but we could not use stored procedures to perform update operations. It is easy to configure code first to use stored procedures in EF6.

3.1. Add code in Dal\schoolcontext.cs :

protected Override voidonmodelcreating (Dbmodelbuilder modelBuilder) {modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); Modelbuilder.entity<Course>()        . Hasmany (c= c.instructors). Withmany (i =i.courses). Map (t= T.mapleftkey ("CourseID")            . Maprightkey ("Instructorid")            . ToTable ("Courseinstructor")); modelbuilder.entity <Department>(). Maptostoredprocedures ();}

This line of code tells EF to use stored procedures when inserting, updating, and deleting department.

3.2. In the package Manage Console , enter the command:

Add-migration DEPARTMENTSP

Open Migrations\<timestamp>_departmentsp.csand you can see:

 Public Override voidUp () {createstoredprocedure ("dbo. Department_insert", P=New{Name= P.string (maxLength: -), Budget= P.decimal (Precision: +, Scale:4, Storetype:" Money"), StartDate=p.datetime (), Instructorid=p.int (),}, Body:@"INSERT [dbo]. [Department]              ([Name], [Budget], [StartDate], [Instructorid]) VALUES (@Name, @Budget, @StartDate, @InstructorID) DECLARE @DepartmentID int Selec T @DepartmentID = [DepartmentID] from [dbo].              [Department] WHERE @ @ROWCOUNT > 0 and [DepartmentID] = scope_identity () SELECT t0.              [DepartmentID] from [dbo]. [Department] As t0 WHERE @ @ROWCOUNT > 0 and T0. [DepartmentID] = @DepartmentID"    ); Createstoredprocedure ("dbo. Department_update", P=New{DepartmentID=p.int (), Name= P.string (maxLength: -), Budget= P.decimal (Precision: +, Scale:4, Storetype:" Money"), StartDate=p.datetime (), Instructorid=p.int (),}, Body:@"UPDATE [dbo].              [Department] SET [Name] = @Name, [Budget] = @Budget, [StartDate] = @StartDate, [Instructorid] = @InstructorID WHERE ([Depa Rtmentid] = @DepartmentID)"    ); Createstoredprocedure ("dbo. Department_delete", P=New{DepartmentID=p.int (),}, Body:@"DELETE [dbo].              [Department] WHERE ([DepartmentID] = @DepartmentID)"    ); }

3.3. In the package Manage console, enter the command:

Update-database

3.4. Run:

Code first creates a default stored procedure name. If we use a database that already exists, we need to specify the name of the stored procedure in order to use the stored procedure already defined in the database. For more information, refer to: Entity Framework Code First insert/update/delete Stored procedures.

If we want to specify what the resulting stored procedure does, we can edit the code that produces the stored procedure in the up method in the migration. In this way, the code we modify will work regardless of whether the migration runtime or the post-deployment migration is automatically run in the product database.

If we want to modify a stored procedure created in an earlier migration, we can use the add-migration command to generate a blank migration and then manually write code that calls the Alterstoredprocedure method.

4. Deploy to Azure:

This section of the operation requires that you have completed the previously optional deployment to Azure. If we encounter a migration error and decide to resolve the problem by removing the database from the local project, you can skip this section.

4.1. In Visual Studio, right-click the project andsolution Explorer to select Publish.

4.2. Click Publish.

4.3. Test whether the program is working properly.

Other EF resource: ASP. NETData access-recommendedresources.

Translation [MVC 5 + EF 6] 9: Async and stored procedures