CODEWITHSUNDEEP
c#sql-serverasp.net-mvcentity-framework
dbar
dbar

Reputation: 41

Entity Framework: Handling primary key unique values (Identity specification turned off) for new record

First, I can't modify the design of the table to turn on identity specification for the primary key.

My question is how to handle in EF where user A and user B will have no conflict in primary key when adding a new record.

I'm working on Asp.Net MVC5 project.

My current code:

public void NewFooMember(FooMember fooMember)
{
    fooMember.NullGuard("fooMember");
    // This is the primary key
    fooMember.MemberKey = NextFooMemberKey();
    Repo.Add(fooMember);
    UnitOfWork.Commit();
}

public int NextFooMemberKey()
{
    int? maxFooMemberKey = Repo.Data.Max(fm => (int?)fm.MemberKey);
    if (!maxFooMemberKey.HasValue)
    {
        return 1;
    }
    return maxFooMemberKey.Value + 1;
}

Upvotes: 1

Views: 444

Answers (1)

Yaakov Ellis
Yaakov Ellis

Reputation: 41490

Your current idea has the flaw that two different calls could be made to NextFooMemberKey at the same time, before either inserts a new records, resulting in two records being inserted into the Db with the same key.

To address this, you should be sure to make this sequence thread-safe, and add lock calls around the key generation and insert functionality to ensure that only one thread can run that section of code at a time (ensuring unique keys).

You could also do with some optimization on the key generation function - assuming that you aren't dealing with multiple servers, you can cache the last key and just increment it each time - that way you don't have to round-trip to the database each time that you are generating a key (you would need to address the scenario where an insert fails, in which case you would need to decrement your static saved key).

private object newFooLock = new object();

public void NewFooMember(FooMember fooMember)
{
  fooMember.NullGuard("fooMember");
  lock (newFooLock) {        // now only one thread can get in here at a time
    try 
    {
      fooMember.MemberKey = NextFooMemberKey();
      Repo.Add(fooMember);
      UnitOfWork.Commit();
    }
    catch (Exception ex)
    {
      lastFooKey--;    // decrement saved value
    }
  }
}

private object newFooKeyLock = new object();
private static int? lastFooKey = null;

public int NextFooMemberKey()
{ 
  // also lock the key generation, just to be safe
  lock (newFooKeyLock) { 
    if (lastFooKey == null) 
    {
      // only get from the db if the local value is not yet populated
      lastFooKey = Repo.Data.Max(fm => (int?)fm.MemberKey) ?? 1;
    }
    lastFooKey++;
    return lastFooKey.Value;
  }
}

Upvotes: 2

Related Questions