StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Look into mike's article here this might help you.</p> <p><a href="http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/06/10008.aspx" rel="nofollow">http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/06/10008.aspx</a></p> <p>I saw a question recently about how to asynchronously execute a LINQ to SQL query.</p> <p>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.</p> <p>So, synchronously this would look something like;</p> <pre><code>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); } } } } </code></pre> <p>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.</p> <p>So, to split this out into something that executes asynchronously I might do something like;</p> <pre><code>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); } } } } </code></pre> <p>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 :-)).</p> <p>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 that might do that kind of thing.</p> <p>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);</p> <pre><code>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)); } } } </code></pre> <p>and that allowed me to to call something a bit more like this;</p> <pre><code> 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(); } </code></pre> <p>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 on the DataContext.</p> <p>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).</p> <p>Update 1.</p> <p>I asked around a little bit and I did find one way to produce an enumeration of anonymous types rather than a concrete type.</p> <p>I added an additional method to that AsyncExtensions class;</p> <pre><code>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); } </code></pre> <p>and then I can call like this;</p> <pre><code>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(); } </code></pre> <p>So, it's not very pretty the way that I've done it :-(</p> <p>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 :-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload