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

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

Question

My table primary key is set to be [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]

I want to copy records into a new row with a new primary key, is there a best way to do this other than taking the max value and adding 1

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

Accepted Answer

I would suggest to set IDENTITY to On for batter maintainability. But if you cant sent it then there are two suggestions :

1. set Datatype as UniqueIdentifier :

You can set the data type of your primary key to UniqueIdentifier and assign value to the primary key from code behind as mentioned below :

var newId = Guid.NewGuid();

2. Get Max ID before insertion:

If UniqueIdentifier is not the solution then get Max value from your primary key and set Max Value + 1 as your next id as mentioned 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

I had a similar problem but i did not need a second id field. My primary id was already a guid but the client wanted a number field for the contactId to display in the application. Seems to work great because we do not delete any entities just mark them as isActive = false so no fear of repeating numbers.

Base Entity 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 added this to the single entity that need that requirement

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;
}

In the Datacontext I also added this

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 the answer marked as correct above SpiderCode Thanks for the suggestion worked great for me ...



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