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

You can create a simple stored procedure in SQL Server that selects the next sequence value like this:

CREATE PROCEDURE dbo.GetNextSequenceValue AS BEGIN    SELECT NEXT VALUE FOR dbo.TestSequence;END

and then you can import that stored procedure into your EDMX model in Entity Framework, and call that stored procedure and fetch the sequence value like this:

// get your EF contextusing (YourEfContext ctx = new YourEfContext()){    // call the stored procedure function import       var results = ctx.GetNextSequenceValue();    // from the results, get the first/single value    int? nextSequenceValue = results.Single();    // display the value, or use it whichever way you need it    Console.WriteLine("Next sequence value is: {0}", nextSequenceValue.Value);}

Update: actually, you can skip the stored procedure and just run this raw SQL query from your EF context:

public partial class YourEfContext : DbContext {    .... (other EF stuff) ......    // get your EF context    public int GetNextSequenceValue()    {        var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.TestSequence;");        var task = rawQuery.SingleAsync();        int nextVal = task.Result;        return nextVal;    }}

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;}

EF 3.1: Adding below function in DbContext. refer using Microsoft.Data.SqlClient;

 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 :)