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

Question

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??

Answer 1

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;

Answer 2

Looks like executing raw SQL is not priority for EF Core, so up to now (EF Core 3.1) it's providing publicly just few basic limited methods. FromSql requires entity type or keyless entity type, and ExecuteSqlRaw / ExecuteSqlInterpolated are the "modern" bridge to ADO.NET ExecuteNonQuery which returns the affected rows.

The good thing is that EF Core is built on top of a public service architecture, so it can be used to add some missing functionalities. For instance, services can be used to build the so called IRelationalCommand, which has all the DbCommand execute methods, in particular ExecuteScalar needed for SQL in question.

Since EF Core model supports sequences, there is also a service for building the IRelationalCommand needed to retrieve the next value (used internally by HiLo value generators).

With that being said, following is a sample implementation of the custom method in question using the aforementioned concepts:

using System;
using System.Globalization;
using Microsoft.EntityFrameworkCore.Diagnostics;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Update;

namespace Microsoft.EntityFrameworkCore
{
    public static partial class CustomExtensions
    {
        public static long GetNextSequenceValue(this DbContext context, string name, string schema = null)
        {
            var sqlGenerator = context.GetService<IUpdateSqlGenerator>();
            var sql = sqlGenerator.GenerateNextSequenceValueOperation(name, schema ?? context.Model.GetDefaultSchema());
            var rawCommandBuilder = context.GetService<IRawSqlCommandBuilder>();
            var command = rawCommandBuilder.Build(sql);
            var connection = context.GetService<IRelationalConnection>();
            var logger = context.GetService<IDiagnosticsLogger<DbLoggerCategory.Database.Command>>();
            var parameters = new RelationalCommandParameterObject(connection, null, null, context, logger);
            var result = command.ExecuteScalar(parameters);
            return Convert.ToInt64(result, CultureInfo.InvariantCulture);
        }
    }
}

Answer 3

In your fluent api configs you can create migration that set ID automatically to be next value from Sequence

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

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

For creating sequence:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasSequence<int>("OrderNumbers", schema: "shared")
        .StartsAt(1000)
        .IncrementsBy(5);
}

Read more from here: https://www.talkingdotnet.com/use-sql-server-sequence-in-entity-framework-core-primary-key/

Answer 4

For people suffering with oracle version of this problem, here's a solution:

var p = new OracleParameter("result", OracleDbType.Decimal, null, System.Data.ParameterDirection.Output);
Database.ExecuteSqlRaw($"BEGIN :result := my_seq.nextval; END;", p);
var nextVal = p.Value;