Auto-increment a primary key in the LINQ to SQL DataContext "Insert" partial method

Question

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

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?

Answer 1

InsertProduct isn't declared as a partial method in the code above. Is this a typo in your post or have you declared it with a different signature than required and thus it is not being executed?

[EDIT] I'm not a VB programmer (I use C#), but I think your code needs to declare the code as partial as well as in the designer. This is true in C#, anyway.

Answer 2

InsertProduct is declared as a partial method in the designer generated file (MyDataClasses.designer.vb).

It is executed, in fact I can insert a breakpoint in InsertProduct and observe everything running correctly for product1. For product2 an exception is thrown by context.SubmitChanges() but the breakpoint is not hit.

Answer 3

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.

Answer 4

The same issue arises when ProductID is a uniqueidentifier:

Private Sub InsertProduct(ByVal instance As Product)

    Dim id As Guid = Guid.NewGuid
    instance.ProductID = id

    Me.ExecuteDynamicInsert(instance)

End Sub