1

I'm currently using Entity Framework inside an MVC3 application to connect to an Oracle Database. I want to write a new record to a table in the Oracle database which includes a sequence that defines it's primary key. Of course, the primary key is defined to be not null. All my views are written in ASP.Net instead of Razor.

I see that most posts out there recommend setting a StoreGeneratedPattern directly on the edmx model. However, I notice that most edmx model examples like this one: Oracle entity in VS entity framework doesnt update the primary key in code include the tables defined using EntityTypes -the table in question I want to do this on is actually encompassed in an EntitySet:

<EntitySet Name="REGISTERED_PERSON" EntityType="Model.Store.REGISTERED_PERSON" store:Type="Tables" Schema="MYSCHEMA" />

and when I try to set StoreGeneratedPattern=Identity on this, I get an error that the StoreGeneratedPattern attribute is not allowed, presumably because it is not an EntityType. I had originally thought about just getting the max. registered_person_id from the database via LINQ statement and then assigning that into the model, but then the problem arises that you'd get a duplicate ID if two inserts were executed at the same time.

I also checked the database and I don't have any triggers written. I know that in Oracle you can opt to select from dual to get the next value which is generated by the sequence, such as illustrated below:
select MY_SEQ.nextval,'some value' from dual

This post here: postgresql nextval question on sequences has a great title, but the problem with that is that I'm not working with postgresql or PHP, so unfortunately it doesn't help me. I also don't have any triggers written to fire the sequence upon insert of a record.

How do I go about getting the nextvalue from the sequence and assign it to my Registered_Person_Id, preferably in the view before the entire model object gets passed back to the controller to insert in the database?

Community
  • 1
  • 1
silvenwolf
  • 70
  • 2
  • 11

1 Answers1

3

I have found that you do need both a trigger and sequence to make this work in Oracle. Here is the trigger pattern that I use in production:

CREATE OR REPLACE TRIGGER MYSCHEMA.TRIGGER_NAME 
BEFORE INSERT ON MYSCHEMA.REGISTERED_PERSON
FOR EACH ROW
WHEN ( NEW.REGISTERED_PERSON_ID IS NULL )
BEGIN
  SELECT MY_SEQUENCE.NEXTVAL INTO :NEW.REGISTERED_PERSON_ID FROM dual;
END;
Rob H
  • 348
  • 2
  • 5