I have a web api post method that inserts a new row in my table in my Oracle database. I'm having trouble with the primary key of the table which is a sequence value. How do I do
my_primary_key_seq.nextval in Entity Framework? Currently this code works but it will violate PK unique constrain when a new row is inserted via our old .net webform which uses the sequence next value as the next primary key.
decimal nextPK = context.FORMPPs.OrderByDescending(p => p.PPID).FirstOrDefault().PPID + 1; item.PPID = nextPK; context.FORMPPs.Add(item); int result = context.SaveChanges();
I had this same issue, and resolved it with some help from this site and Oracle. I'm assuming you're using Database First, since you mentioned another legacy app uses the same database.
There are a few things I had to do. Like Daniel Gabriel mentioned, if you allow Oracle to manage the identity, you don't need to ever call the sequence to find out the number, the database takes care of it for you. But getting that to work was a little tricky because you may need to make a bunch of changes to the database.
Create the sequence (you've already done this) on the identity column.
Create a trigger to automatically call the sequence on insert. http://www.oracle-base.com/articles/misc/autonumber-and-identity.php
Alter your Entity Framework model. I was using EF Database First, and found this article that explained I needed to alter the model to set the property of the table's identity column to