Asynchronous LINQ to SQL

Introduction

This is going to be part of a three part series on using Linq2Sql together with WPF.

While executing your SQL queries asynchronously may have benefits even in ASP, the main benefit that I was able to see was keeping a rich client (WPF) responsive (which is why Silverlight does not allow Linq2Sql or any other synchronous network access).

In the next two articles, I will introduce two more classes that build on this one to keep all of the functionality in the presenter layer.

• SynchedCollection - A class that maintains its own CurrentItem
• EntityCollection - A wrapper for the EntitySet to fix the fact that adding and deleting from an EntitySet won't show up in binding

Usage

In this article, I am introducing only one class - the AsynchronousLinq class. It can be used as is right out of the box and does not require any other classes. You can either copy the CS file right into your project or you can add a reference to the project or the DLL. Either way will work.

The class has two main properties - LinqList & ObservableList. While working with LINQ and WPF for the past few months, I realized that almost every time I wrote a LINQ statement or accessed related elements from an entity as in contact.Emails (or something like that), I was wrapping the statement in an ObservableCollection to assist in the DataBinding. However I also often wanted to be able to go back to the LINQ statement to be able to change the expressions or use it in other queries. So why not make a reusable way to maintain both the bindable collection and the original query.

So in your ViewModel, you create as many AsynchronousLinqs as you need and then you bind to the ObservableList of each one. Then when you want to fill the list, you set LinqList which in turn populates the ObservableList.

<ListBox ItemsSource="{Binding Emails.ObservableList}">...</ListBox> 

public AsynchronousLinq<ContactMethod> Emails  { get; set; }

object SynchronizationObject = new object(); 
Emails = new AsynchronousLinq<ContactMethod> {Wait = SynchronizationObject };

Emails.LinqList = from cm in contact.ContactMethods where cm.Type == 1 select cm

The Asynchronous Bit

Linq2Sql is a powerful technology. However when it boils down to it, it is passing SQL statements to your server and retrieving data. This retrieval can take a while depending on the latency of your network, the complexity of the query & the amount of data returned. While this is happening, your user interface can freeze up. This is because the main thread of your application is waiting for the data to return so it cannot process the users interactions (or anything else). However if you offloaded the data retrieval to another thread, and only when it was done did it contact the main thread and tell it to display that data. Then your main thread is free to interact with the user until the data is ready.

Now some people confuse asynchronous data loading with Linq2Sql's delayed execution. However the truth is that the delayed execution actually makes it harder to predict exactly when the data retrieval will happen. But when it does, it will halt your main thread just as much.

The delayed execution is accomplished through the IQueryable interface. As long as you are calling functions in the IQueryable interface, instead of getting the data you requested, you will be getting a new IQueryable with the necessary expressions to be able to tell the computer (in our case SQL Server) what data you want. It is only once you call a function from IEnumerable (such as .ToList() or wrapping the IQueryable in an ObservableCollection) that the expressions are translated into actual data.

If you rely on WPF binding to call one of those functions, then there is no way to know when the data retrieval will happen. However if you call them yourself in code, then you can make sure that the code is not held up by offloading it to a different thread.

Gotchas

There were a few things we needed to take care of first.

• WPF can NOT be accessed by any thread other than the one it was initialized on.
• Linq2Sql is inherently not Thread safe

To Explain these Ideas Further

I searched Google for asynchronous Linq and found many examples. NONE of them actually worked (at least not in WPF). Some used the Thread class, some used asynchronous delegates, but all of them had the same issues - the ones listed above.

The UI Thread

Any time you access any part of WPF, you must be in the UI Thread. Raising the PropertyChanged event in the INotifyPropertyChanged interface directly calls any WPF elements bound to that property and causes them to update. Doing this from another thread is a violation of the cross threading rules and will crash your program.

To get around this, I used a wonderful built in class call BackgroundWorker. It takes two delegates - one that executes on its own thread and the second one that gets called when the first one is done (unlike the asynchronous delegates ondone delegate) on the UI Thread.

So in the background thread, I wrap the IQueryable in an ObservableCollection (causing the data retrieval to happen) and store it in a temporary variable. And in the ondone delegate which is executing on the UI Thread, I assign the collection to ObservableList and raise the PropertyChanged event, thus updating the display.

Now to the next problem - Linq2Sql in not thread safe!

If you execute 2 Linq2Sql queries at the same time, you get loads of the strangest errors.

This could not be fixed by the BackgroundWorker because the entire point of this all was to keep the data retrieval off the main thread.

So what I needed to do was to make sure that any particular DataContext only had one query executing at any particular time. That does not mean that the program could not have other things happening - it could be updating the display, it could even be querying the same database using a different DataContext.

I could have done something with the DataContext however I did not want to tie this solution directly to a particular technology.

That is where the property Wait comes in. You can assign any object to the property Wait as long as every AsynchronousLinq containing a query to objects from the same DataContext has a reference to the same object. If your program topography is such that the AsynchronousLinqs are all accessible to an class that also has access to the DataContext, then by all means you can just use the DataContext as the Wait object. However if you do not have access to the DataContext, you can use any other object as long as it's always the same object.

What do I do with that object?

Every time I call a function on IEnumerable (like creating an ObservalbeCollection), I wrap it in a lock.

  lock(Wait)
{
oc = new ObservableCollection(LinqList);
}

Now here's the catch - You must also only access the Datacontext within a lock!

That means that anytime you call a function that is going to be executing a SQL statement on the same DataContext that has an AsynchronousLinq, you must wrap that code in a lock with the same object that you assigned to the Wait property.

On the other hand, remember also to NEVER wrap a call to AsynchronousLinq.LinqList even indirectly in a call to lock with the same object that you assigned to Wait. Because if you do, you will cause a deadlock because LinqList will try to wait until you release your lock which will never happen because the call will never return because it is waiting.

Ok, I think this article has gotten long enough. It is much longer than I expected. I am much better at writing code than I am at writing articles, so just read the code and you will probably learn more from that than you will from the article.

Improvements

This class can still use some more improvements:

• Writing a better help article, I realize that this article isn't that great and that the code deserves to be better explained
• Add a demo project. To demonstrate using this class
• Add paged data retrieval. This would be useful for large sets of data. It could cycle through the LinqList using .Take(pageSize)

History

• 16^th April, 2010: Initial post