This discussion is archived
6 Replies Latest reply: Dec 11, 2012 1:56 AM by 979198 RSS

Oracle 11g ODAC Entity Framework - how to auto increment a number

905813 Newbie
Currently Being Moderated
Hello,

I recently downloaded 11.2.0.2.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
context.HISTORies.Add(h)
context.SaveChanges()
End Using

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?
  • 1. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    905913 Newbie
    Currently Being Moderated
    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
  • 2. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    905813 Newbie
    Currently Being Moderated
    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.
  • 3. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    kakiyama Newbie
    Currently Being Moderated
    Yes, StoreGeneratedPattern should be set appropriately so that EF can
    1) avoid sending values to the server that will no be used
    and
    2) return the server generated value to the client
  • 4. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    884940 Newbie
    Currently Being Moderated
    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!
    Any ideas?
  • 5. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    15208 Explorer
    Currently Being Moderated
    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.

    http://connect.microsoft.com/VisualStudio/feedback/details/611665/storegeneratedpattern-does-not-saved-properly-into-edmx-entity-framework-4-0
  • 6. Re: Oracle 11g ODAC Entity Framework - how to auto increment a number
    979198 Newbie
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points