Oracle - Using sequences for Identity columns (Cannot Insert Null)

[nlz242]

Hello,

Just like this post : viewtopic.php?f=30&t=32292
I need help in using EF6 and Oracle Sequence object for insert/update a records with identity column.

Using Entity Developer 5.8.676
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Storage :

Code: Select all

          <Key>
            <PropertyRef Name="NO_INTERV_EXT" />
          </Key>
          <Property Name="NO_INTERV_EXT" Type="int64" Nullable="false" devart:DefaultValue="SEQ_NO_INTERV_EXT.NEXTVAL" StoreGeneratedPattern="Identity" />

Class :

Code: Select all

<Property Name="NOINTERVEXT" Type="Int64" Nullable="false" annotation:StoreGeneratedPattern="Identity" ed:ValidateRequired="true" ed:Guid="9a831fef-9385-453e-b73f-36ea2af80102" />

Mappping :

Code: Select all

<ScalarProperty Name="NOINTERVEXT" ColumnName="NO_INTERV_EXT" />

I do get a valid value when i execute this query on my database :

Code: Select all

SELECT SEQ_NO_INTERV_EXT.NEXTVAL FROM DUAL;

I made sure to configure the InsertNullBehaviour in DmlOptions to "InsertDefaultOrNull" like so :

Code: Select all

                var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
                config.DmlOptions.InsertNullBehaviour = Devart.Data.Oracle.Entity.Configuration.InsertNullBehaviour.InsertDefaultOrNull;

And just before i call SaveChanges(), i verified that this option still had the proper value.

Generated code for the column, in the configuration file looks like this :

Code: Select all

            this
                .Property(p => p.NOINTERVEXT)
                    .HasColumnName(@"NO_INTERV_EXT")
                    .IsRequired()
                    .HasDatabaseGeneratedOption(System.ComponentModel.DataAnnotations.Schema.DatabaseGeneratedOption.Identity)
                    .HasColumnType("int64");

However, when i saw i get an error saying i can't insert Null in this column:

Message=ORA-01400: cannot insert NULL into ("TFP"."TFP_E_INTERV_EXT"."NO_INTERV_EXT")
ORA-06512: at line 4
Offset=0
Source=Devart.Data.Oracle
StackTrace:
à Devart.Data.Oracle.an.d()
à Devart.Data.Oracle.cq.e()
à Devart.Data.Oracle.cq.a()
à Devart.Data.Oracle.i.a(cq A_0, Int32 A_1)
à Devart.Data.Oracle.i.a(Int32 A_0, cg A_1)
à Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Data.Oracle.Entity.g.a(CommandBehavior A_0)
à Devart.Data.Oracle.Entity.g.b(CommandBehavior A_0)
à System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
à System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
à System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.Execute(Dictionary`2 identifierValues, List`1 generatedValues)
à System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.Update()

I tried using DbMonitor to see the query, it sees my process but doesn't show any queries. I'm not sure what is up with that. I did add the reference to my main exe file and create an instance of OracleMonitor on which i set to IsActive = true. It only catches the connection creation but not usage (weird).

EDIT: Got DbMonitor to work, here's my query:

Code: Select all

DECLARE
  updatedRowid ROWID;
BEGIN
INSERT INTO TFP_E_INTERV_EXT(NOM, PRENOM, NOM_RECH, PRENOM_RECH, GENRE_INTERV_EXT, NO_PROP_SYST_EVAL, NO_TEL_RESID, NO_TEL_BUR, AUT_REF, DATE_CREAT, UTIL_CREAT, DATE_MODIF, CODE_UTIL_MODIF, STATUT, NO_CLIENT_SYST_AUTRE, NOM_FORM, NO_TELECOPIE, ADR_ELECTRNQ, IND_LANG_FACT_RECU, IND_INTERURBAIN, MEDIA_PRIV_LET_NOT, AU_SOIN_DE, ANC_NO_PROP_SYST_EVAL, IND_EXCL_ROLE, IND_FUSIONNABLE, NO_INST_FINAN, NO_SUCC_INST_FINAN, ORG_APPARENTE)
VALUES (:p0, :p1, :p2, :p3, NULL, NULL, NULL, NULL, NULL, :p4, :p5, :p6, :p7, :p8, NULL, :p9, NULL, NULL, :p10, :p11, :p12, NULL, NULL, :p13, :p14, NULL, NULL, NULL)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT NO_INTERV_EXT FROM TFP_E_INTERV_EXT WHERE ROWID = updatedRowid;
END;

So yeah, i'm stuck without any ideas of why it's not using the sequence.
Any help would be appreciated!

EDIT 2 :
If i add a Before Insert trigger, it works well. I was hoping i wouldn't have to add triggers tho...

[Shalex]

You are using dotConnect for Oracle v8.5.478, aren't you?

Please set a break point in your code, run the project, navigate to Debug > Windows > Modules, order the list by name / reorder columns, and make sure that the only Devart.* assemblies loaded in the process of your application are:

• Devart.Data.dll v5.0.1270.0
• Devart.Data.Oracle.dll v8.5.478.0
• Devart.Data.Oracle.Entity.dll v8.5.478.6

JIC: the described behaviour is possible if you didn't update (or set incorrectly) the reference to Devart.Data.Oracle.Entity.dll in your project. As a result, the InsertNullBehaviour setting is set for one version of Devart.Data.Oracle.Entity.dll, but another version of Devart.Data.Oracle.Entity.dll is actually used in runtime. If so, try these steps:
1) remove and readd reference to Devart.Data.Oracle.Entity.dll via the References node of your project via Solution Explorer
2) check your app.config and make sure that you correctly updated the version of Devart.Data.Oracle.Entity.dll (e.g.: 8.4.457.6 -> 8.5.478.6)

[nlz242]

Loaded modules are exactly those you list:
Devart.Data.dll v5.0.1270.0
Devart.Data.Oracle.dll v8.5.478.0
Devart.Data.Oracle.Entity.dll v8.5.478.6

No other Devart.* module loaded.

Just to make sure, i double checked my different Config files and they are all on the proper versions.

We are using Fluent mapping, could this be the culprit? I remember Fluent Mapping not having ways to set Default Values... Not sure if it is still true.

[Shalex]

We are using Fluent mapping, could this be the culprit? I remember Fluent Mapping not having ways to set Default Values... Not sure if it is still true.

That is a reason of the issue.

[nlz242]

Just to confirm the theory, i changed Fluent Mapping to false and made sure to provide an EntityConnection instead of a DbConnection and as expected, it works now.
Pregenerated views (which never worked for me with Fluent Mapping) also work now... In the past it would always throw exception on Model not matching Pregenerated views, now it works.

So i guess i'll stick with ModelFirst instead of going CodeFirst. My CodeFirst was generated from a Database anyways, so not *really* CodeFirst.

Thanks