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

Question

I want to make use SQL Server sequence objects in Entity Framework to show number sequence before save it into database.

In current scenario I'm doing something related by increment by one in stored procedure (previous value stored in one table) and passing that value to C# code.

To achieve this I needed one table but now I want to convert it to a sequence object (will it give any advantage ?).

I know how to create sequence and get next value in SQL Server.

But I want to know how to get next value of sequence object of SQL Server in Entity Framework?

I am to unable to find useful answers in Related questions in SO.

Thanks in advance.

Answer 1

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 context using (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;     } } 

Answer 2

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