[SOLVED] Getting "NEXT VALUE FOR" for a SQL Server sequence using EF Core 3.1

Getting "NEXT VALUE FOR" for a SQL Server sequence using EF Core 3.1 - impossible?

I'm writing a new ASP.NET Core Web API, and one of my requirements is to be able to leverage EF Core 3.1 to grab the next value of a sequence defined in my SQL Server as the ID for a record I need to store.

I'm struggling to find a way to do this - in EF 6.x, I used a method directly on the DbContext descendant like this:

public int GetNextSequenceValue()
{
    var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.TestSequence;");
    var task = rawQuery.SingleAsync();
    int nextVal = task.Result;

    return nextVal;
}

and for EF Core up to 2.1, I would have been able to use Database.ExecuteSqlCommand() to run a SQL snippet and get back results. But it seems, in EF Core 3.x, I'm out of luck....

I know there are the .FromSqlRaw() and .FromSqlInterpolated methods on the DbSet - but since I only need to return the next value of a sequence (an INT), that's not going to fly. And I also know these methods also exist on the context.Database level which looks like it would be really close to what I had in EF 6.x - but here, those methods will only return the number of rows affected - I haven't found a way to send back the new value from the SEQUENCE.

Can it really be that in EF Core 3.x, I have to actually resort back to way-old ADO.NET code to fetch that value?? Is there REALLY no way to execute an arbitrary SQL snippet and get back some results from the context??

Solution

If you want to run an arbitrary TSQL batch and return a scalar value, you can do it like this:

var p = new SqlParameter("@result", System.Data.SqlDbType.Int);
p.Direction = System.Data.ParameterDirection.Output;
context.Database.ExecuteSqlRaw("set @result = next value for some_seq", p);
var nextVal = (int)p.Value;