What is the best way to manually generate Primary Keys in Entity Framework 4.1 Code First

Question

What is the best way to manually generate Primary Keys in Entity Framework 4.1 Code First?

I am programming ASP.NET MVC 3 and I use a repository pattern.

I currently generate keys in a sequential order by using the code below:

'Code First Class
Public Class Foo
    <Key()>
    <DatabaseGenerated(DatabaseGeneratedOption.None)>
    Public Property iId As Integer

    Public Property sBar As String
End Class

'Context Class
 Public Class FooBarContext : Inherits DbContext
     Public Property Foos As DbSet(Of Foo)
 End Class

'Get the current Id

'Part of code in repository that stores Entity Foo.
Dim iCurrId as Integer = (From io In context.Foo
                         Select io.iId()).Max

Dim iNewId as Integer = iCurrId + 1

Foo.iId = iNewId

My consern is (however unlikely) that two (or more) users will try to save an entity Foo at the same time and with therefore will get the same ID and the insert will fail.

Is this a good way, or is there any better?

Please not that I CANNOT (and will not) use a database generated identity field!

Answer 1

Your concern is valid - in frequently used web site this will most likely to happen and solution is not very easy. You can use client side Guid as described by @Mikecito but it has significant performance hit and I guess you don't want to use it.

The way you are doing this at the moment is very bad because the only solution is to wrap your code in single serializable transaction - transaction must contain both selecting Id and saving the record. This will make access to your InventoryObjects sequential because each select max will lock whole table until transaction is committed - nobody else will be able to read or write data to the table during the insert transaction. It don't have to be a problem in rarely visited site but it can be NO GO in frequently visited site. There is no way to do it differently in your current set up.

The partial improvement is using separate table to hold max value + stored procedure to get the next value and increment the stored value in atomic operation - (it actually simulates sequences from Oracle). Now the only complication is if you need the sequence without gaps. For example if something goes wrong with saving of new InventoryObject the selected Id will be lost and it will create a gap in the id's sequence. If you need sequence without gaps you must again use transaction to get the next Id and save the record but this time you will only lock single record in sequence table. Retrieving the Id from sequence table should be as close to saving changes as possible to minimize time when sequence record is locked.

Here is sample of sequence table and sequence procedure for SQL server:

CREATE TABLE [dbo].[Sequences]
(
    [SequenceType] VARCHAR(20) NOT NULL, /* Support for multiple sequences */
    [Value] INT NOT NULL
)

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

The table don't need to be mapped by code first - you will never access it directly. You must create custom database initializer to add table and stored procedure for you when EF creates a database. You can try similar approach as described here. You must also add initialization record for you sequence with start value.

Now you only need to call stored procedure to get a value before you are going to save the record:

// Prepare and insert record here

// Transaction is needed only if you don't want gaps
// This whole can be actually moved to overriden SaveChanges in your context
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew, 
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
   record.Id = context.Database.ExecuteStoreCommand("dbo.GetNextSequenceValue @SequenceType", 
       new SqlParameter("SequenceType", "InventoryObjects"));
   context.SaveChanges();
}

Answer 2

Here is what I ended up using. This code is based on the post by Ladislav Mrnka, but modified to work with DbContext.

Model for storing sequence information (do not forget to add it as a DBSet in your context).

<Table("tSequences")>
Public Class Sequence
    <Key()>
    <DatabaseGenerated(DatabaseGeneratedOption.None)>
    <Display(Name:="Model name", Order:=1)>
    Public Property sModelName As String

    <Required()>
    <Display(Name:="Current Primary key value", AutoGenerateField:=False, Order:=2)>
    Public Property iCurrentPKeyValue As Integer
End Class

Initilize database and create a Stored Procedure to get and auto increment sequences.

Public Class DBInitializer
    Inherits CreateDatabaseIfNotExists(Of Context)

    Protected Overrides Sub Seed(context As Context)
        'Create stored procedure to hold
        Dim sStoredProcSQL As String = "CREATE PROCEDURE [dbo].[spGetNextSequenceValue]" & vbCrLf & _
                                        "@sModelName VARCHAR(30)" & vbCrLf & _
                                        "AS BEGIN" & vbCrLf & _
                                        "DECLARE" & vbCrLf & _
                                        "@Result INT" & vbCrLf & _
                                        "UPDATE [dbo].[tSequences] WITH (ROWLOCK, UPDLOCK)" & vbCrLf & _
                                        "SET @Result = iCurrentPKeyValue = iCurrentPKeyValue + 1" & vbCrLf & _
                                        "WHERE sModelName = @sModelName" & vbCrLf & _
                                        "RETURN @Result" & vbCrLf &
                                        "END"

        context.Database.ExecuteSqlCommand(sStoredProcSQL)
    End Sub
End Class

Get a new key (iNewKey) for Entity Foo by running the stored procedure.

Dim iNewKey As Integer

Using scope = New TransactionScope(TransactionScopeOption.RequiresNew, New TransactionOptions() With { _
    .IsolationLevel = IsolationLevel.ReadCommitted _
    })
    iNewKey = context.Database.SqlQuery(Of Integer)("DECLARE @return_value int" & vbCrLf & _
                                                    "EXEC @return_value = [dbo].[spGetNextSequenceValue]" & vbCrLf & _
                                                    "@sModelName = 'Foo'" & vbCrLf & _
                                                    "SELECT 'Return Value' = @return_value").ToList().First()
'Indicate that all operations are completed.
    scope.Complete()

    context.SaveChanges()
End Using