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.

About these ads

About Bahrudin Hrnjica

Senior Software Developer at daenet, Microsoft MVP for C#. Likes C , WPF, Silverlight, WP7, Math, Mechanical Engineering, Evolutionary Algorithms, Blogger.

Posted on 13/10/2011, in .NET, C#, Windows 8, WPF and tagged , . Bookmark the permalink. 9 Comments.

  1. This is super useful especially for performance-critical web development tasks ! thank you

  2. odličan članak, zemljače :-)

  3. Shay Ben-Sasson

    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.

  4. Tamir Daniely

    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.

  5. 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.

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 520 other followers