Multithreading with Linq to SQL

Question

I am creating an application that requires me to use a DataContext inside threads. My application continues to throw an InvalidOperationException , similar to:

There is already an open DataReader associated with this Command which must be closed first

ExecuteReader requires an open and available Connection. The connection current state is connecting

These exceptions are intermittent.

Here is a snippet of my code:

 var repo = new Repository(); var entities = repo.GetAllEntities(); foreach (var entity in entities) { ThreadPool.QueueUserWorkItem( delegate { try { ProcessEntity(entity); } catch (Exception) { throw; } }); } 

I think this might have something to do with passing the entity to the stream from the main thread, as the error seems to throw as soon as I try to access the entity property.

Can anyone understand why this is happening and how can I solve it?

Update

Here is what I decided to go with:

 var events = new Dictionary<int, AutoResetEvent>(); var repo = new Repository(); var entities = repo.GetAllEntities(); foreach (var entity in entities) { events.Add(entity.ID, new AutoResetEvent(false)); ThreadPool.QueueUserWorkItem( delegate { var repo = new Repository(); try { ProcessHierarchy(repo.GetEntity(entity.ID), ReportRange.Weekly); } catch (Exception) { throw; } finally { events[entity.ID].Set(); } }); } WaitHandle.WaitAll(events.Values.ToArray()); 

Improvements / Suggestions are welcome, but this seems to have done the trick.

Answer 1

An exception occurs because some properties of the object execute a new request while the previous reader is not yet closed. You cannot execute multiple queries in the context of data at the same time.

As a workaround, you can “visit” the properties that you access in ProcessEntity() and run SQL before the thread.

For example:

 var repo = new Repository(); var entities = repo.GetAllEntities(); foreach (var entity in entities) { var localEntity = entity; // Verify the callback uses the correct instance var entityCustomers = localEntity.Customers; var entityOrders = localEntity.Orders; var invoices = entityOrders.Invoices; ThreadPool.QueueUserWorkItem( delegate { try { ProcessEntity(localEntity); } catch (Exception) { throw; } }); } 

This workaround will only execute SQL on the main thread, and processing will be done on other threads. You are losing some efficiency here, since all requests are executed in one thread. This solution is good if you have a lot of logic in ProcessEntity() and the queries are not very heavy.

Answer 2

Try creating a repository inside a new thread, rather than transferring it.

Answer 3

Remember that an SqlConnection instance is NOT thread safe. Do you still have an open reader or not. In general, accessing an SqlConnection instance from multiple threads will result in unpredictable intermittent problems.

See: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

Answer 4

The solution for me was the LightSpeed ​​Persistence framework, free up to 8 entities. In the stream, single works are created.

http://www.mindscapehq.com/products/LightSpeed/default.aspx