Is it possible to get Identity Field value before

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.

Answer 2

As far as I know you can not get the ID before saving the changes in the database. The database creates the ID after the values are inserted in the database.

To add to it when you call .SaveChanges() then only it will write the changes to the database and only then the identity value will get generated.

Answer 3

You can get that value by a small hack.

Create a function in SQL Server something like this

CREATE FUNCTION fn_getIdentity(@tbl_name varchar(30))
AS
BEGIN

IF @tbl_name = 'Employee_tbl'
   RETURN IDENT_CURRENT('Employee_tbl')
ELSE IF @tbl_name = 'Department_tbl'
   RETURN IDENT_CURRENT('Department_tbl')
ELSE
   RETURN NULL
END

Create an entity in your Entity framework to support this function and use it wherever you want.

Then use

var nextValue = dbContext.fn_getIdentity("Employee_tbl")

IDENT_CURRENT returns you last incremented value for an identity column. This doesn't mean MAX + 1 as if your previous transaction generated an identity value for this column but was rolled back then you will see next value that will be generated.

Please note, I didn't check syntax properly and this syntax is just to present an idea.

However I would go with solution provided by Stakx i.e. SEQUENCE if using SQL Server 2012 or above else creating a table to implement functionality of SEQUENCE by reserving ID once generated permanently in a table.