I need to retrieve a sequence value to use when adding new records on table PERSON. The pure SQL to do this is:

SELECT SEQ_PERSON_ID.NEXTVAL FROM DUAL

I tried to use this code:

var newID = this.ExecuteQuery<decimal>("SELECT SEQ_PERSON_ID.NEXTVAL FROM DUAL").First();

But I'm told ExecuteQuery is unknown.

This an Oracle 11 database. I use LinqPad 7. Connection provider is Oracle 7.21.12 latest. Entity Framework Core is v7.0.3.

How can I get the latest incremental value of PERSON_ID in a LinqPad program?

I need to retrieve a sequence value to use when adding new records on table PERSON. The pure SQL to do this is:

SELECT SEQ_PERSON_ID.NEXTVAL FROM DUAL

I tried to use this code:

var newID = this.ExecuteQuery<decimal>("SELECT SEQ_PERSON_ID.NEXTVAL FROM DUAL").First();

But I'm told ExecuteQuery is unknown.

This an Oracle 11 database. I use LinqPad 7. Connection provider is Oracle 7.21.12 latest. Entity Framework Core is v7.0.3.

How can I get the latest incremental value of PERSON_ID in a LinqPad program?

• entity-framework-core
• oracle11g
• linqpad

• 1 What is this in your code snippet? – marc_s Commented Nov 19, 2024 at 7:40
• As of EF Core 7, you should be able to use var result = context.Database.SqlQuery<int>(---your-SQL-statement-here----); - I've only seen samples for SQL Server, SQLite and PostgreSQL - not sure if this will work with the Oracle provider, too – marc_s Commented Nov 19, 2024 at 7:41
• See: learn.microsoft/en-us/ef/core/querying/sql-queries – marc_s Commented Nov 19, 2024 at 7:41
• EF Core's DbContext never had an ExecuteQuery. Are you trying to use EF Classic code with EF Core? You don't need this to retrieve the next sequence value for a key. You can configure the entity's key to be auto-generated and EF itself will retrieve the key and fix up FK constraints. If you do need to execute an arbitrary query, use SqlQuery. – Panagiotis Kanavos Commented Nov 19, 2024 at 7:45
• Entity Framework Core is v7.x that's an old version, only needed because it was the last version to support .NET 6, which also went out of support this month. The oldest supported .NET (Core) version is now 8 – Panagiotis Kanavos Commented Nov 19, 2024 at 7:46

2 Answers 2

Since there is no ExecuteSql or similar methods I just inserted a new view which retrieves the newest ID.

Oracle doesn't support below as view:

CREATE OR REPLACE VIEW VIEW_NEXT_ID AS SELECT SEQ_PERSON_ID.NEXTVAL AS NEXT_ID FROM DUAL

So first I created a table function:

CREATE OR REPLACE FUNCTION GET_NEXT_PERSON_ID
RETURN SYS.ODCINUMBERLIST PIPELINED IS
BEGIN
   PIPE ROW (SEQ_PERSON_ID.NEXTVAL);
   RETURN;
END;

And then the view like:

CREATE OR REPLACE VIEW VW_GET_NEXT_PERSON_ID AS SELECT COLUMN_VALUE AS NEXT_ID FROM TABLE(GET_NEXT_PERSON_ID())

And the C# code in LinqPad:

var nextVal = VwGetNextPersonID.FirstOrDefault();
var newID = (int)nextVal.NextID;

In LinqPad with an Oracle 11 database, you can use ExecuteQuery or a similar approach to retrieve the next sequence value. However, if ExecuteQuery is not recognized, you can use ExecuteScalar with raw SQL, as it is a more straightforward and widely supported method.

Here’s how you can retrieve the next value of SEQ_PERSON_ID using LinqPad and Entity Framework Core:

using System.Linq;

void Main()
{
    // Assuming you have set up a DbContext for your Oracle database
    using (var context = new YourDbContext())
    {
        // Retrieve the next value of the sequence
        var newID = context.Database.ExecuteSqlRaw("SELECT SEQ_PERSON_ID.NEXTVAL FROM DUAL");
        
        // Print the retrieved ID
        newID.Dump("Next Sequence Value for PERSON_ID:");
    }
}

Check DbContext Setup: Ensure your YourDbContext is properly configured for your Oracle connection.