Programatically get next value for primary key when IDENTITY is set to OFF

asp.net-mvc-5 c# entity-framework-6

Question

The primary key for my table is set to[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]

Is there a better way to duplicate records into a new row with a new primary key than just taking the maximum value and adding one?

1
0
2/3/2015 8:19:54 PM

Accepted Answer

I would recommend setting IDENTITY to On to maintain battery life. However, if you are unable to send it, here are two alternatives:

setting Datatype to UniqueIdentifier

You can assign a value to the primary key from code behind by setting the data type of your primary key to UniqueIdentifier as shown below:

var newId = Guid.NewGuid();

2. Before inserting, get the maximum ID:

Get the Max value from your primary key and assign Max Value plus 1 as your next id instead if UniqueIdentifier is not the solution, as described below:

var maxId = this.DataContext.[TableName].Max(table => table.[PK_Column]);
var newId = maxId + 1;
3
3/25/2014 9:10:23 AM

Popular Answer

Although I experienced a similar issue, I did not require a second id field. Although the client requested a number field for the contactId to appear in the application, my primary id was already a guid. It appears to work well because there is no concern about repeated numbers because we simply mark all entities as isActive = false instead of deleting them.

Entity base class

public virtual void PrepareSave(EntityState state)
{
    var identityName = Thread.CurrentPrincipal.Identity.Name;
    var now = DateTime.UtcNow;

    if (state == EntityState.Added)
    {
        CreateBy = identityName ?? "unknown";
        CreateDate = now;
    }

    LastModifedBy = identityName ?? "unknown";
    LastModifed = now;
}

I included this in the list of the sole entity in need of that condition.

public override void PrepareSave(EntityState state)
{
    var context = new DbContext();
    var maxId = context.Contacts.Max(model => model.ContactId);
    ContactId = maxId + 1;
    context.Dispose();

    var identityName = Thread.CurrentPrincipal.Identity.Name;
    var now = DateTime.UtcNow;

    if (state == EntityState.Added)
    {
        CreateBy = identityName ?? "unknown";
        CreateDate = now;
    }

    LastModifedBy = identityName ?? "unknown";
    LastModifed = now;
}

I put this to the Datacontext as well.

public override async Task<int> SaveChangesAsync()
    {
        var modifiedEntries = ChangeTracker.Entries()
            .Where(x => x.State == EntityState.Added || x.State == EntityState.Modified);

        foreach (var entry in modifiedEntries)
        {
            // try and convert to an Auditable Entity
            var entity = entry.Entity as BaseEntity;
            // call PrepareSave on the entity, telling it the state it is in
            entity?.PrepareSave(entry.State);
        }

        var result = await base.SaveChangesAsync();
        return result;
    }

This was based on SpiderCode's response that was marked as correct above. I appreciate your suggestion; it was quite helpful.



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