Is it possible to get Identity Field value before saving it in entity framework

Question

I have a customer and sales table

CUSTOMER
--------------
Id (int auto increment)
Name

SALES
---------------
Id (int auto increment)
CustomerId (int)
OrderTotal (decimal)

With Guid i can do this.

dbTransaction = dbContext.Database.BeginTransaction(isolationLevel);

var customer = new Customer()
{
  Id = Guid.NewGuid(),
  Name = "John Doe"
};

var sales = new Sales() 
{
  Id = Guid.NewGuid(),
  CustomerId = customer.Id,
  OrderTotal = 500
};

dbContext.SaveChanges();
dbTransaction.Commit();

How can i do this if my primary key is int (with DatabaseGeneratedOption.Identity)?

Answer 1

You cannot. The ID that goes into a IDENTITY column is generated by the database upon insertion, and all "tricks" to circumvent that and determine the ID yourself are probably flawed.

Short answer: If you want some say in generating an ID before you save, use a GUID (UNIQUEIDENTIFIER), or a SEQUENCE (if you're working with SQL Server 2012 or newer).

Why you should not compute the next free ID yourself:

Don't even consider running a query such as context.Customers.Max(c => c.Id) + 1 as a viable solution, because there's always the possibility that you have concurrent database accesses: another process or thread might persist a new entity to the same table after you've read the next "free" ID but before you store your entity. Computing the next free ID will be prone to collisions, unless your whole operation of getting the ID, doing something with it, and storing the entity with that ID were atomic. This would likely require a table lock in the DB, which might be inefficient.

(The same problem exists even when you use SEQUENCEs, a new feature introduced in SQL Server 2012.) (I was wrong; see end of answer.)

Possible solutions:

1. If you need to determine the ID of an object before you save it, then don't use the ID that goes in a IDENTITY column. Stay with a GUID, because you're extremely unlikely to get any collision with these.

2. There's no need to chose between one or the other: you can actually have your cake and eat it! Nothing stops you from having two ID columns, one that you determine externally (the GUID) and one that stays internal to the DB (the IDENTITY column); see the blog article "CQS vs. server generated IDs" by Mark Seemann for a more detailed look at this idea. Here's the general idea by example:

   CREATE TABLE Foos
   (
       FooId INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
    -- ^^^^^ assigned by the DBMS upon insertion. Mostly for DB-internal use.
       Id UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT (NEWID()),
    -- ^^ can be dictated and seen by the users of your DB. Mostly for DB-external use.
       …
   );
   
   CREATE TABLE FooBars
   (
       FooId INT NOT NULL FOREIGN KEY REFERENCES Foos (FooId),
    --   use DB-internal ID in foreign key constraints ^^^^^
       …
   );
   
   CREATE VIEW PublicFoos AS
   SELECT Id, … FROM Foos;
   --     ^^ publish the public ID for users of your DB
   

   (Make sure you adhere to some convention for consistently naming internal and public ID field names.)

3. SEQUENCEs, a feature introduced in SQL Server 2012, are a possible alternative to having an IDENTITY column. They are automatically increased and you are guaranteed a unique number when getting the next free ID using NEXT VALUE FOR SomeSequence. One of the use cases mentioned on MSDN are:

   Use sequences instead of identity columns in the following scenarios: […] The application requires a number before the insert into the table is made.

   Some caveats:

   • Getting the next sequence value will require an additional roundtrip to the database.

   • Like identity columns, sequences can be reset / re-seeded, so there is the theoretical possibility of ID collisions. Best to never re-seed identity columns and sequences if you can help it.

   • If you fetch the next free sequence value using NEXT VALUE FOR, but then decide not to use it, this will result in a "gap" in your IDs. Gaps obviously cannot happen with regular (non-sequential) GUIDs because there is no inherent ordering to them.