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

c# entity-framework orm sql-server sql-server-2012

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.

1
29
5/23/2017 11:47:21 AM

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:

// 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;
    }
}
41
2/12/2016 3:30:00 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow