How to automatically generate identity for an Oracle database through Entity framework?

Question

I'm using Oracle provider for Entity framework (beta), and I'm facing a problem.

Our tables have Id columns, which are set to be Identity in StoreGeneratedPattern. I thought that EF will automatically do "underlying works", such as create sequences, and get new identity for each record I add to the table. But when I run code to add a new record, such as:

var comment = new Comment
{
    ComplaintId = _currentComplaintId,
    Content = CommentContent.Text,
    CreatedBy = CurrentUser.UserID,
    CreatedDate = DateTime.Now
};

context.Comments.AddObject(comment);
context.SaveChanges();

an Exception still throws, which is

{"ORA-00001: unique constraint (ADMINMGR.CONSTRAINT_COMMENT) violated"}

(CONSTRAINT_COMMENT is the constrain requires that comment identity must be unique.

How do I solve this?

Thank you very much!

http://stackoverflow.com/questions/8232997/generate-identity-for-an-oracle-database-through-entity-framework-using-an-exisi/19684494#19684494 — Lawren Alex, Oct 30 '13 at 14:09

score 17 · Accepted Answer · edited Jun 07 '12 at 11:50

17

StoreGeneratedPattern="Identity" simply tells EF that the value will be generated DB-side on insert, and that it shouldn't supply a value in insert statements.

You still need to create a sequence in Oracle:

create sequence ComplaintIdSequence minvalue 1 maxvalue 9999999 start with 1 increment by 1;

and a trigger to make table inserts use it:

create or replace trigger CommplaintIdTrigger  
before insert on comment for each row 
begin 
  if :new.ComplaintId is null then select ComplaintIdSequence.nextval into :new.ComplaintId from dual; 
  endif; 
end;

edited Jun 07 '12 at 11:50

alextansc

4,626
6
29
45

answered Mar 08 '11 at 03:52

KristoferA

12,287
1
40
62

1

Thank you for your answer, but I still can't believe that Oracle just did not do this automatically :( – Delta76 Mar 08 '11 at 03:55
Oracle should absolutely *not* do that stuff automatically. (Because exactly what it *can* do is more flexible than you think.) It's your framework's job to understand (and hide) the differences between database platforms. – Mike Sherrill 'Cat Recall' Mar 08 '11 at 12:18
2

I didn't said that Oracle should do it in its database, but it should definitely do it in its provider (the "framework" in your comment) – Delta76 Mar 09 '11 at 01:32
2

In Model-First scenarios I agree: it would be a reasonable expectation that EF/the provider would generate SQL-DDL for sequences+triggers if a table has a int (or other numerical) primary key with storegeneratedpattern=identity... – KristoferA Mar 10 '11 at 06:46
I agree that EF should abstract this but in other hand is a shame that a sgdb can't simply increase a value. Even MS Access do that. You can suggest it here: http://data.uservoice.com/forums/72025-ado-net-entity-framework-ef-feature-suggestions – Davi Fiamenghi Mar 23 '12 at 20:57

score 1 · Answer 2 · answered Feb 04 '17 at 10:45

1

Oracle 12c has resolved it

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int SomeNumber { get; set; }

answered Feb 04 '17 at 10:45

Toolkit

10,779
8
59
68

score 0 · Answer 3 · answered Feb 20 '14 at 17:55

Another option would be:

Create a sequence the way Alextansc described. Create a stored procedure that uses MySequence.nextval as it's primary key.

Map 'insert' for this model to your stored procedure and it works!

I've tested this using database first approach.

Using database first mapping to a stored procedure is pretty simple. Go to your edmx file and right click the model you want to map to a stored procedure. Click "stored procedure mappings." The dialog at the bottom of the page gives you three drop down menus for mapping insert, update, and delete to stored procedures.

score 0 · Answer 4 · answered Oct 22 '15 at 02:33

I am using Oracle ODP.NET, Managed driver and Entity Framework 6. I created my tables using the code-first approach but wasn't able to add any records due to a null primary key.

The solution was to grant my user both:
'CREATE SEQUENCE' and
'CREATE TRIGGER'
permissions and re-create the schema.

I realized this after using the -verbose flag in the package management console

score 0 · Answer 5 · answered Dec 23 '15 at 15:13

Instead of remember all of this SQL, you could easily do by using Mig# like this:

        var schema = new DbSchema(ConnectionString, DbPlatform.Oracle12c);
        schema.Alter(db => db.CreateTable("TableName")
            .WithPrimaryKeyColumn("Id", DbType.Int32).AsIdentity()
            ...);

In this example, the Id column will have the required trigger and sequence generated by Mig# automatically.

How to automatically generate identity for an Oracle database through Entity framework?

5 Answers5

Linked

Related