CODEWITHSUNDEEP
oracle-databaseasp.net-coreoracle11gentity-framework-core
Bamdad
Bamdad

Reputation: 838

Cannot insert NULL into table

I'm using EF Core to connect to an Oracle11g database (using the Oracle.EntityFrameworkCore v2.19.90 provider). It's a code first scenario, the tables are created successfully and everything is as expected.

The problem is, when I try to insert something into the database, for example:

_context.Roles.Add(new ApplicationRole()
                       {
                           Name = "FOO",
                           DisplayName = "Foo"
                       });
_context.SaveChanges();

I get an error:

OracleException: ORA-01400: cannot insert NULL into ("SCHEMA"."AppRole"."Id")

The column Id is indeed non-nullable. When I use the SQL Server provider, everything is fine, the SQL Server automatically chooses an id for my entity.

Is there any way to get Oracle to set an Id for me? Or could it be done in another way?

I don't want to use Oracle triggers and the solution should be full code first.

Upvotes: 0

Views: 1956

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

One option may be usage of SEQUENCE and default value:

CREATE TABLE AppRole(
   Id INT   NOT NULL, 
   Name VARCHAR2(100), 
   DisplayName VARCHAR2(100)
);

CREATE SEQUENCE seq;

ALTER TABLE AppRole MODIFY Id DEFAULT seq.NEXTVAL;

INSERT INTO AppRole(Name, DisplayName) VALUES ('Foo','Foo');

db<>fiddle demo

Default with sequence is supported from Oracle 12c.

There should exist syntax in EntityFramework core that allow to do the following without relying on triggers(raw SQL query as last resort):

INSERT INTO AppRole(Id, Name, DisplayName) VALUES (seq.NextVal, 'Foo','Foo');

Sequences

Basic usage

You can set up a sequence in the model, and then use it to generate values for properties: C#

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<int>("OrderNumbers");

    modelBuilder.Entity<Order>()
        .Property(o => o.OrderNo)
        .HasDefaultValueSql("NEXT VALUE FOR shared.OrderNumbers");
}

Note that the specific SQL used to generate a value from a sequence is database-specific; the above example works on SQL Server but will fail on other databases. Consult your specific database's documentation for more information.

Oracle syntax is sequence_name.NEXTVAL.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142778

As you're on Oracle 11g, then you have to use a trigger along with a sequence which will populate ID column in the background.

Another option is to, obviously, provide ID value during insert.

If you were on 12c or above, you could have used identity column. As you're not, your options are listed above.

Upvotes: 1

Related Questions