LLBLGen 4.2, Oct 3rd 2014 build .NET 4.5.1 SQL Server 2008 R2 Adapter model

I'd like to be able to use the new async API to make multiple, independent LINQ queries to the DB within the same adapter and then await them all... something like this...

var query1 = db.EnrollmentAccess.Where(ea=>ea.Date == date).ToListAsync();
var query2 = db.LearnerCompletion.Where(ea=>ea.Date == date).ToListAsync();

var result1 = await query1;
var result2 = await query2;

For potentially expensive queries, we'd like to be able to run them in parallel, especially since they aren't dependent on one another. We've recently tried this, but we started to encounter the following exception (stack trace abbreviated...)

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The connection does not support MultipleActiveResultSets.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.InvalidOperationException: The connection does not support MultipleActiveResultSets.
   at System.Data.SqlClient.SqlCommand.<ExecuteDbDataReaderAsync>b__24(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()

In my research for this issue, I stumbled onto the section titled Re-entrance protection from here: http://www.llblgen.com/documentation/4.2/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/Async/gencode_async_gettingstarted.htm It seems this may be the issue I'm encountering, but the exception seems to suggest I can just "turn on MultipleActiveResultSets" and it will just work, but I've never heard of the feature and don't know about the ramifications of turning on MultipleActiveResultSets.

Can you please advise on what we should be doing given the simple scenario described above? Ideally we'd like to query for that data in parallel.

That is explained in the link you referenced:

documentation wrote:

The code above will make the compiler wrap all code after the await into a continuation of the task being awaited and will execute that after that task is finished, which makes the code below the await avoid re-entering the adapter during the async task.

This is equal to re-using an adapter instance in a multi-threaded environment: it's not thread safe and calling an async method doesn't free you from taking care of this: calling an async method could create multi-threading related issues with an adapter instance if you're not careful.

This also means that if you share an IDataAccessAdapter across methods on a form for example, you can have re-entrancy problems if you have async event handlers. In this case, create a new DataAccessAdapter instance every time you need one (e.g. one per method). Creating a new DataAccessAdapter is very fast (The constructor is almost completely empty) so it doesn't make your code slower.

Rule of thumb: Always await a Task returned by an async method before doing anything else on the adapter.

You have to await directly on the async task, in this case:

var query1 = await db.EnrollmentAccess.Where(ea=>ea.Date == date).ToListAsync();
var query2 = await db.LearnerCompletion.Where(ea=>ea.Date == date).ToListAsync();

To do what you expect, you should use another DataAccessAdapter instance in the second call (thus another LinqMetaData that uses another adapter instance), as you would do in a multi-thread environment, as it's not thread safe to use the same adapter in multiple threads. Like:

var query1 = new LinqMetaData(adapter1).EnrollmentAccess.Where(ea=>ea.Date == date).ToListAsync();
var query2 = new LinqMetaData(adapter2).LearnerCompletion.Where(ea=>ea.Date == date).ToListAsync();

await query1;
await query2;

About the threading issue: async/await can result in the Task being run on a threadpool thread, it depends on the execution context. As the first query is still busy on the connection, the second query can't execute over the same connection. On the same thread this isn't a problem as the second query will wait for the first one.

Don't make the mistake that async/await is the same as concurrency, it's not. If you want to run 2 queries in parallel, simply start a task like Task.Factory.StartNew(()=><code>); to run it on a separate thread from the threadpool. Of course you then have to make sure to use a different adapter each time and you have to wait using the TPL wait systems to grab the results.