Mike Taulty's Blog
Bits and Bytes from Microsoft UK
LINQ to SQL: Asynchronously Executing Queries

Blogs

Mike Taulty's Blog

Elsewhere

Archives

I saw a question recently about how to asynchronously execute a LINQ to SQL query.

Unless you want to go down the "fake async" route of pushing a query out onto the ThreadPool, you can (AFAIK) do real async work by using the GetCommand() method of the DataContext and then doing the work yourself.

So, synchronously this would look something like;

using (NorthwindDataContext ctx = new NorthwindDataContext())
    {
      ctx.Connection.Open();

      var query = from c in ctx.Customers
                  where c.Country == "Spain"
                  select c;

      using (SqlCommand command = ctx.GetCommand(query) as SqlCommand)
      {
        using (SqlDataReader reader = command.ExecuteReader())
        {
          foreach (Customer c in ctx.Translate<Customer>(reader))
          {
            Console.WriteLine(c.CustomerID);
          }
        }
      }
    }

Note that I'm returning a concrete type from my query here rather than an anonymous type. As I wrote about here I don't think I can do Translate with an anonymous type.

So, to split this out into something that executes asynchronously I might do something like;

    using (NorthwindDataContext ctx = new NorthwindDataContext())
    {
      ctx.Connection.Open();

      var query = from c in ctx.Customers
                  where c.Country == "Spain"
                  select c;

      using (SqlCommand command = ctx.GetCommand(query) as SqlCommand)
      {
        SqlDataReader reader = null;
        ManualResetEvent waitEvent = new ManualResetEvent(false);

        command.BeginExecuteReader(result =>
        {
          try
          {
            reader = command.EndExecuteReader(result);            
          }
          catch (SqlException ex)
          {
            Console.WriteLine("Sorry {0}", ex.Message);
          }
          finally
          {
            waitEvent.Set();
          }
        }, null);

        waitEvent.WaitOne();

        if (reader != null)
        {
          foreach (Customer c in ctx.Translate<Customer>(reader))
          {
            Console.WriteLine(c.CustomerID);
          }
        }
      }
    }

Which might be something along the lines of how we can split it up into a sync and an async piece (note that I'm not claiming that it's correct :-)).

It might be nice to be able to wrap that up into some kind of extension method that did the work for you. You can imagine a DataContext.BeginQuery(IQueryable) and DataContext.EndQuery<T> that might do that kind of thing.

I hacked together the following example of something a little like that (so take it with a large pinch of salt as it might be broken);

namespace AsyncExtensions
{
  public static class AsyncExtensions
  {
    private class AsyncResult : IAsyncResult
    {
      public AsyncResult()
      {
        doneEvent = new ManualResetEvent(false);
      }
      public object AsyncState
      {
        get { return (state); }
        set { state = value; }
      }
      public WaitHandle AsyncWaitHandle
      {
        get { return (doneEvent); }
      }
      public bool CompletedSynchronously
      {
        get { return (false); }
      }
      public bool IsCompleted
      {
        get { return (completed); }
      }
      public void Complete()
      {
        completed = true;
        doneEvent.Set();
      }
      public Exception Exception { get; set; }
      public SqlDataReader Reader { get; set; }
      private object state;
      private bool completed;
      private ManualResetEvent doneEvent;
    }
    public static IAsyncResult BeginQuery(this DataContext ctx, IQueryable query,
      AsyncCallback callback, object state)
    {
      AsyncResult localResult = new AsyncResult();
      localResult.AsyncState = state;

      SqlCommand command = ctx.GetCommand(query) as SqlCommand;

      command.BeginExecuteReader(result =>
      {
        try
        {
          SqlDataReader reader = command.EndExecuteReader(result);
          localResult.Reader = reader;
        }
        catch (Exception ex)
        {
          // Needs to be rethrown to the caller...
          localResult.Exception = ex;
        }
        finally
        {
          // Need to call the caller...
          localResult.Complete();

          if (callback != null)
          {
            callback(localResult);
          }
        }
      }, null);
      return (localResult);
    }
    public static IEnumerable<T> EndQuery<T>(this DataContext ctx,
      IAsyncResult result)
    {
      AsyncResult localResult = (AsyncResult)result;

      if (localResult.Exception != null)
      {
        throw localResult.Exception;
      }
      return (ctx.Translate<T>(localResult.Reader));
    }
  }
}

