Primary key constraint violation when adding new row into table with EF

Question

We're getting a very intermittent (like every few weeks someone complains about this) primary key constraint violation in our web app. I've searched the code base, and the only code that even creates any rows in this table is as follows:

decimal nextDocId = (from d in context.TPM_PROJECTVERSIONDOCS
                     orderby d.DOCUMENTID descending
                     select d.DOCUMENTID).Max() + 1;

foreach (TPM_PROJECTVERSIONDOCS doc in documents)
{
   TPM_PROJECTVERSIONDOCS newDoc = new TPM_PROJECTVERSIONDOCS();
   newDoc.DOCUMENTID = nextDocId;
   newDoc.DOCBLOB = doc.DOCBLOB;
   newDoc.DOCUMENTNAME = doc.DOCUMENTNAME;
   newDoc.FILECONTENTTYPE = doc.FILECONTENTTYPE;
   version.TPM_PROJECTVERSIONDOCS.Add(newDoc);
   nextDocId++;
}

The error we get is:

ORA-00001: unique constraint (TPMDBO.TPM_PROJECTVERSIONDOCS_PK) violated

This means that DOCUMENTID is already in use. I have a few theories as to what causes this. First, if multiple people were adding documents at the same time, somewhere between the time when nextDocId was set and the time the context was saved, new documents may have been added to the database. However, this time would only be a few milliseconds so I think this is unlikely with the small amount of traffic our site gets.

My second theory is perhaps EF does some sort of caching, and nextDocId is returning a cached value that is no longer valid.

Since this error only happens every so often, and of course only on our production servers, I have no good ways to debug into it or repro.

My question: What's the most likely cause of this, and is there a better way to re-write this code to prevent primary key violations? I'd love to use just an auto-incrementing field for the primary key, but unfortunately Oracle doesn't support them. Switching to a UUID would also be a solution, but would result in a lot of DB changes. Thanks!

UPDATE:

Here's the TPM_PROJECTVERSIONDOCS entity:

<EntityType Name="TPM_PROJECTVERSIONDOCS">
   <Key>
      <PropertyRef Name="DOCUMENTID" />
   </Key>
   <Property Name="DOCUMENTID" Type="decimal" Nullable="false" />
   <Property Name="PROJECTID" Type="decimal" Nullable="false" />
   <Property Name="VERSIONID" Type="decimal" Nullable="false" />
   <Property Name="DOCUMENTNAME" Type="VARCHAR2" Nullable="false" MaxLength="500" />
   <Property Name="DOCBLOB" Type="BLOB" Nullable="false" />
   <Property Name="FILECONTENTTYPE" Type="VARCHAR2" Nullable="false" MaxLength="80" />
</EntityType>

I'm unaware of any way to either make DOCUMENTID default to a sequence, or to query a sequence using the EF.

Gooseneck

Answer 1

Since you are using Oracle, you should be using an oracle sequence value. It will not return a duplicate! Calling the sequence.nextval instead of your max()+1 will solve it.

Freeway

Yea, I had that idea too but after much research I could not find a way to get Entity Framework to use a sequence for a field value. – Gooseneck

@MikeChristensen: couldn't you have a small stored proc to retrieve the next value from a sequence, and just call that from your code, in your process of inserting the row? – Mucro

Marking this as the best answer since using a sequence is for sure the best approach. However, I posted my implementation as an answer as well to help out others. – Gooseneck

Answer 2

(Just posting for future readers)

I believe I have a decent way to solve this problem. I'm not sure it's the absolute best way, but I'll go out on a limb and say it's far superior than what was there before. Here's what I did.

First, I created a new sequence:

CREATE SEQUENCE TPM_PROJECTVERSIONDOCS_PK_SEQ
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

When I port this over to production, I'll start after the current MAX rather than 1; however my test DB has no rows in this table.

Next, I created an extension method on TPMEntities (my entity context):

public static class EntityUtil
{
   public enum Sequence
   {
      TPM_PROJECTVERSIONDOCS_PK_SEQ
   };

   public static decimal GetNextSequence(this TPMEntities context, Sequence sequence)
   {
      string sql = String.Format("select {0}.nextval from dual", sequence.ToString());
      var testId = context.ExecuteStoreQuery<decimal>(sql);

      return testId.First();
   }
}

I decided to use an enum for each sequence (I only have one right now, but I'll have others as I port legacy code over) rather than a string to ensure the sequence is valid and for some Intellisense help.

Next, I replaced:

newDoc.DOCUMENTID = nextDocId;

With:

newDoc.DOCUMENTID = context.GetNextSequence(EntityUtil.Sequence.TPM_PROJECTVERSIONDOCS_PK_SEQ);

So far, this seems to work pretty well.

Gooseneck

Nice followup. Thanks for posting your implementation. – Freeway

Answer 3

Add .ValueGeneratedOnAdd().UseHiLo with Oracle sequence to method OnModelCreating. Ef core with Oracle sequence will take care of Uniqueness:

speechToTextEvent.Property(e => e.Id)
.ValueGeneratedOnAdd()    
.UseHiLo("SPEECH_TO_TEXT_EVENTS_SEQUENCE")
.HasColumnName("ID")
.IsRequired();

Puryear