在实体框架4.1代码中手动生成主键的最佳方式是什么？ [英] What is the best way to manually generate Primary Keys in Entity Framework 4.1 Code First

问题描述

在实体框架4.1中首先手动生成主键的最佳方式是什么？

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

我正在编程ASP.NET MVC 3，我使用了一个存储库模式。

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

我的保存是（但不太可能）两个（或更多）用户将尝试同时保存一个实体Foo，因此将获得相同的ID，插入将失败。

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!

推荐答案

这是我最后使用的。这个代码是基于Ladislav Mrnka的帖子，但修改为使用DbContext。

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

存储序列信息的模型（不要忘记将其添加为DBSet在您的上下文）

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

通过运行存储过程获取Entity Foo的新密钥（iNewKey）。

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

这篇关于在实体框架4.1代码中手动生成主键的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！