Entity Framework 4 and transactions: unmanaged changes affect the selection of transaction results?

Question

If I use transactions with Entity Framework 4, how will this be selected in the modified set, reflecting the newly saved client identifiers in the transaction, or will it generate the same identifier for each client?

using ( System.Data.Common.DbTransaction trans = new DbTransaction() ) { foreach( var customer in CustomersToSave ) { // Calculate lowest ID available var id_Query = (from c in Customers select c.ID).Distinct(); var lowest_ID_Available = (from p1 in id_Query from p2 in id_Query.Where(a => a == p1 + 1).DefaultIfEmpty() where p2 == 0 select p1).Min(); ... Create a customer with the lowest_ID_Available } trans.Commit() } 

Answer 1

This will only work if you call SaveChanges after adding each client, which is a pretty bad solution. Also, this will not be performed in the transaction, because only DbTransaction does not work until the creation of DbConnection , calling BeginTransaction . Use TransactionScope instead.

Another issue is concurrency. Depending on the level of transaction isolation, other concurrent transactions will either wait for this to complete, or they will be able to use the same identifiers as the current transaction.

This is usually handled by a separate table containing only counters. You will create a stored procedure to obtain the next counter for the given key (table name). This procedure will use atomic updates, which will set the return value and the current value counter at the same time.

Using this procedure depends on your requirements. If you need a continuous sequence of generated numbers (without holes from rollbacks), you should block the entry in this special table only for the current transaction. If you only need to get a unique number, but you do not need to have a sequence without holes, you can use simultaneous access.

An example of a stored procedure that blocks a record (if you do not need to block the removal of prompts in the update statement):

 CREATE PROCEDURE [dbo].[GetNextSequenceValue] @SequenceType VARCHAR(20) AS BEGIN DECLARE @Result INT UPDATE [dbo].[Sequences] WITH (ROWLOCK, UPDLOCK) SET @Result = Value = Value + 1 WHERE SequenceType = @SequenceType RETURN @Result END