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

how to get next value of sequence in sql server
entity framework 6 code first sequence
how to get next primary key value in sql server
select next value from sequence
get sql query from entity framework core
how to use sequence in stored procedure sql server
sql server sequence duplicate values
how to check sequence in sql server

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.

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

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. Accepted Answer. 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:

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

Sequences, HasDefaultValueSql("NEXT VALUE FOR shared.OrderNumbers"); }. Note that the specific SQL used to generate a value from a sequence is the above example works on SQL Server but will fail on other databases. Consult  The entity is loaded from SQL Server via Entity Framework 4. I would like to provide 'next' and 'previous' links to walk through logs bidirectionally. Logs are ordered by two properties/columns: Date; Time; Both of these columns may contain null, and there is no guarantee of uniqueness.

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

Use SQL Server Sequence in Entity Framework Core to Create , Use sql server sequence in entity framework core to create primary key via So in this case, the default value we need to get it from the newly created sequence. The SQL Syntax to select next value from the sequence is,  The best option, as you suggested, is to set DatabaseGeneratedOption.None and get the next value from the sequence (e.g. as in this question) right before you save a new record. Then assign it to the Id value, and save.

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.

Getting "NEXT VALUE FOR" for a SQL Server sequence using EF , If you want to run an arbitrary TSQL batch and return a scalar value, you can do it like this: var p = new SqlParameter("@result", System.Data. So in this case, the default value we need to get it from the newly created sequence. The SQL Syntax to select next value from the sequence is, SELECT NEXT VALUE FOR DBSequence. And same syntax we need to supply HasDefaultValueSql except “SELECT” keyword. Now, lets add some data using the following code.

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

Using T-SQL SEQUENCE with Entity Framework 6, Your C# code looks fine, but using non-cached sequences instead of IDENTITY (​which would be much easier to implement in Entity  The NEXT VALUE FOR function supports generating sorted sequence values by applying the OVER clause to the NEXT VALUE FOR call. By using the OVER clause, a user is guaranteed that the values being returned are generated in the order of the OVER clause's ORDER BY subclause.

Provide a mechanism for getting the next value in a sequence , Per https://docs.microsoft.com/en-us/ef/core/modeling/relational/sequences, Fluent to get the next value from a sequence without hand-writing SQL. (A.. .​com/use-sql-server-sequence-in-entity-framework-core-primary-key/  I have created the following SQL SEQUENCE . CREATE SEQUENCE SEQ_ORDENES_TRABAJO as int START WITH 1 INCREMENT BY 1 MINVALUE 1 CYCLE ; That is used in this table to allow the 'Consecutivo' field to use that SEQUENCE

Sequence Object in SQL Server, How to Create an Incrementing Sequence Object in SQL Server? How to Generate the Next Sequence Value in SQL Server? Retrieving the current sequence  17. It's clear that you can't escape from this catch-22 by playing with DatabaseGeneratedOption s. The best option, as you suggested, is to set DatabaseGeneratedOption.None and get the next value from the sequence (e.g. as in this question) right before you save a new record. Then assign it to the Id value, and save.

SQL Sequence Vs Identity Column, Sequences are a relatively new feature that have only existed since SQL Server 2012, but have long existed in Oracle (where WHERE name='MySequence' To Get Next Value SELECT NEXT VALUE FOR MySequence; SQL MiddleInt, LastName) VALUES ('Jane', 'L', 'Doe'); SELECT * FROM MyIdentity. You can set up a sequence in the model, and then use it to generate values for properties: protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.HasSequence<int>("OrderNumbers"); modelBuilder.Entity<Order>() .Property(o => o.OrderNo) .HasDefaultValueSql("NEXT VALUE FOR shared.OrderNumbers"); }

Comments
  • Possible duplicate of How to get the next Sequence number in Sql Server Express using Entity Framework?
  • @TimPohlmann: just run the SQL statement I show in the "Update" section - no stored procedure or anything needed
  • But I have to generate dbo first. Don't I?
  • You need to create the dbo.TestSequence as a SEQUENCE first - yes
  • If you're using migrations; you can add your CREATE SEQUENCE script to a migration as a Sql("CREATE SEQUENCE ......."); call
  • how do you handle it with transaction ?
  • How do you map this in an Entity?
  • What do you mean? You wan to use it as a primary key?
  • DUAL is an ORACLE syntax.
  • This should be a comment, not an answer.
  • 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.