and that allowed me to to call something a bit more like this;

 using (NorthwindDataContext ctx = new NorthwindDataContext())
    {
      ctx.Connection.Open();

      var query = from c in ctx.Customers
                  where c.Country == "Spain"
                  select c;

      ctx.BeginQuery(query, result =>
      {
        foreach (Customer c in ctx.EndQuery<Customer>(result))
        {
          Console.WriteLine(c.CustomerID);  
        }
      }, null);

      Console.ReadLine();   
    }

Remember, that code may be broken ( I didn't spend too long thinking about it ) and it certainly assumes that you will be careful in protecting your DataContext because it'll call your AsyncCallback on a different thread than the one that you call BeginQuery() on which means that you need care about using that extension EndQuery<T> on the DataContext.

Also, the other area here would be how you might perform any insert/update/delete operations asynchronously as part of a call to SubmitChanges() and I don't think there's a way of doing that other than to use one of the mechanisms to push it into the ThreadPool (where you'd still need to take care with your DataContext).

Update 1.

I asked around a little bit and I did find one way to produce an enumeration of anonymous types rather than a concrete type.

I added an additional method to that AsyncExtensions class;

    public static IEnumerable<T> EndQuery<T>(this DataContext ctx,
      IAsyncResult result,
      Func<IDataRecord, T> selector)
    {
      AsyncResult localResult = (AsyncResult)result;

      if (localResult.Exception != null)
      {
        throw localResult.Exception;
      }
      IEnumerable<T> results =
        (localResult.Reader.Cast<IDataRecord>()).Select(selector);

      return (results);
    }

 

and then I can call like this;

    using (NorthwindDataContext ctx = new NorthwindDataContext())
    {
      ctx.Connection.Open();

      var query = from c in ctx.Customers
                  where c.Country == "Spain"
                  select c;

      ctx.BeginQuery(query, result =>
      {
        foreach (var v in ctx.EndQuery(result, 
          x => new { 
            Id = (string)x["CustomerID"], 
            Name = (string)x["CompanyName"] 
          }))
        {
          Console.WriteLine(v);  
        }
      }, null);

      Console.ReadLine();   
    }

So, it's not very pretty the way that I've done it :-(

I also spotted that my SqlDataReader is not getting closed whilst taking a quick look here so that's a bit of an "issue" to say the least. Not entirely sure what I'd do about that given that those "EndQuery" methods need to really return with the reader still open. So, that would need a bit of thought - perhaps it's time to give up on this one :-)


Posted Thu, Dec 6 2007 6:07 AM by mtaulty
Filed under: , ,

Comments

Christopher Steen wrote Link Listing - December 6, 2007
on Fri, Dec 7 2007 7:15 PM
Link Listing - December 6, 2007
Christopher Steen wrote Link Listing - December 6, 2007
on Fri, Dec 7 2007 7:16 PM
ASP.NET  ASP.NET MVC Framework (Part 3): Passing ViewData from Controllers to Views [Via: ScottGu ] ...
EndQuery Method | Kill Bill Team Blog wrote EndQuery Method | Kill Bill Team Blog
on Fri, Dec 24 2010 10:05 AM

Pingback from  EndQuery Method | Kill Bill Team Blog

EndQuery Method | ???????????????????? wrote EndQuery Method | ????????????????????
on Mon, Jan 24 2011 12:12 AM

Pingback from  EndQuery Method | ????????????????????