How to get Oracle next sequence value in .NET Entity Framework?

.net entity-framework nextval oracle11g sequence

Question

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();
1
7
8/15/2013 8:18:52 PM

Popular Answer

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.

  1. Create the sequence (you've already done this) on the identity column.

  2. Create a trigger to automatically call the sequence on insert. http://www.oracle-base.com/articles/misc/autonumber-and-identity.php

  3. 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

    StoreGeneratedPattern="Identity"

Oracle entity in VS entity framework doesnt update the primary key in code

  1. But I didn't like the fact that I had to re-add this change every time I refreshed my EF model from the database. Double-click the .edmx file, then locate your table in the database diagram, highlight the identity column in the diagram, and in the properties window, change the StoreGeneratedPattern value to Identity. That should make it persist even when you update your EF model.
5
5/23/2017 11:53:56 AM


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