.HasDefaultValueSql is not working

[df5]

Both of these options do not execute, it actually prevents the INSERT statement from happening at all.

entity.Property(e => e.BlockId).HasDefaultValueSql("SELECT DATA_BLOCK_SEQ.NEXTVAL FROM DUAL"); //does not work!
entity.Property(e => e.BlockId).ForOracleHasDefaultValueSql("SELECT DATA_BLOCK_SEQ.NEXTVAL FROM DUAL"); //does not work!

I am on Devart.Data.Oracle.EFCore version 9.6.646

Please advise or have a fix for it.

Thanks.

[df5]

Additional Info:

The reason why I wanted to use the HasDefaultValueSql() is because currently when the HasDefaultValue() option is used along with a trigger to generate the Id from a sequence the SQL generated is:

DECLARE
updatedRowid ROWID;
BEGIN
INSERT INTO ABISS.DATA_VERSION (ACTIVITY_TYPE_ID, ADJUSTMENT_TYPE, OPR_MONTH, VERSION_LABEL)
VALUES (:p0, :p1, :p2, :p4)
RETURNING ROWID INTO updatedRowid;
OPEN :outParameter FOR SELECT VERSION_ID FROM DATA_VERSION WHERE ROWID = updatedRowid;
END;

The clause:

SELECT VERSION_ID FROM DATA_VERSION WHERE ROWID = updatedRowid;

becomes slow once my table is large.

I was hoping that you can enable it like: HasDefaultSql("select data_seq.nextval from dual") and that the generated SQL could be like:


BEGIN

select data_seq.nextval from dual into p5;

INSERT INTO ABISS.DATA_VERSION (DATA_VERSION_ID, ACTIVITY_TYPE_ID, ADJUSTMENT_TYPE, OPR_MONTH, VERSION_LABEL)
VALUES (p5, :p0, :p1, :p2, :p4)
RETURNING number INTO :p5;

END;

This will be more efficient since it avoids the extra ..SELET WHERE ROWID= rowid from the currently generated SQL.

Let me know what you think.

[Shalex]

Solution 1.

Refer to https://stackoverflow.com/questions/528 ... e/52861607.

Solution 2.

Code: Select all

public class DbSequence
{
[Key]
public int NEXTVALUE { get; set; }
}
...
DbSequence sequence = await dbContext.Set<DbSequence>().FromSql("SELECT DATA_BLOCK_SEQ.NEXTVAL FROM DUAL").ToArray().FirstOrDefaultAsync();
...
entity.Blockid = sequence.NEXTVALUE;