在LINQ to SQL DataContext中“自动插入"主键.部分方法 [英] Auto-increment a primary key in the LINQ to SQL DataContext "Insert" partial method

问题描述

我想做的是以下几点.我有一个单独的表Products，其中包含私钥ProductID.而不是让SQL Server在插入时自动增加ProductID，我想在DataContext局部方法"InsertProduct"中增加它:

What I would like to do is the following. I have a single table, Products, containing a private key ProductID. Instead of having SQL Server auto-increment ProductID on inserts, I want to increment it in the DataContext partial method "InsertProduct":

Partial Public Class MyDataContext

    Private Sub InsertProduct(ByVal instance As Product)

        Dim id As Integer = Me.Products.Max(Function(p As Product) p.ProductID) + 1
        instance.ProductID = id

        Me.ExecuteDynamicInsert(instance)

    End Sub

End Class

但是，这仅在插入第一个Product实例时有效.尝试插入第二个实例时，检索到的ID与第一个实例相同，

However, this will only work when inserting the first Product instance. When attempting to insert a second instance, the id retrieved is the same as for the first,

Using context As New MyDataContext

    Dim product1 As New Product
    context.Products.InsertOnSubmit(product1)
    context.SubmitChanges() 'This works

    Dim product2 As New Product
    context.Products.InsertOnSubmit(product2)
    context.SubmitChanges() 'DuplicateKeyException

End Using

我在这里错过了明显的东西吗?

Am I missing something obvious here?

推荐答案

我真的建议让SQL Server进行增量编号.即使您确实使用了上面的方法，在多用户情况下(他们都获得相同的ID并尝试插入相同的ID)，这种方法在负载下也会失败.

I would really recommend letting SQL Server do the incremental numbering. The above approach even if you do get it working would fail under load in a multi-user scenario where they both get the same ID and try to insert the same one.

这篇关于在LINQ to SQL DataContext中“自动插入"主键.部分方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！