In this post I am going to provide you with a hands-on example on how to take advantage of asynchronous execution using Entity Framework. EF 6 has the ability to execute a query and command asynchronously using DbContext.

Entity Framework is an object-relational mapping (ORM) framework for the .NET Framework.EF addresses the problem of Object-relational impedance mismatch. I will not be talking about that mismatch because it is well documented in many sites on the Internet.

Through that framework we can program against a conceptual application model instead of programming directly against a relational schema-model. By doing so we can decrease the amount of code we do write to access a data storage and thus decrease maintenance time. You can find many posts regarding Entity Framework in this blog.

Before I go on with my hands-on example I would like to talk about asynchronous programming.

Asynchronous programing is everywhere these days. It doesn't matter if you're on the client or the server.  On the client, you want asynchronous operations to take place because you want to keep the UI thread free.  You don't want it to block on IO operations because then the UI freezes and it doesn't responds to the user's mouse clicks. This is why the A in Ajax stands for asynchronous and it's partially why Ajax makes browser applications more responsive. It keeps that UI thread free. 

On the server, we also want async operations because that keeps the request processing thread free from blocking on IO operations that keeps them busy in executing and servicing network request. 

There's such a demand for async programming these days that Microsoft introduced new keywords into the C# language to make async programming simple. The keywords are async and await.

I would like to continue on asynchronous programming and explain what is thread processing on the application server (IIS) and how the .Net Framework maintains threads to service requests. 

If you need to build a highly scalable web application then one of your goals is to keep the threads in server process as busy as possible. 

In an environment like ASP.NET, there are only a limited number of threads reserved for processing HTTP requests. 

If these threads have to wait for a long input output operation to complete, you end up with those threads sitting idle and doing no work. 

A classic example of this is a controller action (in ASP.Net MVC) that needs to call another web service, an IO operation to produce the result.

If that request that travels over the network, and we often measure that in milliseconds,let's say that it takes 250 milliseconds, then the processing thread has to sit idle for 250 milliseconds. If all the threads are tied up and idle,any new HTTP request that are arriving at the server, ASP.NET will queue them up and force them to wait for one of this request processing threads to free out.  

We can free our threads out from running long input output operations and using asynchronous behavior on the server. This has nothing to do with Ajax or asynchronous calls on the client. People often confuse Ajax partial updates with asynchronous programing on the server.

On the web server, the .NET Framework maintains a pool of threads that are used to service ASP.NET requests.  When a request arrives, a thread from the pool is dispatched to process that request. 

If the request is processed synchronously, the thread that processes the request is busy while the request is being processed, thus the thread cannot service another request. 

This might not be a problem, because the thread pool can be made large enough to accommodate many busy threads. However, the number of threads in the thread pool is limited (the default maximum for .NET 4.5 is 5,000).  In large applications with high concurrency of  long-running requests, all available threads might be busy.  This condition is known as thread starvation. When this condition is reached, the web server queues requests.  If the request queue becomes full, the web server rejects requests with an HTTP 503 status (Server Too Busy). 

Another very important point that I want to make is that asynchrnous does not mean parallel, it simply means that the IIS web server can handle more concurrent requests. If you have a database that is the bottleneck asynchronous programming will not solve that problem. You should tackle the issues that make database performance to be the main problem in the application lifecycle (poor indexes, badly designed queries, low memory).Basically, the rule of thumb is to use an async task when your operation is accessing a slow medium like the network, disk, or database. 

You may want to have a look in this article as well.

I will build a small ASP.Net application that will fetch data from a database using the EF 6.0 (database first) as my data access layer and writing LINQ to Entities queries against the entity data model.

Building a web application using non blocking calls to the data layer is a great way to increase the scalability of your system. When performing a task asynchronously we free up the worker thread to accept another request while work is being done in the background. Entity Framework 6.0 has support for Async/Await pattern (e.g ToListAsync, FirstAsync).

