Default Value for EntityFramework column as sequence.curval

[jongleur]

Hi.
Using EntityFramework 6 with dotConnect 8.4 for Oracle and an Oracle Express I stumble over the DDL and Code Generation that seems to target Oracle 12 and above only.

Use Case:
One of my entities has a field that should Default to the current value of an Oracle sequence.

I tried to do that as described in [1] and it Looks like it is the right Approach - for Oracle Databases starting at Oracle 12.

The DDL description of the Provider includes the Default value in the database creation Statement, which is not supported in the more old Versions.

Is it possible to get these Settings to be wrapped to a Trigger instead as it is done for identity columns as well? or is there any other way to get this great devart Feature to work with Oracle 11g Express?

thanks for your help
Peter

[1] http://blog.devart.com/set-identity-and ... ggers.html

[jongleur]

As a draft how that COULD work, I did it before by using custom Triggers.
Any default column of a table was included in an before-insert-for-each-row Trigger.

If there is any way to customize the SQL code Generation (DDL and DQL!) this would be possible to implement, bud I didn't find a template or something like that that generates the SQL commands.

[Shalex]

The SQL generation depends on the Oracle server version specified in the Provider Manifest Token property of EntityContextModel. By design, the value of Provider Manifest Token is updated if you change connection string via Database Explorer and open connection.

I have tested two servers (ProviderManifestToken="Oracle, 12.1.0.1" and ProviderManifestToken="Oracle, 11.2.0.1"): the SQL generated in both cases is the same, and it is executed successfully.

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO DEPT1(DEPTNO, DNAME, LOC)
VALUES (DEPT1s_SEQ.NEXTVAL, :p0, :p1)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT DEPTNO FROM DEPT1 WHERE ROWID = updatedRowid;
END;

Please give us the following information about the case when the generated SQL fails to execute:
1) the exact version of your Oracle server
2) the value of Profider Manifest Token in the model
3) the generated SQL itself. You can see it in dbMonitor (documentation, download link)

[jongleur]

Hi Shalex,
thank you very much for your Response, but I fear my explanations where insufficient.
I didn't "complain" about the Generation of SQL queries to retrieve data from the database, but about the ones generating the database tables.

But let's start with your questions:
(1) Exact Version of Oracle Server (according to select * from v$version):

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
INS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

(2) Value of the Provider manifest token: "Oracle, 11.2.0.2"

(3): As mentioned above I don't complain about the SQL Queries generated for INSERT/UPDATE/SELECT queries, but about the ones generated for the Schema Definition.
When I open the edml model in Entity Developer, and "Generate Database Script from Model" I get the following code generated (this is for the one table that is affected):

Code: Select all

-- Script was generated by Devart Entity Developer, Version 5.7.500.0
-- Script date 15.12.2014 09:22:56
-- Target Server: Oracle
-- Server Version: 11gR2

-- 
-- Creating a table CRITMANTEST.BOTTLENECK 
-- 
CREATE TABLE CRITMANTEST.BOTTLENECK (
   GLOBALBNID NUMBER(19) NOT NULL,
   BNID NUMBER(19) DEFAULT Bottleneck_SEQ.curval NOT NULL,
   SCID NUMBER NOT NULL,
   PLANT NUMBER(19) NOT NULL,
   BASETAKT NUMBER(19) NULL,
   SUBSECTION NUMBER(19) NULL,
   ISHARD NUMBER(1) NOT NULL,
   CREATED TIMESTAMP(6) NOT NULL,
   CREATOR VARCHAR2(32 CHAR) NOT NULL,
   MAXCOUNT NUMBER(19) NOT NULL,
   INTERVAL NUMBER(19) NOT NULL,
   "COMMENT" VARCHAR2(1000 CHAR) NULL,
   ISDENSITYCONSTR NUMBER(1) NOT NULL,
   WORKAREA NUMBER(19) NULL,
   STATION NUMBER(19) NULL,
   STARTPAT NUMBER(10) NOT NULL,
   ISACTIVE NUMBER(1) NOT NULL,
   INPRODUCTION NUMBER(1) NOT NULL,
   DBDATE TIMESTAMP(6) NOT NULL,
   DBACTION VARCHAR2(6 CHAR) NOT NULL,
   CRITERIONID CHAR(4) NOT NULL,
   ENDPAT NUMBER(10) NOT NULL,
   CONSTRAINT PK_BOTTLENECK PRIMARY KEY (GLOBALBNID)
);

-- 
-- Creating a sequence CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq" 
-- 
CREATE SEQUENCE CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq" START WITH 1 INCREMENT BY 1;

