Oracle auto identity insert problem

[susahin80]

I use attribute mapping like this:

      [DatabaseColumn("ID")]
      [DatabasePrimaryKey]
      public int Id { get; set; }

The Id field maps to the ID field which is identity column.( ID NUMBER Generated as Identity ).

I see no attribute for specifying an identity column. It seems I can use [DatabaseIgnore] attribute, but I want to get the value of ID in read operations, if I use [DatabaseIgnore] I can't access the Id value since it is ignored.

So is there a way to resolve this problem? It would be good to have a Identity attribute which supports identity columns.

[mikebeaton]

Hi @susahin80,

The default Mighty approach, adopted from Massive, should be to treat items specified as being primary keys as identity values, in the sense you need, if the table is modified using the CRUD actions. Are they what you are using?

You are right that the new Mighty DatabasePrimaryKey attribute you are using should already be setting the column to be a primary key in the relevant sense.

[susahin80]

Hi @mikebeaton, thanks for the answer. Actually I never used Massive library.

I came across with MightyOrm when I wanted to try Dapper alternatives.

I use the Insert method like this:

            var db = new MightyOrm<Summary>(oracleConnection);

            Summary summary = new Summary { Name = "Name" };

            db.Insert(db.New(summary, true));

The exception is: System.ArgumentException: 'Invalid parameter binding Parametre adı: ID'.

Id field in Summary class is like this:

        [DatabaseColumn("ID")]
        [DatabasePrimaryKey]
        public int Id { get; set; }

When I add [DatabaseIgnore] attribute, the Insert method works as expected, but this will also cause the Get method ignore the ID field value in read operations which is not desirable.

        [DatabaseColumn("ID")]
        [DatabasePrimaryKey]
        [DatabaseIgnore]
        public int Id { get; set; }

[mikebeaton]

Hi - let me have a look some more, I definitely don't immediately see anything you are doing wrong

[susahin80]

In PetaPoco I see they handle using AutoIncrement property.

    [TableName("People")]
    [PrimaryKey("Id", AutoIncrement = true)]
    public class Person
    {
        [Column]
        public Guid Id { get; set; }

But interestingly, PetaPoco inserts the record to the database table, but also giving exception in code which is misleading.

[mikebeaton]

Hi @susahin80 - I have realised I may have not answered you with the obvious: as supported in Massive, the correct way to identify the primary keys of a table are to pass in the key column name (or names, in the atypical case of a multi-column primary key) into the primaryKeys parameter of the MightyOrm constructor. I believe that is all you need to do! Can you try that and let me know? Thanks!

[mikebeaton]

Though using PrimaryKeyAttribute [EDIT: DatabasePrimaryKeyAttribute] should, indeed, also work. But let me know about the above.

[susahin80]

Hi @susahin80 - I have realised I may have not answered you with the obvious: as supported in Massive, the correct way to identify the primary keys of a table are to pass in the key column name (or names, in the atypical case of a multi-column primary key) into the primaryKeys parameter of the MightyOrm constructor. I believe that is all you need to do! Can you try that and let me know? Thanks!

@mikebeaton thanks for the info, I will share the result when I try.

[susahin80]

Though using PrimaryKeyAttribute should, indeed, also work. But let me know about the above.

@mikebeaton do you mean [DatabasePrimaryKey] attribute? It was the first thing I tried, it gave System.ArgumentException: 'Invalid parameter binding error for oracle identity column (the oracle itself manages that )

[mikebeaton]

Sorry - getting there slowly - you need to specify the sequence parameter in the constructor.

I do not use Oracle day-to-day, and it is long enough since I looked at this that I did not immediately remember - apologies!

That should be all that you needed, all along.

The DatabasePrimaryKey attribute and the primaryKeys constructor parameter should indeed have equivalent results, and you do not need both - but with either of them, specifically on sequence-based DBs - including Oracle - you also need to specify the sequence name using the sequence constructor parameter - in order to use identity-based CRUD features.

That should be all of it - sorry it took me so long to get you the answer!

[mikebeaton]

Though closed - would appreciate confirmation that all is okay once using the above, if you have the time to - and will check back! And, of course, will re-open if all still not okay! Thanks! :)

[mikebeaton]

Oh by the way, @susahin80, for your interest (perhaps?! 😉 ):

The reason Mighty needs to know the sequence name is because of this:

https://medium.com/@mikebeaton/another-answer-to-that-annoying-insert-problem-224cd1114eec

[susahin80]

@mikebeaton actually the Identity column in oracle automatically generates a sequence under the hood with a random name.

For example, with the following ID column definition:
ID NUMBER Generated as Identity

Oracle generates a sequence like this:
-- Sequence ISEQ$$_1198866 is created automatically by Oracle for use with an Identity column

And in Toad I see the default value is "ISEQ$$_1198866".nextval for this ID column.

Since the sequence name is automatically generated, it is not good to use this seq name in the constructor (since the seq name will be different in different environments.)

Giving this info, I also tried to specify this sequence name in the constructor like this:

           var db = new MightyOrm<Summary>(oracleConnection, sequence: "ISEQ$$_1198866");

            Summary summary = new Summary();

            db.Insert(summary);

This gives "ORA-00957: duplicate column name.." error.

I think to handle oracle Idendity column feature, some additional work may be needed to specify that a property maps to the Oracle Identity column, and that columns must not be added in the Insert statement.