retrieve newly inserted record ID with LINQ To SQL

Question

Using the following:

private BlogDataContext db = new BlogDataContext ();

article.Created = DateTime.UtcNow;
article.Modified = DateTime.UtcNow;

db.Articles.InsertOnSubmit(article);
db.SubmitChanges();

int id = article.Id;

I am wondering is this safe? Will it give me the Id of the article that the user just inserted, or will there be case of concurrency if another user update the article a fraction of a second after this user?

Answer 1

Your dataContext instance (named db) will capture the identity value generated by the database at the time of insert. If another user modifies the value after that point, not only will you not see it, but the DataContext instance will give you the now stale value when asked! This makes sense if you think about how a DataContext must do object tracking for data modifications.

If you're attempting to add a parent record, and then add a child record in a "safe" (atomic) way, do this instead:

Order o1 = new Order() {Date = DateTime.Now, Quantity = 3};
Order o2 = new Order() {Date = DateTime.Now, Quantity = 4};

Customer c1 = new Customer() {Name = "Bob"};
c1.Orders.Add(o1);
c1.Orders.Add(o2);

db.InsertOnSubmit(c1);
db.SubmitChanges();

The customer and both orders will be inserted into the database - all or none (if none, you get an exception telling you why).

Notice that I used the relationship property: Customer.Orders instead of using Ids. This is because I don't know what Ids the database will generate. Neither does the DataContext know. But if you examine Customer.Orders autogenerated code - you will see that the id is managed there.