Multithreading with Linq to SQL

Question

Since the original chain ( Multithreading with Linq to SQL ) has become quite old, I thought I would raise another question about a similar subject. Consider a scenario in which a DomainService provides many methods for retrieving data from a SQL Server database. Obviously, in a multi-user scenario, when several requests arrive simultaneously, you should expect this.DataContext to be used in parallel, without the control and additional efforts of the developer to process these several requests. So how about if I put my sequential LINQ queries in Parallel.Invoke (), all the hell breaks, and I get the scary "There is already an open DataReader associated with this Command, which should be closed first." mistake...?

To demonstrate, this works:

List<Data> retVal = new List<Data>(); retVal.AddRange(this.DataContext.Table1.Where(w=>wA==1).Select(s=>new Data{f1=sD}).ToList()); retVal.AddRange(this.DataContext.Table1.Where(w=>wB==2).Select(s=>new Data{f1=sD}).ToList()); retVal.AddRange(this.DataContext.Table1.Where(w=>wC==3).Select(s=>new Data{f1=sD}).ToList()); 

... and yet it is not:

 List<Data> retVal = new List<Data>(); Parallel.Invoke( ()=>retVal.AddRange(this.DataContext.Table1.Where(w=>wA==1).Select(s=>new Data{f1=sD}).ToList()), ()=>retVal.AddRange(this.DataContext.Table1.Where(w=>wB==2).Select(s=>new Data{f1=sD}).ToList()), ()=>retVal.AddRange(this.DataContext.Table1.Where(w=>wC==3).Select(s=>new Data{f1=sD})).ToList()); 

Do not pay attention to the fact that List is not thread safe, because the error comes from SQL data connection.

Any ideas and explanations would be highly appreciated.

Answer 1

First, to clarify, this problem is related to multi-threaded, and not with multiple users. In a multi-user scenario, each user will have their own instance of the DataContext , avoiding problems with threads around shared instances.

The parallel example fails because the DataContext not a thread safe object; he expects to be used by one thread, and not by many in parallel. This occurs as an exception related to data readers, because the DataContext has its connection open, reading with a data reader when you try to execute the second statement in parallel.

The same problem will be obvious if you try to use one SqlConnection instance for multiple threads without any serialization methods.

Answer 2

You should not share DataContext by streams. This is inherently unsafe. In addition, the DataContext intended to be used one at a time per unit of work (i.e., one per conversation). Each request should be considered as a different conversation and should respond with a unique DataContext .