How to get next value of SQL Server sequence in Entity Framework?

Since I am using Code First and I do not want to have some additional DDL, this is my way: (EF Core 2.1, SQL Server)

Define the sequence:

protected override void OnModelCreating( ModelBuilder modelBuilder )
{
    modelBuilder.HasSequence("MySequence");
}

And to retrieve it I add the following function to the context:

public int GetMySequence()
{
   SqlParameter result = new SqlParameter("@result", System.Data.SqlDbType.Int)
   {
      Direction = System.Data.ParameterDirection.Output
   };

   Database.ExecuteSqlCommand(
              "SELECT @result = (NEXT VALUE FOR MySequence)", result);

   return (int)result.Value;
}

In case anyone else who is working with Entity Framework Core ends up looking here, this worked for me:

var connection = dbContext.Database.GetDbConnection();
connection.Open();
using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "SELECT NEXT VALUE FOR ACH.FileIDModifier;";
    var obj = cmd.ExecuteScalar();
    int anInt = (int)obj;
}

EF 6.0: modify DbContext class, adding NextValueForSequence function. Code-first creation of sequences (default settings):

using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;
using System.ComponentModel;

public class myContext : DbContext
{
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         modelBuilder.HasSequence<int>("Generate_First_Sequence");
         modelBuilder.HasSequence<int>("Generate_Second_Sequence");
         
         // other ModelBuilder calls as needed
         // ...
     }

     // DbSet declarations as needed
     // ...

     public async Task<int> NextValueForSequence(SequenceEnum.Sequence sequence)
     {
         SqlParameter result = new SqlParameter("@result", System.Data.SqlDbType.Int)
         {
             Direction = System.Data.ParameterDirection.Output
         };

         var sequenceIdentifier = sequence.GetType().GetMember(sequence.ToString()).First().GetCustomAttribute<DescriptionAttribute>().Description;

         await Database.ExecuteSqlRawAsync($"SELECT @result = (NEXT VALUE FOR [{sequenceIdentifier}])", result);

         return (int)result.Value;
     }
}


public class SequenceEnum
{
    public enum Sequence
    {
        [Description("Generate_First_Sequence")]
        FirstSequence,
        [Description("Generate_Second_Sequence")]
        SecondSequence,
    }
}

While calling context

await context.NextValueForSequence(SequenceEnum.Sequence.FirstSequence);

Hope this helps :)

Since this functionality doesn't come out of the box, I came up to write an extension-class for the DbContext that does the job. Have a look at this chunk of code:

public enum Sequence
{
    [Description("sequence__name__goes__here")]
    ClientNr,
    [Description("another__sequence__name")]
    OrderNr,
}
public static class MyDbContextExtensions
{
    public static int NextValueForSequence(this MyDbContext pCtx, Sequence pSequence)
    {
        SqlParameter result = new SqlParameter("@result", System.Data.SqlDbType.Int)
        {
            Direction = System.Data.ParameterDirection.Output
        };
        var sequenceIdentifier = pSequence.GetType()
                    .GetMember(pSequence.ToString())
                    .First()
                    .GetCustomAttribute<DescriptionAttribute>()
                    .Description;
        pCtx.Database.ExecuteSqlCommand($"SELECT @result = (NEXT VALUE FOR [{sequenceIdentifier}]);", result);
        return (int)result.Value;
    }
}

While I must admit that all that stuff with reflection and annotations for some might seem like an overkill, I still kinda like it.

It allows me to retrieve the value in a pretty elegant way

ctx.NextValueForSequence(Sequence.OrderNr);

It also mocks a "type proof" way, constraining me to explicitly define the different sequence names in a centralized location rather than just passing magic strings from anywhere I want.

If you don't want it that way, just change the method in order to pass the sequence name as a string. It would work just as fine.

As of today imo there's more elegant solution. Wat I disliked about available answers - hardcoded sequence names. Therefore, sharing my solution:

public async Task<int> GetNextValueAsync(string sequenceName, string? schema = null)
{
    var sqlGenerator = this.GetService<IUpdateSqlGenerator>();
    var sql = sqlGenerator.GenerateNextSequenceValueOperation(sequenceName, schema);

    var result = await Database.SqlQueryRaw<int>(sql).ToListAsync();
    return result.Single();
}

It is based on the fact that now we can generate SELECTs to retrieve next from the sequence: https://github.com/dotnet/efcore/issues/8403#issuecomment-1457919371

The updated answer that marc_s gave is missing a task.wait() after the "rawQuery.SingleAsync();". Without a wait() could cause a race condition, attempting to get the results before the query is actually ran in an asynchronous (multi-threaded) process.

If you wanted to do it outside of a stored procedure you can create an entity class that holds just a string or int (whatever your sequence returns) and then run some raw SQL against it. Then just use your object or string however you'd like.

 SEQ_TXN_ID txn_id= _context.SEQ_TXN_IDs.SqlQuery("SELECT txn_id_seq.NEXTVAL txn_ID FROM DUAL").FirstOrDefault();

//This code worked for me after slight mods of other answers here. Try this:
public static Int64 GetNextFormSequenceValue(){
    Int64 result = 0;
    var db = new dbContext.db(); //change the name to whatever your LINQ names are
    Int64 nextId = db.ExecuteQuery<Int64>("SELECT NEXT VALUE FOR YOURSEQNAME").FirstOrDefault();

    if(nextId > 0){
      result = nextId;
    }
    return result;
}

11 years later :) In EF 6.0 you can supplement

modelBuilder.HasSequence<int>("ApplicationNumber")
            .StartsAt(100000)
            .IncrementsBy(1);

with

entity.Property(x => x.ApplicationNumber).HasDefaultValueSql("NEXT VALUE FOR ApplicationNumber");

in the OnModelCreating. No need for extra SQL commands, procedures or extension methods 👍