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

To retrieve the next value of a SQL Server sequence in Entity Framework, various methods can be employed. Let's explore several approaches:

1. Creating a Stored Procedure:

   This involves creating a simple stored procedure in SQL Server that selects the next sequence value. The stored procedure can be imported into the Entity Framework EDMX model. Here's an example:

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

   Once imported into the EF model, you can call this stored procedure to fetch the sequence value as demonstrated below:

   using (YourEfContext ctx = new YourEfContext())
   {
       // Call the stored procedure function import
       var results = ctx.GetNextSequenceValue();
   
       // Get the first/single value from the results
       int? nextSequenceValue = results.Single();
   
       // Display the value or use it as needed
       Console.WriteLine("Next sequence value is: {0}", nextSequenceValue.Value);
   }
   

2. Raw SQL Query:

   Instead of using a stored procedure, you can directly execute a raw SQL query from your EF context to retrieve the next sequence value. For instance:

   public partial class YourEfContext : DbContext 
   {
       .... (other EF stuff) ......
   
       // Get the next sequence value using raw SQL
       public int GetNextSequenceValue()
       {
           var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR dbo.TestSequence;");
           var task = rawQuery.SingleAsync();
           int nextVal = task.Result;
   
           return nextVal;
       }
   }
   

3. Using an Entity Class:

   If you don't want to use stored procedures or raw SQL, you can create an entity class that holds the sequence value. Then, run a raw SQL query against it to retrieve the value. Here's an example:

   SEQ_TXN_ID txn_id= _context.SEQ_TXN_IDs.SqlQuery("SELECT txn_id_seq.NEXTVAL txn_ID FROM DUAL").FirstOrDefault();
   

4. Defining a Sequence in Code First:

   With EF Core 2.1 and above, you can define a sequence in Code First by adding the following code to the OnModelCreating method of your DbContext:

   protected override void OnModelCreating( ModelBuilder modelBuilder )
   {
       modelBuilder.HasSequence("MySequence");
   }
   

   To retrieve the next sequence value, you can add a function to your context as demonstrated below:

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

5. Extension Class for DbContext:

   You can create an extension class for DbContext that handles retrieving the next sequence value. This allows you to retrieve the value in an elegant and type-proof manner, as seen in the example below:

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

   You can then use it as follows:

   ctx.NextValueForSequence(Sequence.OrderNr);
   

6. Direct Execution of Raw SQL (EF Core):

   For EF Core users, the next sequence value can be obtained directly using raw SQL:

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

7. Using an Extension Method:

   You can create an extension method to retrieve the next sequence value, as seen in the example below:

   public static Int64 GetNextFormSequenceValue(){
       Int64 result = 0;
       var db = new dbContext.db(); //Adjust as per your LINQ naming conventions
       Int64 nextId = db.ExecuteQuery<Int64>("SELECT NEXT VALUE FOR YOURSEQNAME").FirstOrDefault();
   
       if(nextId > 0){
         result = nextId;
       }
       return result;
   }
   

   This method can then be invoked as follows:

   await context.NextValueForSequence(SequenceEnum.Sequence.FirstSequence);
   

8. Generating SELECT Statements:

   With EF Core 3.1 and above, you can generate SELECT statements that retrieve the next sequence value. This can be achieved using the following code:

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

The specific approach you choose will depend on your project's requirements and preferences.