Using Async and Await to break up database call (with Dapper)

Question

We're requesting thousands of objects back from Dapper and hit the parameter limit (2100) so have decided to load them in chunks.

I thought it would be a good opportunity to try Async Await - this the first time I've had a go so maybe making a school boy error!

Breakpoints are being hit, but the whole thing is just not returning. It's not throwing an error - it just seems like everything is going in a black hole!

Help please!

This was my original method - it now calls the Async method

    public List<MyObject> Get(IEnumerable<int> ids)
    {
        return this.GetMyObjectsAsync(ids).Result.ToList();
    }  //Breakpoint on this final bracket never gets hit

I added this method to split the ids into chunks of 1000 and then await for the tasks to complete

    private async Task<List<MyObject>> GetMyObjectsAsync(IEnumerable<int> ids)
    {
        var subSets = this.Partition(ids, 1000);

        var tasks = subSets.Select(set => GetMyObjectsTask(set.ToArray()));

        //breakpoint on the line below gets hit ...
        var multiLists = await Task.WhenAll(tasks);

        //breakpoint on line below never gets hit ...
        var list = new List<MyObject>();
        foreach (var myobj in multiLists)
        {
            list.AddRange(myobj);   
        }
        return list;
    }

and below is the task ...

    private async Task<IEnumerable<MyObject>> GetMyObjectsTask(params int[] ids)
    {
        using (var db = new SqlConnection(this.connectionString))
        {
            //breakpoint on the line below gets hit
            await db.OpenAsync();
            return await db.QueryAsync<MyObject>(@"SELECT Something FROM Somewhere WHERE ID IN @Ids",
            new { ids});
        }
    }

The following method just splits the list of ids in chunks - this appears to work fine ...

    private IEnumerable<IEnumerable<T>> Partition<T>(IEnumerable<T> source, int size)
    {
        var partition = new List<T>(size);
        var counter = 0;

        using (var enumerator = source.GetEnumerator())
        {
            while (enumerator.MoveNext())
            {
                partition.Add(enumerator.Current);
                counter++;
                if (counter % size == 0)
                {
                    yield return partition.ToList();
                    partition.Clear();
                    counter = 0;
                }
            }

            if (counter != 0)
                yield return partition;
        }
    }

Answer 1

You are creating a deadlock situation the way you use async/await in combination with Task<T>.Result.

The offending line is:

return this.GetMyObjectsAsync(ids).Result.ToList();

GetMyObjectsAsync(ids).Result blocks the current sync context. But GetMyObjectsAsync uses await which schedules a continuation point for the current sync context. I'm sure you can see the problem with that approach: the continuation can never be executed because the current sync contextis blocked by Task.Result.

One solution that can work in some cases would be to use ConfigureAwait(false) which means that the continuation can be run on any sync context.

But in general I think it's best to avoid Task.Result with async/await.

---

Please note that whether this deadlock situation actually occurs depends on the synchronization context that is used when calling the asynchronous method. For ASP.net, Windows Forms and WPF this will result in a deadlock, but as far as I know it won't for a console application. (Thanks to Marc Gravell for his comment)

---

Microsoft has a good article about Best Practices in Asynchronous Programming. (Thanks to ken2k)