Using Linq to Sql asynchronously with new async/await

Question

What are best practices to use L2S with new C# 5 async/await keywords comparing to this approach? Couldn't find any on web.

Answer 1

EF 5 does not have async/await support, but the open source version is actively looking into possibilities here. EDIT: the Async support in EF is documented at http://msdn.microsoft.com/en-us/data/jj819165.aspx. It doesn't stream the results in as they are hydrated (as you would find with RX) but it does make the database calls asynchronous.

As for LINQ to SQL, outside of wrapping your request in a Task.Factory.Start operation, I wouldn't hold my breath hoping that task based async (required for async/await) will be implemented by Microsoft for Linq to SQL.

You could use the IQToolkit and extend it adding your own async support if absolutely necessary. Also, Mono has implemented LINQ to SQL which you might be able to extend with async support.

Answer 2

Scott Hanselman has an interesting post where he demonstrates how one could produce an async API on top of an existing Linq to SQL query. I haven't time too play around with the idea but I'm guessing that one could create a more generic extension method which would allows the same technique to be expanded to any object of type IQueryable or IEnumerable.

Here is the code directly from his post to use as a reference.

SqlCommand _beginFindCmd = null;

public IAsyncResult BeginFind(int id, AsyncCallback callback, Object asyncState)
{
    var query = from w in _db.Widgets
                where w.Id == id
                select w;
    _beginFindCmd = _db.GetCommand(query) as SqlCommand;
    _db.Connection.Open();
    return _beginFindCmd.BeginExecuteReader(callback, asyncState, System.Data.CommandBehavior.CloseConnection);
}

public Widget EndFind(IAsyncResult result)
{
    var rdr = _beginFindCmd.EndExecuteReader(result);
    var widget = (from w in _db.Translate<Widget>(rdr)
                  select w).SingleOrDefault();
    rdr.Close();
    return widget;
}

With a little bit of work one could make this TPL and likewise even cleaner as a single async method. If I get a chance to do just this I'll post what I come up with.