Entity Framework - Retrieve Id Before 'Savechanges' Inside a Transaction

Entity Framework - retrieve ID before 'SaveChanges' inside a transaction

The ID is generated by the database after the row is inserted to the table. You can't ask the database what that value is going to be before the row is inserted.

You have two ways around this - the easiest would be to call SaveChanges. Since you are inside a transaction, you can roll back in case there's a problem after you get the ID.

The second way would be not to use the database's built in IDENTITY fields, but rather implement them yourself. This can be very useful when you have a lot of bulk insert operations, but it comes with a price - it's not trivial to implement.

EDIT: SQL Server 2012 has a built-in SEQUENCE type that can be used instead of an IDENTITY column, no need to implement it yourself.

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

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.

Id of newly added Entity before SaveChanges()

All the call to Add() is doing is effectively registering it with the data context so that the entity can be tracked. No actual operation is done against the database at this time until (as you rightly mention) you call SaveChanges().

I'm assuming that you're using automatically-generated Ids in your table; in this case, it's the database engine which will generate that Id when the record is inserted, and Entity Framework will read this back and populate your entity for you.

So, to confirm your suspicions, no - it's not possible to get the Id before SaveChanges() is called in this case.

Hope this helps!

access id of added entity before SaveChanges() is called

If your goal is that you want both your save, and your audit log to be created at the same time, you could wrap it in a transaction scope so your method to be atomic.

    public override int SaveChanges()
    {
        ChangeTracker.DetectChanges();
        using (var scope = new TransactionScope())
        {
            var objectStateManager = ((IObjectContextAdapter) this).ObjectContext.ObjectStateManager;
            var modifiedAuditableEntities =
                objectStateManager.GetObjectStateEntries(EntityState.Modified | EntityState.Added).Where(
                    e => (IAuditable) e.Entity != null);
            var result = base.SaveChanges();
            foreach (var entry in modifiedAuditableEntities)
            {
                var entity = (IAuditable) entry.Entity;

                if (entity != null)
                {
                    switch (entry.State)
                    {
                        case EntityState.Added:
                            entity.IsAdded = true;
                            break;
                        case EntityState.Deleted:
                            entity.IsDeleted = true;
                            break;
                        case EntityState.Modified:
                            entity.IsModified = true;
                            break;
                    }

                    this.EntitySet<AuditLogEntry>().Add(
                        this.auditLogService.CreateAuditLogEntryForEntity((IAuditable) entry.Entity));
                }
            }
            base.SaveChanges();

            scope.Complete();
            return result;
        }
    }

How to retrieve ID generated by DB after saveChanges using DbContextScope

I use Mehdime's context scope wherever I can, as I've found it to be an exceptional implementation of a unit of work. I agree with Camilo's comment about the unnecessary separation. If EF is trusted to serve as your DAL then it should be trusted to work as designed so that you can leverage it completely.

In my case, my controllers manage the DbContextScope and I utilize a repository pattern in combination with a DDD design for my entities. The repository serves as the gate keeper for the interactions with the context scoped and located with the DbContextLocator. When it comes to creating entities, the repository serves as the factory with a "Create{X}" method where {X} represents the entity. This ensures that all required information needed to create the entity is provided, and the entity is associated with the DbContext before being returned so that the entity is guaranteed to always be in a valid state. This means that ones the context scope SaveChanges call is made, the bounding service has the entity with it's assigned ID automatically. ViewModels / DTOs are what the controller returns to the consumer. You also do have the option to call the DbContext's SaveChanges within the boundary of the DbContextScope which will also reveal IDs prior to the context scope SaveChanges. This is more of a very edge-case scenario for when you want to fetch an ID for loosely coupled entities. (No FK/mapped relationship) The repository also services "Delete" code to ensure all related entities, rules, and such are managed. While editing entities falls under DDD methods on the entity itself.

There may be a more purist argument that this "leaks" details of the domain or EF specific concerns into the controller, but my personal opinion is that the benefits of "trusting" entities and EF within the scope of the bounded context within the service layer far, far, outweighs anything else. It's simpler, and allows you a lot of flexibility in your code without the need for near-duplicate methods propagating to supply consumers with filtered data, or complex filtering logic to "hide" EF from the service layer. The basic rule I follow is entities are never returned outside of the boundary of their context scope. (No detach/reattach, just Select into ViewModels, and managing Create/Update/Delete on entities based on passed in view models/parameters.)

If there are more specific concerns / examples you can provide, please feel free to add some code outlining where you see those issues.

Retrieve Identity value in entity framework and still able to rollback

What about using TransactionScope

using (var scope = new TransactionScope())
{
    using (var context = new MyContext())
    {
        // Insert data

        context.SaveChanges();

        // Do something with retrieved Id
    }

    // If something goes wrong and following command is not called
    // transaction will rollback
    scope.Complete(); // Commit
}

If you do not call Complete transaction will rollback.

---

Related Topics