1) Create an empty ASP.Net Web Application (Empty MVC Application) and give it the name EF6AspNetAsynchronous. I am using Visual Studio 2015 Enterprise edition.

2) I will use the AdventureWorks2014 database (You can download it here) for this application and more specifically the Person.CountryRegion table. I have installed SQL Server 2014 Enterprise edition in my machine. SQL Express edition will work fine.

3) I will add an ADO.Net Entity data model (inside the Models folder) using Database First. Follow the wizzard steps, create the connection string and then import into the conceptual model the Person.CountryRegion table which will become an entity in the domain model. If you want to look at those detailed steps if you are new to EF and Database First have a look at this post.

4) I will query the database using synchronous methods first. Then I will change the code in my EF 6.0 data access layer and in the controller to leverage async support in ASP.Net MVC and EF 6.0.

5) Add a new item, a class file in the Models folder. The name of the class is DBHelper.cs

public static class DBHelper
{

    public static List<CountryRegion> SelectAll()
    {

     AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
     var query = from c in ctx.CountryRegions
     orderby c.Name ascending
     select c;
     return query.ToList();
    }
}

6) Add an empty Controller with the name CountryController.cs

The code for the CountryController controller follows

public class CountryController : Controller
    {
    // GET: Country
    private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
    public ActionResult Index()
    {

    List<CountryRegion> model = DBHelper.SelectAll();

    return View(model);
    }
}

We also need to reference our model

using EF6AspNetAsynchronous.Models;

7) Add an empty View in the Views Folder. First create a new Country folder inside the Views folder.

The code for the View follows.

@model IEnumerable<EF6AspNetAsynchronous.Models.CountryRegion>
@{
ViewBag.Title = "CountryRegion";
}

<h2>Index</h2>

<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.CountryRegionCode)
</th>
<th>
@Html.DisplayNameFor(model => model.Name)
</th>
<th>
@Html.DisplayNameFor(model => model.ModifiedDate)
</th>

<th></th>
</tr>

@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.CountryRegionCode)
</td>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.ModifiedDate)
</td>

</tr>
}

</table>

8) Build and run your application. All the information is displayed in the page. Have a look at the picture below.

9) Let me show you how to create the asynchronous version of this simple web application.

In the DBHelper.cs class change the code to the code below

using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Threading.Tasks;

namespace EF6AspNetAsynchronous.Models
{
public static class DBHelper
{
public async static Task<List<CountryRegion>> SelectAllAsync()
{

AdventureWorks2014Entities ctx = new AdventureWorks2014Entities();
var query = from c in ctx.CountryRegions
orderby c.Name ascending
select c;
return await query.ToListAsync();
}

}
}

I changed the name of the method to SelectAllAsync.The method SelectAllAsync() method is denoted with async keyword.The method returns a Task of a List of CountryRegion entities. The return keyword is followed by the await keyword.

I do not call the ToList() method the return statement calls the ToListAsync() method.The ToListAsync() is the asynchronous method that EF 6.0 supports.

10) Now let's make the necessary changes to the Controller

public class CountryController : Controller
{
// GET: Country
private AdventureWorks2014Entities db = new AdventureWorks2014Entities();
public async Task<ActionResult> Index()
{

List<CountryRegion> model = await DBHelper.SelectAllAsync();

return View(model);
}

}
}

We also need to reference the using System.Threading.Tasks;  namespace.

The Index() method itself is marked with an async keyword and it returns a Task object wrapping the ActionResult.

I make a call to the SelectAllAsync() method of the DBHelper class. This time SelectAllAsync() is called with an await keyword and returns a List of CountryRegion objects.Build and run your application again. You will get the same results.

We looked into a detailed example on how to select data from a data table in a datastore, SQL Server, through EF Database first leveraging the EF 6.0 support for asynchronous operations and the Async/Await pattern of ASP.Net 4.5.

Hope it helps!!!