ENTITY FRAMEWORK, ORACLE, INDENTITY/SEQUENCE FOR INSERTS

Unlike SQL Server, Oracle does not have the concept of Identity columns. Instead Oracle has concept of sequences. While working with current Oracle Provider for Entity Frameworks, there is no support for specifying these sequences declaratively in the designer. You will definitely have need for inserts in your application.

While performing inserts using EF, we used Oracle Client Library and we wrote functions like

Code Snippet
  1. private int GetNextPatientDocumentSequence()
  2. {
  3.     int result = 0;
  4.  
  5.     string sqlQuery = @"select  S_DOC_ID.nextval from dual";
  6.  
  7.     using (OracleConnection con = new OracleConnection(OracleConnectionString))
  8.     {
  9.         con.Open();
  10.         OracleCommand cmd = con.CreateCommand();
  11.         cmd.CommandText = sqlQuery;
  12.         object data = cmd.ExecuteScalar();
  13.         string str = data.ToString();
  14.         result = int.Parse(str);
  15.         //con.Close();
  16.     }
  17.     return result;
  18. }

 

We thought about changing the structure, i.e adding a TRIGGER for inserts and then use the EF designer to specify the id generation declaratively. Finally decided against it (for obvious design reasons).

SQL SERVER 2012 AND SEQUENCE

SQL Server 2012 introduce the concept of the SEQUENCE. If you want to write your ORM layer which supports two (Oracle and SQL Server) of the major RDBMS vendors, you should consider using this feature.

2 thoughts on “ENTITY FRAMEWORK, ORACLE, INDENTITY/SEQUENCE FOR INSERTS

  1. Cody Baker says:

    Hey Shanthu! I just got off a contract gig that was my first Oracle exposure. There were some nice features, but sequences were kinda annoying. Hat tip to MSFT for coming up with a better way to handle that!

    • Kind of arrogant to call it a “better” way. Sequences are not tied down to one table/column like an identity. This is very useful if you have an ID which is spread across several different tables for example if you have “Employee” and “Customer” but each had a column called RelatedPersonID or something. Also, Identities add significant complexity when bulk loading data for which values have already been assigned (SET IDENTITY_INSERT OFF) etc. And, if sequences were so bad, why did MSFT decide to add them to SQL 2012?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: