How to make multiple async calls and return data from first call that finds matching record then stop remaining async calls

Question

I need to make multiple db calls by looping through the connection strings. There is only 1 matching record across dbs and If I find a matching record then I can return data and cancel other async calls.

using (var Contexts = instContextfactory.GetContextList())
{
    foreach(var context in Contexts.GetContextList())
    {    
        // how do I make all the calls and return data from the first call that finds data and continue with further process.(don't care about other calls if any single call finds data.           
        context.Insurance.GetInsuranceByANI(ani);
    }
}

GetInsuranceByANI

public Task<IEnumerable<Insurance>> GetInsuranceByANI(string ani)
{
    using (ITransaction transaction = Session.Value.BeginTransaction())
    {
        transaction.Rollback();
        IDbCommand command = new SqlCommand();
        command.Connection = Session.Value.Connection;

        transaction.Enlist(command);

        string storedProcName = "spGetInsurance";

        command.CommandText = storedProcName;
        command.Parameters.Add(new SqlParameter("@ANI", SqlDbType.Char, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, ani));

        var rdr = command.ExecuteReader();
        return Task.FromResult(MapInsurance(rdr));
    }
}

For example : I'm looping through with 5(a, b, c, d, e) different db connection strings. I need make asyn calls to all the 5 dbs. If I find matching record in db : b then I can return that data and continue to next steps and can stop making calls to other dbs

Answer 1

How about returning the value as soon as you get it. Not allowing the flow to move forwards and break the loop.

using (var Contexts = instContextfactory.GetContextList())
    {
           foreach(var context in Contexts.GetContextList())
           {    
               // how do I make all the calls and return data from the first call that finds data and continue with the further process.(don't care about other calls if any single call finds data.           
                var result = await context.Insurance.GetInsuranceByANI(ani);

                if(result.Any())
                {
                    return result.First();
                }
           }
    }

Answer 2

To make things simple you should first change back your GetInsuranceByANI method to become synchronous again. We will spawn tasks to call it asynchronously later.

public IEnumerable<Insurance> GetInsuranceByANI(string ani)
{
    using (ITransaction transaction = Session.Value.BeginTransaction())
    {
        transaction.Rollback();
        IDbCommand command = new SqlCommand();
        command.Connection = Session.Value.Connection;

        transaction.Enlist(command);

        string storedProcName = "spGetInsurance";

        command.CommandText = storedProcName;
        command.Parameters.Add(new SqlParameter("@ANI", SqlDbType.Char, 0, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Default, ani));

        var rdr = command.ExecuteReader();
        return MapInsurance(rdr);
    }
}

Now to the implementation of the method that searches all databases asynchronously. We will create a task for each database, running in a thread-pool thread. This is debatable, but we are trying to keep things simple. We also instantiate a CancellationTokenSource, and pass its Token to all Task.Run methods. This will only ensure that after we have our result, no more tasks are going to start. If the available threads in the thread-pool are more than the databases to search, all tasks are going to start immediately, and the cancellation token will not actually cancel anything. In other words all queries that are started are going to be completed no matter what. This is obviously a waste of resources, but again we are trying to keep things simple.

After starting the tasks we are entering a loop awaiting the next task to complete (using the method Task.WhenAny). If the result is found we cancel the token and return the result. If the result is not found we continue the loop for the next result. If all tasks complete and we still don't have a result, we return null.

async Task<IEnumerable<Insurance>> SearchAllByANI(string ani)
{
    var tasks = new HashSet<Task<IEnumerable<Insurance>>>();
    var cts = new CancellationTokenSource();
    using (var Contexts = instContextfactory.GetContextList())
    {
        foreach (var context in Contexts.GetContextList())
        {
            tasks.Add(Task.Run(() =>
            {
                return context.Insurance.GetInsuranceByANI(ani);
            }, cts.Token));
        }
    }
    while (tasks.Count > 0)
    {
        var task = await Task.WhenAny(tasks);
        var result = await task;
        if (result != null && result.Any())
        {
            cts.Cancel();
            return result;
        }
        tasks.Remove(task);
    }
    return null;
}

Usage example:

IEnumerable<Insurance> result = await SearchAllByANI("12345");
if (result == null)
{
    // Nothing fould
}
else
{
    // Do something with result
}