oracle sequence with entity framework core throws ORA-02287

Question

I've got an ASP .net 6 project with EF core. I need a sequenced value outside of tables, keys, etc. So I'm doing this:

public class GenericInt { public int IntValue { get; set; } }
public DbSet<GenericInt> GenericInts { get; set; }
...
var qresult = await ctx.GenericInts.FromSqlRaw("select MYSCHEMA.MY_SEQ.NEXTVAL from dual").FirstOrDefaultAsync();

This throws:

Oracle.ManagedDataAccess.Client.OracleException
HResult=0x80004005
Message=ORA-02287: sequence number not allowed here
Source=Oracle Data Provider for .NET, Managed Driver

Why? How to repair this?

Answer 1

Panagiotis Kanavos gave the key point:

FirstOrDefault constructs a subquery.

(And sequence cannot be used in subqueries, it's a known limitation.)

There was another problem with my original EF call, so the working lines are:

var qresult = await ctx.GenericInts.FromSqlRaw("select MYSCHEMA.MY_SEQ.NEXTVAL as intval from dual").ToListAsync();
int seqValue = qresult[0].IntValue;

(The advice of mis-using EF may also be considered.)