Is it possible to get the value of the Identity Field before storing it in the entity structure

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 it.

 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 can not. The identifier, which is included in the IDENTITY column, is generated by the database upon insertion, and all the “tricks” to get around this and determine the identifier yourself are probably erroneous.

Short answer:. If you want some people to report the creation of an identifier before saving, use GUID ( UNIQUEIDENTIFIER ) or SEQUENCE (if you are running SQL Server 2012 or later).

Why you should not independently calculate the following free identifier:

Do not even consider running a query such as context.Customers.Max(c => c.Id) + 1 as a viable solution, because there is always the possibility of simultaneous access to the database: another process or thread can transfer a new object into one and the same table after you read the next "free" identifier, but before you save your object. The calculation of the next free identifier will be subject to collisions if your entire operation of obtaining the identifier, something with it and storing an object with this identifier is not atomic. To do this, you probably need to lock the table in the database, which may be ineffective.

(The same problem exists even if you use SEQUENCE s, a new feature introduced in SQL Server 2012.) ( I was wrong , see the end of the answer.)

Possible solutions:

• If you need to determine the identifier of an object before saving it, do not use the identifier that is in the IDENTITY column. Stay with the GUID because you are unlikely to come across them.

• No need to choose between one or the other: you can actually have your own cake and eat it! Nothing prevents you from having two columns of ID that you define externally (GUID) and one that remains internal to the database ( IDENTITY column); see the blog article "CQS vs. server generated IDs" by Mark Seemann for a more in-depth study of this idea. Here is a general idea using an 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 an agreement to consistently name the names of internal and public identification fields.)

• SEQUENCE s , a feature introduced in SQL Server 2012, is an alternative to having an IDENTITY column. They automatically increase, and you are guaranteed a unique number when you receive the next free identifier using NEXT VALUE FOR SomeSequence . One use case mentioned in MSDN :

  Use sequences instead of identifier columns in the following scenarios: [...] An application needs a number before inserting it into a table.

  Some reservations:

  • Retrieving the next sequence value will require additional backtracking to the database.

  • Like identity columns, sequences can be reset / re-seeded, so there is a theoretical possibility of collision with identifiers. It’s better never to reassemble the columns and sequences of identifiers if you can help them.

  • If you get the next free sequence value using NEXT VALUE FOR , but then decide not to use it, this will result in a “space” in your identifiers. Spaces obviously cannot happen with regular (inconsistent) GUIDs, because they don't have their own inherent order.

Answer 2

As far as I know, you cannot get the identifier before saving changes to the database. The database creates an identifier after the values ​​are inserted into the database.

To add to it when you call .SaveChanges() , then only it will write the changes to the database, and only then will an identification value be generated.

Answer 3

You can get this value with a little 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 feature and use it where you want.

Then use

 var nextValue = dbContext.fn_getIdentity("Employee_tbl") 

IDENT_CURRENT returns the last incremental value for the identity column. This does not mean MAX + 1, as if your previous transaction generated an identifier value for this column, but was discarded, then you will see the next value that will be generated.

Note that I did not check the syntax correctly, and this syntax is just to present the idea.

However, I would go with the solution provided by Stakx, i.e., SEQUENCE, if you are using SQL Server 2012 or higher, otherwise create a table to implement the SEQUENCE functionality by reserving the identifier when it is constantly created in the table.