Getting "The connection does not support MultipleActiveResultSets" in a ForEach with async-await

Question

I have the following code using Dapper.SimpleCRUD :

var test = new FallEnvironmentalCondition[] {
    new FallEnvironmentalCondition {Id=40,FallId=3,EnvironmentalConditionId=1},
    new FallEnvironmentalCondition {Id=41,FallId=3,EnvironmentalConditionId=2},
    new FallEnvironmentalCondition {Id=42,FallId=3,EnvironmentalConditionId=3}
};
test.ToList().ForEach(async x => await conn.UpdateAsync(x));

With this code, I am getting following exception:

InvalidOperationException: The connection does not support MultipleActiveResultSets

I don't understand I am awaiting each update so why am I getting this error.

Note: I have no control on the connection string so I can't turn MARS on.

Solution 1

That code starts a Task for each item in the list, but does not wait for the each task to complete before starting the next one. Inside each Task it waits for the update to complete. Try

 Enumerable.Range(1, 10).ToList().ForEach(async i => await Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now)));

Which is equivalent to

    foreach (var i in Enumerable.Range(1, 10).ToList() )
    {
        var task = Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now));
    }

If you're in a non-async method you will have to Wait(), not await each task. EG

    foreach (var i in Enumerable.Range(1, 10).ToList() )
    {
        var task = Task.Delay(1000).ContinueWith(t => Console.WriteLine(DateTime.Now));
        //possibly do other stuff on this thread
        task.Wait(); //wait for this task to complete
    }

Solution 2

You need to add attribute MultipleActiveResultSets in connection string and set it to true to allow multiple active result sets.

 "Data Source=MSSQL1;" & _  
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" & _  
    "MultipleActiveResultSets=True"  

Read more at: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

Solution 3

The problem is the ForEach method is not an asynchronous method. It will not await the Task returned by your lambda. Running that code will fire every task and not wait for completion of any of them.

General point: marking a lambda as async does not make a synchronous method you pass it into behave asynchronously.

Solution: you will need to use a foreach loop which awaits the tasks' completion.

eg: foreach (var x in xs) await f(x);

You can wrap that in a helper method if you prefer.

(I know it's an old question, but I don't think it was clearly answered)

Solution 4

MARS has some limitations and also a non-zero overhead. You can use the following helpers to make the updates sequential:

public static async Task WhenAllOneByOne<T>(this IEnumerable<T> source, Func<T, Task> process)
{
    foreach (var item in source)
        await process(item);
}

public static async Task<List<U>> WhenAllOneByOne<T, U>(this IEnumerable<T> source, Func<T, Task<U>> transform)
{
    var results = new List<U>();

    foreach (var item in source)
        results.Add(await transform(item));

    return results;
    // I would use yield return but unfortunately it is not supported in async methods
}

So your example would turn into

await test.WhenAllOneByOne(conn.UpdateAsync);

I usually call the second helper instead of Task.WhenAll, as follows:

await Task.WhenAll(source.Select(transform)); // not MARS-safe
await source.WhenAllOneByOne(transform); // MARS-safe

View more solutions