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.

Yettie

Possible duplicate of How to get the next Sequence number in Sql Server Express using Entity Framework? – Stele

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

Mixer

@TimPohlmann: just run the SQL statement I show in the "Update" section - no stored procedure or anything needed – Mixer

But I have to generate dbo first. Don't I? – Cathouse

You need to create the dbo.TestSequence as a SEQUENCE first - yes – Mixer

If you're using migrations; you can add your CREATE SEQUENCE script to a migration as a Sql("CREATE SEQUENCE ......."); call – Mixer

how do you handle it with transaction ? – Sowell

@Sowell Wrap all of your EF work with ctx.Database.BeginTransaction() and commit at the end (after your last SaveChangesAsync call) and it should work. – Triune

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

Massasauga

How do you map this in an Entity? – Fung

What do you mean? You wan to use it as a primary key? – Massasauga

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

Colton

I had a problem with ExecuteSqlRaw where it just returns -1 with any queries whereas this ADO.NET code works well. – Jennie

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

Bawdyhouse

If you get exception with message "The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects", check your namespace imports and make sure you have using Microsoft.Data.SqlClient;. See this answer: https://mcmap.net/q/280146/-quot-sqlparametercollection-only-accepts-non-null-sqlparameter-type-objects-not-string-objects-quot. Just realized the using statement is already in this answer :facepalm: – Aiguille

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.

Stuckup

Answer 6

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

Piercing

You still have to hard-code the sequence name :). The benefit of this code is probably that it's database-provider agnostic. – Affra

Answer 7

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.

Cytolysin

This should be a comment, not an answer. – Oxpecker

I wanted to comment, but I have not crossed 50 points yet so I couldn't. I then tried to edit and add the necessary code, but couldn't do that either. I did think it was valuable to mention because the bug introduced into the code would have been intermittent and extremely difficult to locate. – Cytolysin

Answer 8

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

Cuneal

DUAL is an ORACLE syntax. – Nabal

Answer 9

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

Discolor