How to make parallel database calls and bind to model ASP.NET Core MVC

Question

I'm using ASP.NET Core MVC 2.0 and I've got an API that calls a method which opens a DB connection and inserts values into a model.

I'm hitting the same method 7 times and I'm just passing different information into it the parameters to build out my model. I HAVE to hit it seven different times, no rewrite of code will prevent this.

Instead of creating a loop and calling that method I wish to make parallel calls to the db so the response is faster. I've found multiple articles explaining how to do this e.g. How to properly make asynchronous / parallel database calls but there's enough difference that I can't seem to get it to work.

Following is the my method hitting the DB:

    public async Task<RS_Model> Get_Results(RequestS_Model values)
    {
       //Deleted a bunch of code to keep it simple
        string strQS = @"EXEC Get param1,param2,etc";

        using (SqlConnection conSQL = new SqlConnection(connectionString))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strQS, conSQL))
            {
                conSQL.Open();

                using (SqlDataReader dtrSQL = cmdSQL.ExecuteReader())
                {
                    while (dtrSQL.Read())
                    {
                        Double.TryParse(dtrSQL["Value1"].ToString(), out dblVal1);

                    } //Ends While
                } //end SQLDataReader
            } //Ends cmdSQL
        } //ends using

        results.Price = dblVal1;

        return  results;
    } //Ends Get Results

My IActionResult for the api is:

    [HttpGet]
    public async Task<IActionResult> Get([FromQuery] RequestS_Model values)
    {
        SV_Results Results = new SV_Results();

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        RS_Model model = new RS_Model();

        model.dblr[0] = await  Results.Get_Results(values);
        values.Parm1 = 2;
        model.dblr[1] = await    Results.Get_Results(values);
        values.Parm1 = 3;
        model.dblr[2] = await    Results.Get_Results(values);
        values.Parm1 = 4;
        model.dblr[3] = await    Results.Get_Results(values);
        values.Parm1 = 5;
        model.dblr[4] = await    Results.Get_Results(values);
        values.Parm1 = 6;
        model.dblr[5] = await    Results.Get_Results(values);
        values.Parm1 = 7;
        model.dblr[6] = await    Results.Get_Results(values);

        //int[] results = await Task.WhenAll(new Task<int>[] { task1, task2 });


        return new OkObjectResult(model);
    } //IActionResults

I know that I've forced them into a synchronous call be doing what I am, but I can't seem to make the 7 calls asynchronous and then wait for them to be all done before I build my final model. The final response needs to be in Json, but I haven't even gotten that far yet.

The DB queries themself are inherently single-tasked. There are implicit, row to table level locks around every Query. As long as you can do it all in a single execute Query, there will not be issues. Once you split it over multiple queries (like getting a value and writing it), that is when race conditions can occur. So try to keep any math to the Query Langauge. — Christopher, Dec 14 '19 at 01:08
Mostly I am really confused why you do not just make a version of GetResults that takes an array and does some basic loop/wair all on the sub-operations. Or use a table valued Stored Procedure. — Christopher, Dec 14 '19 at 01:12
@Chris, in your question you pass the same `values` object to `Results.Get_Results()`, that is slightly confusing. Please elaborate 1) how to you bind model with the sql query (inline into SQL string, pass as parameters to command...) 2) why do you pass `RequestS_Model ` 7 times unmodified (where it is changed ) — fenixil, Dec 14 '19 at 01:17
@fenixil I deleted that code out to try and make it more simple to read. However, you are correct. In my quest to make it a little more simple to follow I added a little confusion. — Chris, Dec 14 '19 at 02:34
@Christopher This stored procedure only makes a couple select statements (but no update, alter, delete etc) against a database table so a chance of any row locks are at a minimum. The rest of the 2,000 lines of code is doing lots and lots of math. I can't rewrite the procedure because of some of the legacy requirements surrounding it. — Chris, Dec 14 '19 at 02:44
Even selects can run into race conditions. Namely someone updating stuff between two selects. Once your stored procedure or code does more then 1 DML or DQL Statements, it is a risk for race conditions. | Also if you are doing "lots of math", this might actually call for proper Multithreading. In most cases it is more trouble the worth. But (propably) CPU heavy math? That is the rare Exception where they do make sense. — Christopher, Dec 14 '19 at 03:25
Somewhat off-topic, but make sure to use [C# Coding Conventions](https://learn.microsoft.com/en-us/dotnet/csharp/programming-guide/inside-a-program/coding-conventions) if you don't wanna drive your fellow C#/.NET devs into madness and making their life harder reading your code — Tseng, Dec 14 '19 at 05:50
P.S. ASP.NET Core 2.0 and 2.2 are End of life and not recieving any updates more. Consider upgrading to 2.1 or least 3.1 (both LTS releases) — Tseng, Dec 14 '19 at 05:54

score 2 · Accepted Answer · answered Dec 14 '19 at 02:04

Instead of this:

 model.dblr[0] = await Results.Get_Results(values);
 model.dblr[1] = await Results.Get_Results(values);
 model.dblr[2] = await Results.Get_Results(values);
 model.dblr[3] = await Results.Get_Results(values);
 model.dblr[4] = await Results.Get_Results(values);
 model.dblr[5] = await Results.Get_Results(values);
 model.dblr[6] = await Results.Get_Results(values);

Create a list of tasks and await them as a group:

var tasks = Enumerable.Range(0,7).Select( i => Results.Get_Results(values) ).ToList();
await Task.WhenAll(tasks);
for (int i=0; i<7; i++) model.dblr[i] = tasks[i].Result;

How to make parallel database calls and bind to model ASP.NET Core MVC

1 Answers1