Asynchronous Call of Entity Framework in C# 5.0


Since there is asynchronous pattern in C# 5.0 and .NET 4.5, we can think about how to apply this pattern in to EntityFramework. Natively EntityFramework doesn’t support of asynchronous calls, and you have to use classic DBReader to achieve it. In previous post about WCF and async and await keyword, we saw that asynchronous patter is already implemented when we add service in to client as Service Reference. Entity Framework probably will not have such an option, and you will have to deal with custom implementation.
If we look in to implementation of  DBReader and SqlReader in .NET 4.5 we can see that there is also async implementation as well. We don’t need to use begin/end execute reader ayn more. It is a good idea to try to implement similar pattern in Entity Framework.
So, first create a new project in Visual studio 11 DP, and create sample database, so we can test our async solution against EntityFramework.

  • Create new WPF project in Visual Studio 11 Developer Preview.
  • Give it a name AsyncEntityFrameworkDemo.


  • Within VS 11 DP open Server Explorer and create new Database DemoDb.
  • Create table Customers, with columns show on picture below

After you create a Table you need to insert a large number of rows that the retrieving data takes at least several seconds.
For that demo I have inserted about 500.000 rows. You can download my database with the Demo sample too.

After you prepare database, let’s create Entity Framework model. After that drag some controls to the MainPage.xaml designer like picture shows. You need to drag one DataGrid, Button and some other controls that we can test unblocking thread while data is retrieving from database.
Define OnLoaded event handle of the MainPage. In the event method we are going to call asynchronous implemented method.

private void OnLoaded(object sender, RoutedEventArgs e)
{
   LoadCustomersAsync();
}

The implementation of LoadCustomersAsync(); is showed of the following listing.

///
<summary> /// Retieves data asynchoronous by calling EntityFramework Translate method. It uses the DbCommand and ExecuteReaderAsync
/// for retreiving data from database
/// </summary>
private async void LoadCustomersAsync()
{
    using (DemoDbEntities ent = new DemoDbEntities())
    {
        IDbConnection conn = (ent.Connection as EntityConnection).StoreConnection;
        conn.Open();
        using (DbCommand cmd = (DbCommand)conn.CreateCommand())
        {
                var query = from p in ent.Customers
                        where p.Name.Contains("Name") && p.Surname.Contains("Surname") && (p.Name+p.Surname).Length>3
                        select p;

            //Above linq code convert to SQL statement
            string str = ((ObjectQuery)query).ToTraceString();
            cmd.CommandText = str;

            //New method in C# 5.0 Execute reader async
            var task= await cmd.ExecuteReaderAsync();

            //translate retieved data to entity customer
            var cust1 = await Task.Run(
                () => ent.Translate(task).ToList());

            //put retrieved data to obesrvable coll
            var data = new ObservableCollection(cust1);
            //Notify about collection changed
            Customer_CollectionChanged(data);
        }
    }

}

The method is constructed of calling ExecuteReaderAsync: – the new asynchronous implementation of the SqlReader. Further, we have translated the data in to EntitySet and return the list of customers.
The complete data retrieval is implemented with the async pattern.
When we run the demo, we can see that the main UI Thread is not blocked while data is retrieving from the database.
After this implementation it will be nice that we can make an extension method for asynchronous Entity Framework call. So the following list represent extension method for asynchronous call for any Entity Framework query.

///
<summary>/// Asynchronous call of Entity Framework query
/// </summary>public static class AsyncExtensions
{
    public static async Task> ExecuteAsync(this IQueryable source,SqlTransaction transaction=null)
        where T : EntityObject
    {
        var query = (ObjectQuery)source;

        //Find conncetion from query context
        var conn = ((EntityConnection)query.Context.Connection).StoreConnection as SqlConnection;
        if (conn == null)
            return null;

        //parse for sql code from the query
        var cmdText = query.ToTraceString();
        if (string.IsNullOrEmpty(cmdText))
            return null;

        //Create SQL Command object
        var cmd = new SqlCommand(cmdText);

        //if query contains parametres append them
        cmd.Parameters.AddRange(query.Parameters.Select(x => new SqlParameter(x.Name, x.Value ?? DBNull.Value)).ToArray());

        //Configure connection string
        cmd.Connection = conn;
        cmd.Connection.ConnectionString = new SqlConnectionStringBuilder(conn.ConnectionString) { AsynchronousProcessing = true }.ToString();

        //Now open the connection
        if(cmd.Connection.State!= System.Data.ConnectionState.Open)
            cmd.Connection.Open();

        //New method in C# 5.0 Execute reader async
        var reader = await cmd.ExecuteReaderAsync();
        var data = await Task.Run(() => { return query.Context.Translate(reader).ToList(); });

        return data;

    }
}

