can linq update and query atomically?

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!

From stackoverflow

• You can surround the code with a lock{}.

  Lock Keyword

  Toad : True, but I also want other processes (even on other computers) to not get these records. Also, it seems silly to foreach through 1000 records and set it to processing, when it can be done with 1 atomic sql statement

• 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.

  eglasius : linq2sql isn't only queries, but you are correct it doesn't support this scenario. There are custom methods on some blogs to do batch updates, but they don't return a result set at the same time.

• 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

  Ian Suttle : That's how I'd do it as well.

  Toad : great! thanks!