Automatically increase primary key in LINQ to SQL DataContext "Insert" partial method

Question

What I would like to do is the following. I have one product table containing the ProductID private key. Instead of SQL Server automatically incrementing ProductID on inserts, I want to increment it in the DataContext "InsertProduct" partial method:

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 instance of the product. When you try to insert a second instance, the received identifier will be 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

Did I miss something obvious here?

Answer 1

, SQL Server . , , , .

Answer 2

InsertProduct . ​​ , , , , ?

[EDIT] VB ( #), , , . #.

Answer 3

InsertProduct (MyDataClasses.designer.vb).

Running, in fact I can insert a breakpoint in InsertProduct and watch everything that works correctly for product1. An exception is thrown for product2 context.SubmitChanges(), but the breakpoint does not fall.