I recently downloaded 18.104.22.168.30 of ODAC (the version covered in this tutorial http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/EntityFrameworkOBE/EntityFrameworkOBE.htm) so that I can write a .NET MVC app against an Oracle database.
I've never done any work with Oracle DB's before and found out today that the auto increment of a key field doesn't just 'happen' like it does in a SQL Server table (yeah, I know how dumb this sounds).
There's a History table in my Oracle DB. It has a sequence on it called 's_history'. But I don't know how to make the table automatically add an incremented number via the Entity Framework. Here's some code:
Using context As New DBEntities
Dim h As New HISTORY
h.YEAR = 2011
h.MONTH = 12
If I run this then no row gets created because the HISTORY_ID column can't be NULL. I can set the HISTORY_ID colum to a valid number and the row will get created. But how do I get the Entity Framework to optimistically lock the row and generate the next available ID for me?
I ceate a sequence for each table (to hold the value of the incrementing ID) and a before insert trigger (to retrieve the new ID from the sequence and insert into your ID column)
Suggest you use SQL Developer, you can do this in two easy steps -
Right click on Sequences > Create Sequence (call it something like SEQ_TABLENAME_ID)
Create your table (which must have a numeric PK column)
Right click on the table and select Trigger > Create Trigger (PK From Sequence)
Select the sequence you created above and the ID column
Your insert should run OK now. If you get any errors, check that the triggers etc are compiled without errors (another clunky gotcha, esp when deploying via script)
Edited by: user3007186 on 16/12/2011 13:21
Thanks for responding :)
I created the Trigger as you suggested and did a few manual INSERT statements via SQL Developer to verify that it works. The rows were created with the incremented ID value.
Then I tried it via the .edmx model in the MVC app; I get this message:
ORA-00001: unique constraint (KMBC.PK_HISTORY) violated
Do I still need to set StoreGeneratedPattern to Identity on the Model even with the sequence / trigger in place? I haven't done so yet.
I've used this method( Creating a Sequence + trigger in DB, setting StoreGenatedPattern = Identity in model ) for making auto increment fields
and it works, my problem is : every time i want to update the model,
even the tiniest change regenerates the entire .edmx file and causes all the StoreGeneratedPattern="Identity"
in all entities to disappear!
You need to apply Visual Studio 2010 SP1 from Microsoft.
The SP1 was supposed to fix "StoredGeneratedPattern=Identity disappears when .edmx file is changed" issue,
but the following link indicatges it actually was not. The issue was later fixed in a hotfix.
Hi, we have the same problem in a new MVC 4 project using VS2012, EF 5 and ODP.NET for Oracle 11.
Everytime we "generate database from model", the ssdl part on edmx file loses the "storedGeneratedPatter=Identity" attribute.
Does anyone know how to workaround (not manually!) or fix the problem in VS2012?