LINQ TO SQL and asynchronous IO

LINQ TO SQL doesn’t inherently support asynchronous IO. It is unfortunate, because non-blocking IO is a way to get great scalability on server side whether you do it directly with APM,   CCR Iterators  and APM adaptors, Jeffrey Richter's AsyncEnumerator, TPL iterators'  integration with APM,  or even Axum which natively recognizes APM methods.

Well there is actually a small hole through which we can use APM but in a very restrictive way.
The best  tool to experiment with L2S is LinqPAD. Point it at Northwind database and try next “statement” query:

var query = from e in Employees
   where e.EmployeeID == 1
   select e;

System.Data.Common.DbCommand cmd = this.GetCommand(query);
try
{
 this.Connection.Open();
 System.Data.Common.DbDataReader reader = cmd.ExecuteReader();
 IEnumerable employees = this.Translate(reader);
 
 //using results
 Employees employee = employees.First();
 employee.Dump();
}
finally
{
 this.Connection.Close();
}

Instead of ExecuteReader we can use BeginExecuteReader and EndExecuteReader methods.  Wrap it in an convenient extension method for DataContext class and it seams we solved the problem. No.. Here is the list of problems we still have:

• eager loading of many-to-one and one-to-many relations of the  entity in question doesn’t work with our method
• to fulfil some queries L2S has to send more than one SQL commands to the server, but DataContext.GetCommand returns only the first one
• L2S doesn’t support asynchronous lazy loading of  many-to-one, one-to-many  and Link<T> based properties of an entity
• L2S doesn’t support asynchronous compiled queries
• besides querying L2S doesn’t support  submitting changes asynchronously
• and don’t forget about transactions

The list looks like a show stopper. But if you really  need to use APM with L2S and are not going to  eager load relations of the entity you want to query and sure that your  query will generate only one SQL command and won’t be surrounded by a transaction then you  should use it because parallelism and asynchronicity  is the way to performance and scalability.