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

azure-sql-database c# entity-framework sql-server

Accepted Answer

Not at all. Putting an ID into aIDENTITY When a column is inserted, the database creates it; hence, any "tricks" to get around this and figure out the ID yourself are definitely faulty.

Short response: Use a GUID if you want some control over how an ID is generated before saving.UNIQUEIDENTIFIER ), or aSEQUENCE (if you're using SQL Server 2012 or a later version).

Reasons why you shouldn't calculate the upcoming free ID yourself

Never ever contemplate executing a query likecontext.Customers.Max(c => c.Id) + 1 because it's always possible to have parallel database requests; for example, another process or thread can persist a new entity to the same table before you store your entity, once you've read the next "free" ID. Unless the entire activity of receiving the ID, doing anything with it, and saving the entity with that ID were atomic, computing the next free ID will be susceptible to collisions. This would probably necessitate a database table lock, which would be ineffective.

(The issue persists even if you useSEQUENCE s, a brand-new function that SQL Server 2012 introduced.) (I was mistaken; see end of answer.)

Potential remedies

  1. Don't use the ID that goes in an if you need to know the ID of an item before saving it.IDENTITY column. Maintain a GUID since it is quite unlikely that you will collide with any of these.

  2. It's not necessary to make a trade-off because you can genuinely have your cake and eat it, too! There is nothing that prevents you from having two two columns of IDs: one that you choose (the GUID) and one that remains internal to the DB (the).IDENTITY column); for a more thorough examination of this concept, see Mark Seemann's blog post "CQS vs. IDs generated by the server". Here is an illustration of the general concept:

    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 follow a convention for naming internal and public ID field names consistently.)

  3. The feature SEQUENCE s, which was added to SQL Server 2012, is an alternative to having anIDENTITY column. When you obtain the following free ID utilizing NEXT VALUE FOR SomeSequence, they are instantly enhanced and you are ensured of receiving a special number. One of the referred to on MSDN use cases is:

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

    a few warnings:

    • An additional roundtrip to the database will be necessary to retrieve the following sequence value.

    • Sequences can be reset or re-seeded, much like identity columns, which increases the risk of ID collisions by theoretical. If at all possible, it is best to re-seed identity columns and sequences.

    • If you use to retrieve the following free sequence valueNEXT VALUE FOR , but ultimately decide against using it, there will be a "gap" in your IDs. Regular (non-sequential) GUIDs obviously cannot have gaps because they lack an inherent ordering.

15
10/26/2017 6:30:52 AM

Popular Answer

As far as I'm aware, you cannot obtain the ID prior to the database being saved with the changes. After the values are entered into the database, the ID is created by the database.

As an addition when you call.SaveChanges() The identity value will only be generated when the modifications have been written to the database.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow