.NET Entity Framework Add New Row With Incremental Primary Key

Question

I have a table in a SQL Server database which I want to add a row to. The primary key is not auto increment and I don't want it to be. However, on this occassion, I do want to add a row with the next available value. Is there a simple way to do this? I know I could query the table for the max value and then increment it but is there an easier way when using EF?

If a separate query is neccessary getting the MAX value how would you do this with LINQ?

Answer 1

It would be necessary unless the server is identity or computed column, in such a case you could do something like below.

 myRecord.ID = Context.Records.Max(record => record.ID) + 1;

Answer 2

There is not easy way for that, especially if you insist on next available value (= no gaps). You should use auto increment value and it will make your application perform much better.

What is the problem?

1. You need to get a value from the database
2. You need to assign it to your entity
3. You need to save the entity to the database

Looks easy, isn't it? No it isn't if you have concurrent environment where more then one thread can do the step 1. concurrently before other threads reach the step 3. = all threads will insert record with the same value.

How to deal with it? One way is to use separate table with max value and atomic operation to get and increment the value. I think stored procedure doing something like this should be atomic:

DECLARE @Result INT
UPDATE SequenceTable SET @Result = MaxValue = MaxValue + 1 WHERE SequnceName = '...'
RETURN @Result

The call to the stored procedure should be outside of any transaction to make best throughput.

This should hopefully allow you to generate unique numbers but not sequence without gaps. If you take the value by such operation and you will not use that value, it will be lost and your sequence will contain gap.

If you don't want gap you must put the stored procedure call and the operation into transaction so that record in sequence table is locked until data are committed.