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.