-- 
-- Creating a trigger CRITMANTEST.AI$BOTTLENECK 
-- 
CREATE OR REPLACE TRIGGER CRITMANTEST.AI$BOTTLENECK
   BEFORE INSERT ON CRITMANTEST.BOTTLENECK
   FOR EACH ROW
   BEGIN
      SELECT CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq".NEXTVAL INTO :NEW.GLOBALBNID FROM DUAL;
   END;
/

The BNID column of this table should get as a default value the current value of the Bottleneck_SEQ sequence (which is just another sequence, that could be shared by multiple tables or, as in my case, only be used when there is no other value given, thus it's the DEFAULT, not the generated value).

Please don't insist on this being not useful or something like that, in fact it is useful as I trick EF to push new rows into the table instead of updating existing ones whenever an entity is changed. Nevertheless that's not relevant for this question.

My Problem is the Generation of the table with that column Definition as
"BNID NUMBER(19) DEFAULT Bottleneck_SEQ.curval NOT NULL", because Oracle starts to support sequence values as default values in the create table statement afaik with Version 12, thus this script, which states to be generated for target Version 11gR2 is not valid for that Server Version.

A valid script instead should omit the default value in the CREATE TABLE and instead set the default value in the Trigger, like:

Code: Select all

CREATE OR REPLACE TRIGGER "CRITMANTEST.AI$BOTTLENECK"
  BEFORE INSERT ON CRITMANTEST.BOTTLENECK
  FOR EACH ROW
  BEGIN
      SELECT CRITMANTEST."AI$BOTTLENECK_GLOBALBNID_seq".NEXTVAL INTO :NEW.GLOBALBNID FROM DUAL;
      IF :NEW.BNID IS NULL
  	 THEN select BOTTLENECK_SEQ.curval into :new.bnid from dual;
      END IF; 
  END;

The existing solution IMHO is the better way for Oracle Version 12 and above, but it's invalid for older Versions.

As an additional note: for constant default values it is possible to set them as default in earlier Versions as well, thus the Generation strategy works for the default values as supported by Standard edmx models, but not with the Extended Devart-Default values using functions, sequences or similar.

regards
Peter

[Shalex]

Value of the Provider manifest token: "Oracle, 11.2.0.2" [...]
My Problem is the Generation of the table with that column Definition as
"BNID NUMBER(19) DEFAULT Bottleneck_SEQ.curval NOT NULL" [...]
A valid script instead should omit the default value in the CREATE TABLE and instead set the default value in the Trigger

We have reproduced an incorrect DDL generation with your scenario where Database-First (predefined Bottleneck_SEQ) and Model-First (creating BOTTLENECK table) approaches are mixed up.

Possible ways to solve the issue with BNID using a current implementation of Entity Developer:

1. If you are going to work with your table only via Entity Framework functionality of dotConnect for Oracle:
a) clear the default value of the BNID property
b) generate database script and create a table
c) set the StoreGeneratedPattern="Identity" devart:DefaultValue="Bottleneck_SEQ.curval" attributes for the BNID property
Now the DML queries for the BOTTLENECK table should be generated correctly.

2. Mark the BNID property with the StoreGeneratedPattern="Identity" attribute. As a result, Generate Database Script will produce new sequence/trigger. Modify manually the script: remove Devart's sequence, update the trigger with your predefined sequence.

Is any of these approaches suitable for your scenario?

[jongleur]

Hi Shalex,
thanks again for the answer.

About your ideas how to solve the issue:
(1) Nearly a solution. Drawback left: As I would like to use DropDatabase() and CreateDatabase() (one initial Motivation for switching to DevArts Provider, as the Oracle one does not Support these two functions), generating the database via script is a showstopper for this Feature.
As I would like to use this in a automatted test Environment recreating the database Schema, this would be nice to have.
Or is it possible to set the devart:defaultValue-Property by code in the generated classes?
(2) Same as above: involves Manual creation of the script.

Thanks nevertheless, for your help,
It would be great to get an update when there is a solution available, up to then I'll try to hack around it - e.g. by one of your approaches.

regards
Peter

[jongleur]

Hi again,

Found a solution that "works for now" by deleting my views from the database and add "them" as "defining queries" in the edml model.

Unfortunately this does not generate a view, but stores the query in the model to be executed every time the DbSet is queried. See my followup question here

regards
Peter

[Shalex]

As I would like to use DropDatabase() and CreateDatabase() (one initial Motivation for switching to DevArts Provider, as the Oracle one does not Support these two functions), generating the database via script is a showstopper for this Feature.

Try using Code-First Migrations. This feature allows to add an Sql operation to the migration manually where you can run the needed DDL (create your trigger, materialized view, etc).
So the most of your objects would use default migrations, but custom views would be implemented via Sql operation.