Multi-threading with Linq to SQL?

The exception is thrown since some properties of entity executes new query while a previous reader has not been closed yet. You cannot execute more than one query on the data context at the same time As a workaround you can "visit" the properties you access in ProcessEntity() and make the SQL run prior to 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 execute the SQL only on the main thread and the processing will be done in other threads.

You loose here some of the efficiency since all the queries are done in the single thread. This solution is good if you have a lot of logic in ProcessEntity() and the queries are not very heavy.

The exception is thrown since some properties of entity executes new query while a previous reader has not been closed yet. You cannot execute more than one query on the data context at the same time. As a workaround you can "visit" the properties you access in ProcessEntity() and make the SQL run prior to 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 execute the SQL only on the main thread and the processing will be done in other threads. You loose here some of the efficiency since all the queries are done in the single thread.

This solution is good if you have a lot of logic in ProcessEntity() and the queries are not very heavy.

– James Nov 29 '09 at 14:26 The better way is to run on one context the the query for Entities and inside each WorkItem start new DataContext, but if DataContext does not use some sort of connections pool you can easily reach max number of connections and encounter new exception :) Another solution is to visit all properties marked as data members using reflection and avoid the explicit access to properties. It'll behave the same and your DB query won't run in background thread... – Elisha Nov 29 '09 at 14:41 Ah so in each of my anon delegates retrieve the entity again and just work off that datacontext! It's pretty unlikely (at the minute) that the connections will get to max at the moment however is there a better way of managing connection sizes?

I though SQL managed this internally? – James Nov 29 '09 at 14:48 I am not sure about it, but I think that it manages the connection in a way where if you go over the limit it waits for a connection to be released. I am very unsure about it, I haven't played with it for long time :) – Elisha Nov 29 '09 at 14:57 1 @James Elisha is right, SQL Server will stop accepting connections once it reaches its limit, all new connection attempts will block until one of the existing connections closes or the connection timeout on the connection attempt is reached.

DbLinq should respect . NET's connection pool because under the hood it simply uses the ADO. NET provider to connect to the server.

– Rory Nov 29 '09 at 15:28.

Be aware that a SqlConnection instance is NOT thread safe. Whether you still have an open reader or not. In general, the access of a SqlConnection instance from multiple threads will cause you unpredictable intermittent problems.

See: msdn.microsoft.com/en-us/library/system.....

Try creating the Repository inside the new thread instead of passing it in.

I need to find the entity outside it. However, I suppose I could just send in the ID of the entity and re-fetch it inside the thread. – James Nov 29 '09 at 13:33 That sounds like your best bet, I don't think its safe to pass ObjectContext instances around the way you're trying to do.

– Rory Nov 29 '09 at 13:35 @James given the way you are creating the workitem for the thread (using an anonymous delegate), you should be able to rely on c# closure support to capture the value of the ID instead of having to pass it in. Take a look at blogs.msdn. Com/matt/archive/2008/03/01/… for more info.

– Rory Nov 29 '09 at 14:02 @James After reviewing your code again, I realised that my suggestion changes the way your query is processed (having the query and processing done on a background thread vs. having the query run on one thread and the processing done on multiple background threads). If you want to keep your processing semantics unchanged, Elisha's answer would be the better choice. The only catch is having to force the related entities you want to access during processing to load before creating the new thread.

Sorry for wasting any time you may have spent on it already. – Rory Nov 29 '09 at 14:30 No worries Rory, thanks for your suggestions. – James Nov 29 '09 at 14:33.

The solution for me was LightSpeed Persistence framework is free until 8 entities Per thread create the unitwork. mindscapehq.com/products/LightSpeed/defa....

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.