can linq update and query atomically?

Question

Hi,

I need to get 1000 rows from a database, and at the same time tag them as 'in process'. This way, another thread can not take the same 1000 rows and process them as well.

With linq i do something like this:

msgs = (from m in database.messages where (m.status == MESSAGESTATUSINIT) select m).Take(1000).ToList();

ideally I would at the same time set the status to MESSAGESTATUSPROCESSING. Of course this MUST be atomic.

any ideas? Or is it back to SQL?

thanks!

Answer 1

You can surround the code with a lock{}.

Lock Keyword

Answer 2

Linq itself is not going to offer this ability; Keep in mind Linq == Queries. Data updating happens elsewhere.

You might be better off in this instance writing a stored procedure to return the results; The sproc can update the table and return the results atomically, and your application then will have a single call.

Answer 3

You can't do that in linq2sql. Use a stored procedure, and configure it on the designer to output Message. This way you can manipulate the loaded entities and commit changes normally.

Check this for the how to do it (specifically Mapping the Return Type of SPROC Methods to Data Model Classes): http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx