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.

In your connection string, be sure to set MultipleActiveResultSets=True" — pmbAustin, Sep 11 '17 at 19:59
@pmbAustin I am trying to avoid that because I am not in control of the connection string. — MotKohn, Sep 11 '17 at 20:08

score 79 · Answer 1 · answered Sep 11 '17 at 19:59

79

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://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets

answered Sep 11 '17 at 19:59

vendettamit

14,315
2
32
54

1

I am trying to avoid that because I am not in control of the connection string. – MotKohn Sep 11 '17 at 20:08
5

In that case you should use non-async method to update your entities in a sequential manner. – vendettamit Sep 11 '17 at 20:14
3

Why doesn't `await`ing help? – MotKohn Sep 11 '17 at 20:16
8

Because your program will execute the update command in multiple threads which would require simultaneous access to connection. – vendettamit Sep 11 '17 at 20:23
1

Read this documentation on async programming with Ado.net https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/asynchronous-programming – vendettamit Sep 11 '17 at 20:24
1

this worked so well. I converted my linq code to stored procedures but ran in the multiple active resultset error. I added MultipleActiveResultSets=True to the end of my connection string and my code worked. I am using var reader = await command.ExecuteReaderAsync() to access my resultset – Golden Lion Mar 26 '20 at 17:36

score 25 · Accepted Answer · answered Sep 11 '17 at 20:51

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
    }

This helped me! Was (stupidly I might say) trying to access DB in a non-async method. — user2768479, Dec 27 '20 at 21:18
The answer below by @RafaelReis mentions the actual cause and provides a solution that does not require delays! Thanks for that! — MarTim, Oct 24 '22 at 06:38

score 24 · Answer 3 · answered Sep 13 '18 at 20:58

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)

score 6 · Answer 4 · answered Jul 18 '18 at 05:57

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

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

4 Answers4

Linked