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
– Sandip Bantawa, Commented Dec 9, 2015 at 14:04

Parth Savadiya · Accepted Answer · 2017-12-13 06:43:57Z

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, 2017 at 6:43

Parth Savadiya

1,2113 gold badges18 silver badges42 bronze badges

answered Dec 8, 2015 at 20:24

David Pine

24.6k11 gold badges82 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Zwan Over a year ago

It raise the same error than my second exemple at line start with Task<int> dcaiResult = . (see the error in main post ) .a cmd is already launched i have to wait before exec a seconde one???

usr Over a year ago

await does not run tasks, it waits for tasks already running. At the time Task.WhenAll is hin both tasks are already running.

Zwan Over a year ago

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 Over a year ago

@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 .

David Pine Over a year ago

@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.

|

Collectives™ on Stack Overflow

Call multiple stored procedures using Async/Await and EntityFramework

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related