"Next number" scenarios using Entity Framework

Question

I have been playing with Entity Framework and so far I like using it a lot. But everything I've done so far assumes optimistic locking which works well for me in most cases. However, I have the following scenario:

1. A SQL Server table with only one row that holds application-wide data
2. The row contains a column named "NextAvailableNumber"
3. The Application needs to read the number, increment it by 1, and update it

The above has to guarantee that any competing process has to wait to get a number until the first transaction has completed. Previously we have done this using table locks (since there's only one row), but I would like to know how this should be done using LINQ to Entities?

Thanks,

Jim K.

Answer 1

I think you need to implement this in stored procedure because and use explicit row locking (or table locking as you mentioned). Then you will call that procedure from EF. I don't think that this can be handled from application code unless you are using serializable transaction every time you work with your special table. It can have huge negative impact on application's performance.

We are actually doing something similar but our table contains plenty of rows for different sequences so I'm using stored procedure with row lock and update lock. We first wanted to call this procedure in insert method of our repository but after that I moved it into database and I call my procedure from after insert trigger. The reason for this was to deffer row locking to the time of real insertion to database not to the time of marking the entity for insert in EF context (insert itself and procedure call must be in the same transaction in our case). I modified my EF model so that related property has StoreGeneratedPattern set to computed. After each insert EF will requery DB to get assigned sequence number. The only drawback is that EF will also requery DB after each update of these entities but in our case it already do this because of timestamp. This is still in phase of testing and evaluating so I can still change my mind and reimplement it.