Call multiple stored procedures using Async/Await and EntityFramework

Question

I have to call multiple stored procedures that consume time. Ideally those procedures have to exec in the same time but it raise lot of problems.

Here is the simplified code :

private  async void refresh_Controle(object sender, RoutedEventArgs e)
{
    SqlParameter param1 = new SqlParameter("@devicename", DeviceName);
    Task<int> mcResult = GenkaiBase.Database.ExecuteSqlCommandAsync("exec Refresh_McAfee @devicename", param1);
    int Mc = await mcResult;

    SqlParameter param2 = new SqlParameter("@devicename", DeviceName);

    Task<int> dcaiResult = GenkaiBase.Database.ExecuteSqlCommandAsync("exec Refresh_DCAI @devicename", param2);
    int Dc = await dcaiResult;
}

This has 2 issues:

1. those procedure execute one after the other
2. if I call this more than once, I get a SQL Server error where one of the procedure is chosen as victim.

I tried calling the two procedures in the same time with this code in an async method:

public async Task<bool> Refresh_Control(string devicename)
{
    List<Task> Tlist = new List<Task>();
    Console.WriteLine("Launch Refresh");

    SqlParameter param1 = new SqlParameter("@devicename", devicename);

    Task<int> mcResult =  Genkai_db.Database.ExecuteSqlCommandAsync("exec Refresh_McAfee @devicename", param1);

    SqlParameter param2 = new SqlParameter("@devicename", devicename);

    Task<int> dcaiResult =  Genkai_db.Database.ExecuteSqlCommandAsync("exec Refresh_DCAI @devicename", param2);

    Console.WriteLine("all set");

    Tlist.Add(mcResult);
    Tlist.Add(dcaiResult);

    await Task.WhenAll(Tlist.ToArray());
    int mc = await mcResult;
    int dc = await dcaiResult;

    Console.WriteLine("Finish Refresh" + mc + dc);
    return true;
}

The logic is fine for send thing simultaneous but the second procedure throw an error cause the first one isn't finish yet.

Error translated by goole:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.dll but was not handled in user code

Additional Information: A second operation was started in this context before a previous asynchronous operation has been completed. Use "await" to ensure that all asynchronous operations were completed before calling another method in this context. No member instance is guaranteed to be thread safe.

So what is the deal, why can't I call several stored procedures at the same time without being stuck by SQL Server?

Answer 1

Update

I believe that this is simply not supported by EF at this point in time, and perhaps this is a duplicate question based on this SO answer. It cannot be done... Sorry.

Original

The issue is that you are trying to await them twice. When you pass them into the await Task.WhenAll function they are running in parallel and awaited. Then afterwards you are then trying to await them again, instead of access the .Result of the task instance.

Please try the code below and let me know if it works.

public async Task Refresh_Control(string devicename)
{
    Task<int> mcResult = 
        Genkai_db.Database.ExecuteSqlCommandAsync("exec Refresh_McAfee @devicename", 
            new SqlParameter("@devicename", devicename));
    Task<int> dcaiResult = 
        Genkai_db.Database.ExecuteSqlCommandAsync("exec Refresh_DCAI @devicename", 
            new SqlParameter("@devicename", devicename));

    await Task.WhenAll(mcResult, dcaiResult);

    int mc = mcResult.Result;
    int dc = dcaiResult.Result;

    Console.WriteLine("Finish Refresh :: mc=" + mc + ", dc=" + dc);
}