I'm trying to synchronize a table between two databases using linq. I want the primary keys (identities) in both tables to be the same. I haven't been able to accomplish this using linq.
After scouring google, the closest I can get is this:
Using DbContext as New DBContext()
'Somewhere I read that the line below ensures queries run in the same session
DbContext.Connection.Open()
Dim CopyOfShoe as New Shoe()
CopyOfShoe.ID = OriginalShoe.ID
CopyOfShoe.Color = OriginalShoe.Color
DbContext.ExecuteCommand("SET IDENTITY_INSERT Shoes ON")
DbContext.Shoes.InsertOnSubmit(CopyOfShoe)
DbContext.SubmitChanges()
DbContext.ExecuteCommand("SET IDENTITY_INSERT Shoes OFF")
End Using
But this throws an exception:
Explicit value must be specified for identity column in table 'Shoes' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
I know that I can get around this by using ExecuteCommand to insert the new record with the existing Identity, but I want to know if it is possible using just linq.