Handling auto-incrementing IDENTITY SQL Server fields with LINQ to SQL in C#

Question

I'm building an ASP.NET MVC site that uses LINQ to SQL to connect to SQL Server, where I have a table that has an IDENTITY bigint primary key column that represents an ID.

In one of my code methods, I need to create an object of that table to get its ID, which I will place into another object based on another table (FK-to-PK relationship).

At what point is the IDENTITY column value generated and how can I obtain it from my code?

Is the right approach to:

1. Create the object that has the IDENTITY column
2. Do an InsertOnSubmit() and SubmitChanges() to submit the object to the database table
3. Get the value of the ID property of the object

Answer 1

The identity will only be generated when you call SubmitChanges, so your approach listed is a viable option.

What you can do however is to set the FK object rather than ID on the other object and LINQ to SQL will work it out for you.

EG: If TableA.TableBID is an FK to TableB.TableBID then you're referring to taking:

TableB b = new TableB();
context.TableB.InsertOnSubmit(b);
context.SubmitChanges();
TableA a = new TableA();
a.TableBID = b.TableBID;
context.TableA.InsertOnSubmit(a);
context.SubmitChanges();

But you can do it in a single submit by setting the object for the FK like this:

TableB b = new TableB();
context.TableB.InsertOnSubmit(b);
TableA a = new TableA();
a.TableB = b;
context.TableA.InsertOnSubmit(a);
context.SubmitChanges();

Which isn't much shorter code-wise in this example, but it does allow only 1 spot where you commit which is typically a good thing structurally.