Run variable number of LINQ queries asynchronously

Question

I want to loop through a collection of db connect strings and execute a query against all DBs, then combine each IEnumerable result into one IEnumerable List<T>.

The synchronous version looks like this:

public ActionResult ListSites()
{
    ConnectionStringSettingsCollection ConnectionStrings = ConfigurationManager.ConnectionStrings;

    List<SiteInfoModel> lstSites = new List<SiteInfoModel>();
    
    foreach (ConnectionStringSettings cn in ConnectionStrings)
    {
        lstSites.AddRange(getSitesForInstance(cn));
    }

    return View("~/Views/Sites/List.cshtml", lstSites);
}

private List<SiteInfoModel> getSitesForInstance(ConnectionStringSettings css)
{            
    using (STContext db = new STContext(css.ConnectionString))
    {
        IEnumerable<SiteTracker.Data.site_info> sites = db.Sites;

        return (from s in sites
                     orderby s.name
                     select new SiteInfoModel
                     {
                         instance = css.Name,
                         siteName = formatValue(s.name, s.active),
                         siteUrl = formatValue(s.url, s.active),
                         siteId = s.site_id,
                         isActive = s.active
                     }).ToList();                
    }
}

This question may have several sub-questions since it feels like I'm struggling with a few different things:

How should getSitesForInstance() be written? Should it be
```
private async Task<List<SiteInfoModel>> getSitesForInstance()
```
If it should be async, then how do I write the linq expression? I tried changing IEnumerable to IQueryable and using .ToListAsync() but I get an error about projecting a non-anonymous type. Can it be written without async Task<T> and still have the query execute concurrently across all connection strings? If it can be done either way, is one way better or worse.
How should ListSites() look when written to make concurrent database calls? I've looked at this example but it doesn't quite get me to a working solution. I've played around with something like this:
```
 Task<List<SiteInfoModel>>[] tasks = new Task<List<SiteInfoModel>>[ConnectionStrings.Count];

 for (int i = 0; i < ConnectionStrings.Count - 1; i++)
 {
     tasks[i] = getSitesForInstance(ConnectionStrings[i]);
 }

 Task.WaitAll(tasks);
```
...but couldn't get that working. Each database call returns a List<SiteInfoModel>. When the results from all tasks are combined into the variable lstSites - which is the view model - then the problem/question is solved/answered.

Not sure why people are voting to close - but I'm also not sure why it wouldn't work. What exactly didn't work about your async version? I do something similar in an application which works fine. — Rudi Visser, Nov 16 '16 at 22:17

score 0 · Accepted Answer · edited May 23 '17 at 11:59

There are 2 answers here

To get all results in 1 variable - You could simply use Parallel.ForEach https://stackoverflow.com/a/12610915/444149 or https://msdn.microsoft.com/en-us/library/dd460680(v=vs.110).aspx

When speaking about async.. 1-rst thing You need is to make Your method GetSitesForInstance async. To do that just

Add async keyword in front of the method name
use .ToListAsync() in the query

2-nd when calling the method - add 'await' in front of it.

score 0 · Answer 2 · answered Nov 17 '16 at 22:33

here is the code that made this work:

public async Task<ActionResult> ListAsync()
{            
     ConnectionStringSettingsCollection ConnectionStrings = ConfigurationManager.ConnectionStrings;

     List<SiteInfoModel> lstSites = new List<SiteInfoModel>();

     Task<List<SiteInfoModel>>[] tasks = new Task<List<SiteInfoModel>>[ConnectionStrings.Count];

     for (int i = 0; i < ConnectionStrings.Count; i++)
     {
         tasks[i] = getSitesForInstanceAsync(ConnectionStrings[i]);
     }

     try
     {
         Task.WaitAll(tasks.ToArray());
     }
     catch (AggregateException) { }

     for (int ctr = 0; ctr < tasks.Length; ctr++)
     {
         if (tasks[ctr].Status == TaskStatus.Faulted)
             Console.WriteLine("error occurred in {0}", ConnectionStrings[ctr].Name);
         else
         {
             lstSites.AddRange(tasks[ctr].Result);
         }

     }

     return View("~/Views/Sites/List.cshtml", lstSites);
 }

private Task<List<SiteInfoModel>> getSitesForInstanceAsync(ConnectionStringSettings css)
 {
     return Task.Run(() => getSitesForInstance(css));
 }

Visual Studio is complaining that ListAsync() does not use the await keyword, but it works fine. Is this a problem? Seems like Task.WaitAll() is doing the awaiting

Run variable number of LINQ queries asynchronously

2 Answers2