C# / Linq-to-sql - Lock datacontext.Table on insertonsubmit

Question

I'm currently creating a method on my data access class that is going to insert an entity object to the database and I was expecting to get the latest inserted ID afterwards... and I've already done that but then I was wondering what would happend if the method somehow gets invoked twice at the same time, would it return the wrong ID?

So as a work around to that I decided to Lock the table on my datacontext:

lock(dataContext.Persons)
{
    InsertOnSubmit(person);
    dataContext.SubmitChanges();
}

but I do feel like this is inappropriate... I mean, the table isnt big and it wont take long to the datacontext to submit changes... So my question is, what kind of trouble would I run into by locking it like that?

PS: Let me know if my question was not clear enough and I'll edit it!

Answer 1

There is no need to lock your table.

LINQ-to-SQL will automatically populate your ID field with the identity from the database upon your call to .SubmitChanges() when you insert your person.

The caveat is that both your database and L2S entity must define your ID field as an identity. On your Person entity, you should have that field defined as the primary key, IsDbGenerated=true, UpdateCheck=never, and have the correct database type.

Once you submit, you should be able to simply retrieve the ID from your Person entity:

dataContext.Persons.InsertOnSubmit(person);
dataContext.SubmitChanges();
var id = person.ID; // now has the database generated identity.

Answer 2

Im not sure if this is part of your concerns, but if you use TransactionScope you can pass in a TransactionOption that will deal with multiple transactions.

Check out TransactionOption.Isolationlevel