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:

those procedure execute one after the other
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?

entity framework you can't, you can with multiple context, but I guess you can do this with normal ADO.NET — Sandip Bantawa, Dec 09 '15 at 14:04

score 7 · Accepted Answer · edited Dec 13 '17 at 06:43

7

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);
}

edited Dec 13 '17 at 06:43

Parth Savadiya

1,203
3
18
40

answered Dec 08 '15 at 20:24

David Pine

23,787
10
79
107

It raise the same error than my second exemple at line start with Task dcaiResult = . (see the error in main post ) .a cmd is already launched i have to wait before exec a seconde one??? – Zwan Dec 09 '15 at 08:56
await does not run tasks, it waits for tasks already running. At the time Task.WhenAll is hin both tasks are already running. – usr Dec 09 '15 at 08:58
yes and the error is not on the await its rigth at line that exec the second procedure. Its ether EF or SQL server that reject the exec.But i doubt it have no fix to this. – Zwan Dec 09 '15 at 09:07
@David you confirm its EF limit so mean i can parallele with let say ADO? i know it raise sql probleme too but if i have just to deal with deadlock than this limite it maybe better to use ADO . – Zwan Dec 09 '15 at 13:48
@Zwan Yes, the limitation is with EF. EF is throwing the exception, straight-up ADO.NET would allow you to do both in parallel. Please mark my answer as "accepted" if you find it to be correct. Thank you – David Pine Dec 09 '15 at 13:53
@DavidPine I guess await Task.WhenAll is the place OP is having problem – Sandip Bantawa Dec 09 '15 at 14:07
1

@brykneval, that is where the symptom of the problem shows itself -- as the invocation of the asynchronous calls will then throw as EF detects that you're attempting two calls in parallel. – David Pine Dec 09 '15 at 14:10

Call multiple stored procedures using Async/Await and EntityFramework

1 Answers1