Multi-threading with Linq to SQL

Question

Since the original thread (Multi-threading with Linq to SQL) has become quite old by now, I've thought I'd post another question on a similar subject. Consider a scenario, where a DomainService exposes numerous methods to retrieve data from a SQL Server database. Obviously, in a multi user scenario, with multiple requests coming in at the same time, one has to expect this.DataContext to be used in parallel, with no control nor additional effort from the developer to handle those multiple requests. So how come, if I put my sequential LINQ queries into a Parallel.Invoke(), all hell breaks loose and I get the dreadded "There is already an open DataReader associated with this Command which must be closed first." error ...?

To demonstrate, this works:

List<Data> retVal = new List<Data>();

retVal.AddRange(this.DataContext.Table1.Where(w=>w.A==1).Select(s=>new Data{f1=s.D}).ToList());
retVal.AddRange(this.DataContext.Table1.Where(w=>w.B==2).Select(s=>new Data{f1=s.D}).ToList());
retVal.AddRange(this.DataContext.Table1.Where(w=>w.C==3).Select(s=>new Data{f1=s.D}).ToList());

... and yet this doesn't:

List<Data> retVal = new List<Data>();
Parallel.Invoke(
()=>retVal.AddRange(this.DataContext.Table1.Where(w=>w.A==1).Select(s=>new Data{f1=s.D}).ToList()),
()=>retVal.AddRange(this.DataContext.Table1.Where(w=>w.B==2).Select(s=>new Data{f1=s.D}).ToList()),
()=>retVal.AddRange(this.DataContext.Table1.Where(w=>w.C==3).Select(s=>new Data{f1=s.D})).ToList());

Never mind for a second that List is not thread-safe, as the error is coming from the SQL data connection.

Any insights and explanations will be much appreciated.

Answer 1

First, to clarify, this issue is to do with multi-threading rather than multi-users. In a multi-user scenario, each user will have their own DataContext instance, avoiding threading issues around shared instances.

The parallel example fails because the DataContext is not a thread-safe object; it expects to be used by a single thread, not by many in parallel. This emerges as an exception associated with data readers, because the DataContext has its connection open, reading with the data reader when you attempt to execute the second statement in parallel.

The same issue would be evident if you tried to use one SqlConnection instance across several threads without any serialization techniques.

Answer 2

You shouldn't share a DataContext across threads. It is inherently unsafe. Additionally, DataContexts are meant to be used one per unit of work (i.e., one per conversation). Each request should be considered a different conversation and should be answered with a unique DataContext.