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

.net entity-framework oracle11g sequence nextval
iCanObjSeeSharp picture iCanObjSeeSharp · Aug 15, 2013 · Viewed 16.4k times · Source

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();

Answer

jozolo picture jozolo · Dec 3, 2014

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.

Related questions

MetadataException: Unable to load the specified metadata resource

All of a sudden I keep getting a MetadataException on instantiating my generated ObjectContext class. The connection string in App.Config looks correct - hasn't changed since last it worked - and I've tried regenerating a new model (edmx-file) from …

Read More
Unable to update the EntitySet - because it has a DefiningQuery and no <UpdateFunction> element exist

I am using Entity Framework 1 with .net 3.5. I am doing something simple like this: var roomDetails = context.Rooms.ToList(); foreach (var room in roomDetails) { room.LastUpdated = DateTime.Now; } I am getting this error when I try to do: context.SaveChanges(); …

Read More
How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?

Is there a way to compare two DateTime variables in Linq2Sql but to disregard the Time part. The app stores items in the DB and adds a published date. I want to keep the exact time but still be …

Read More