Is mixing WPF, LinqToSql and multiple threads a bad idea?

Question

My situation is roughly similar to this guy except that I don't need change notifications right now

I have a WPF App displaying a hierarchy. The children for each node are retrieved using a LinqToSql query. The app works perfectly when there is one thread.

Now I'd like to make things a bit faster.. by loading children asynchronously. Fire off a thread, to do the DB fetching, on completion create the corresponding tree nodes for the children.

<HierarchicalDataTemplate DataType="{x:Type viewmodels:NodeDM}" ItemsSource="{Binding Path=Children}">

After some thrashing around yesterday, I found that WPF Data Binding allows this via an IsAsync property on the Binding. So I made the following change

<HierarchicalDataTemplate .. ItemsSource="{Binding Path=Children, IsAsync=True}">

Now its mayhem, an initial bunch of nodes pass through the fire before exceptions run riot. Pasting the first one here...

System.Windows.Data Error: 16 : Cannot get 'Children' value (type 'ObservableCollection`1') from '' (type 'NodeDM'). BindingExpression:Path=Children; DataItem='NodeDM' (HashCode=29677729); target element is 'TreeViewItem' (Name=''); target property is 'ItemsSource' (type 'IEnumerable') TargetInvocationException:'System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException: Specified cast is not valid.
   at System.Data.SqlClient.SqlBuffer.get_Int32()
   at System.Data.SqlClient.SqlBuffer.get_Value()
   at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i)
   <snipped around 20-30 lines>
   at System.Data.Linq.Table`1.GetEnumerator()
   at System.Data.Linq.Table`1.System.Collections.Generic.IEnumerable<TEntity>.GetEnumerator()
   at System.Linq.Lookup`2.CreateForJoin(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.<JoinIterator>d__61`4.MoveNext()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at ICTemplates.Models.NodeDM.Load_Children()
   at ICTemplates.Models.NodeDM.get_Children()

Others include

System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

I have a deformed tree where some nodes have failed to load. I have a singleton instance of the almightly LinqToSql DataContext class, which talks to the DB. So I tried putting a lock so that multiple worker threads do not access it simultaneously.. but no luck.

partial class Node
{
  public IEnumerable<Segment> Children
  {
    lock (MyDatabaseDataContext.m_lockObject)
    {
      return // LINQ Query to join 2 tables and return a result
    }
  }

Reverting the IsAsync change makes things all good again. Why is the IsAsync property messing up LinqToSql ? The WPF Treeview is enough to make normal people pull their hair out.

Answer 1

Your problem seems to come from using the same connection object for the reads. When you have syncronous data access one connection is usually enough if you remember to open/close between reads. Thy to change your GetChildren code to create new connections (and commands/data readers etc.) on each call so multiple threads would not step on each others toes. Also if the results are cached in memory in some structure try to syncronize access to that structure using locks. You don't want one thread to try to enumerate over your elements while another is trying to add new ones.