The use of this method is simple. Include in your project, and call against any Entity Framework query. In our Demo the new method looks like the folowing:

private void LoadCustomersAsyncExtensionMethod()
{
    DemoDbEntities ent = new DemoDbEntities();
    var query = from p in ent.Customers
                where p.Name.Contains("Name") && p.Surname.Contains("Surname") && (p.Name + p.Surname).Length > 3
                select p;

    //Execute EF query asynchronous
    var result = query.ExecuteAsync();

    //When the result come from database continue with and  assign to datagrid
    result.ContinueWith((asyncTask) =>
    {
        //Fill ItemsSource collection by data from database using Dispatcher, since probably we are not in UI thread
        this.Dispatcher.Invoke(
            DispatcherPriority.Normal,
            (Action)(() => customersDataGrid.ItemsSource = asyncTask.Result)
                                );

    });
}

After we define a query, we call ExecuteAsync extension method. We need to wait the data from database by calling ContinueWith task method.
When the data is come, assign the to ItemSource of DataGrid.

The Demo sample and database you can download here.

19 thoughts on “Asynchronous Call of Entity Framework in C# 5.0

  1. AMAZING! But I have two questions:
    1) Does the ExecuteReaderAsync() executes on the IO threadpool (hope not on the .net thread pool, if so, then there is no gain on a server side execution – only on client side not blocking the UI thread)?
    2) What do we loose when we do it and not directly using EF? I think we loose the first level caching of EntityFramework. Secondly, how could this merge into the Unit of Work Pattern?

    Thanks, Great Implementation,
    Shay

    • Hi
      thanks for comment and very good questions.

      For the first question there is some changes in beta version of VS 2011 and async pattern will not work with .NET 4 and below, so we have to wait the release in order to see all good and bad sides of the async pattern. But further you can see some answers for similar question about ExecuteReaderAsync() on StackOverflow .

      For second question you already answer, yes we loose tracking changes since we converted entity in to List. But I think it can be implemented so that we can track changes and store back in to db. I didnt try to implement async for this kind of queries.
      Regards

    • “Does the ExecuteReaderAsync() executes on the IO threadpool”

      yes, so it does hold some scalability benefit when used in asp.net, when used in conjunction with async pages or controllers.

  2. It’s worth mentioning that this is only required when you actually want to asynchronously access the DB itself.

    If your only goal is keeping the UI running then you only need to enumerate the query in a task and await it. For example:

    var data = await Task.Run(() => query.ToList())

    And this will be sufficient to free up the UI.

  3. Pingback: Ho to convert your old sequential code in to async « Bahrudin Hrnjica Blog

  4. The usage of ToList() in my opinion is wrong and will reduce performance, as the first element will only be accessible once the last element has been read from the DB. On the other hand if one were to use yield return, then the database connection’s lifecycle will be tied to how quickly your processing will take, potentially leaving the database connection (and associated read locks) open whilst the UI thread updates the UI.

    Personally I would reference the Rx.Net libraries and return a Subject then on the callback just

    foreach( var x in query.Context.Translate(reader))
    {subject.OnNext(x);}

    subject.OnComplete()

    Then the Subject on each element will raise an event that the UI thread can subscribe to. Thus preventing blocking, asynchronously loading your elements AND concurrently mapping your dbrow to your POCO objects!

    • Hi,
      thanks for very useful comment.
      Maybe your approach with using the Rx.NET would produce better implementation. Unfortunately I didn’t think about this post since I have written it more than year ago.
      It would be good if you would implement an example and share with the community, because lot of people came to this post by searching for async operation against EF.

  5. May I simply just say what a relief too discover a person that actually knows what they are
    discussing on the web. You definitely realize how
    to bring ann issue to light and make it important. More and more people
    ought to look at this and understand this side of your
    story. I was surprised you’re not more popular given that
    you surely possess the gift.

  6. Pingback: foodandme.in

  7. Extremely initial coming from all, navigate to the u – Torrent software.
    Bahya kumbhaka could be the state where the yogi surrenders his very self
    , inside the form of his breath, towards the lord and
    merges with universal breath it may be the noblest kind of surrender,
    because the yogi’s identity is completely merged with all the Lord.
    If you’ve never tried this before, it is a feature that will permit you to gain 33 mb a
    day.

  8. Hi admin, i must say you have high quality posts
    here. Your website should go viral. You need initial traffic only.

    How to get it? Search for: Mertiso’s tips go viral

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s