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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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();

Answer 8

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