LINQ Getting The Next Row ID?

Question

Lets say I have a table with RowId, and Value:

1 valueA
2 valueB
3 valueC
4 valueD

Then let's say I delete the 4th row:

1 valueA
2 valueB
3 valueC

So the next row ID will be 5. Because the 4th row was deleted, I can't simply find the last row in the table and add 1 to get the next row ID. How can I obtain the next row ID?

Thanks!

Answer 1

After you insert a new row you will get the row ID. I don't see why you would need it before you insert it!

Answer 2

In LinqToSQL, if two classes each have a PRIMARY KEY IDENTITY columns and have a connecting association (created by FKey in the database or manually in the designer), you can do your two related items insert like this:

Customer c = new Customer() {Name = "Bob"};
Order o = new Order() {Customer = c, Quantity = 5};

myDataContext.InsertOnSubmit(c);
  //submit changes will insert c and o
  //  in the right order and with all id's populated.
myDataContext.SubmitChanges();
  //the id's are populated 
int customerID = c.CustomerID;
int orderID = o.OrderID;
customerID = o.CustomerID;