How to get next value of SQL Server sequence in Entity Framework?
Solution 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;
}
}
Solution 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;
}
Solution 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;
}
Solution 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 :)
Solution 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.
ManirajSS
Fav quote: High quality code doesn't happen by accident - it takes patience and precision
Updated on June 04, 2020Comments
-
ManirajSS almost 4 years
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.
-
marc_s about 8 years@TimPohlmann: just run the SQL statement I show in the "Update" section - no stored procedure or anything needed
-
Tim Pohlmann about 8 yearsBut I have to generate dbo first. Don't I?
-
marc_s about 8 yearsYou need to create the
dbo.TestSequence
as aSEQUENCE
first - yes -
marc_s about 8 yearsIf you're using migrations; you can add your
CREATE SEQUENCE
script to a migration as aSql("CREATE SEQUENCE .......");
call -
Alexandre N. over 6 yearsDUAL is an ORACLE syntax.
-
AGH over 5 yearshow do you handle it with transaction ?
-
Machado about 5 yearsHow do you map this in an Entity?
-
Mario The Spoon over 4 yearsWhat do you mean? You wan to use it as a primary key?
-
CoderLee about 4 yearsThis should be a comment, not an answer.
-
Dan about 4 yearsI 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.
-
Dai almost 3 years@AGH Wrap all of your EF work with
ctx.Database.BeginTransaction()
and commit at the end (after your lastSaveChangesAsync
call) and it should work. -
Luke almost 3 yearsIf 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: stackoverflow.com/a/63968315/6621862. Just realized the using statement is already in this answer :facepalm: -
Shadman almost 2 yearsI had a problem with
ExecuteSqlRaw
where it just returns -1 with any queries whereas this ADO.NET code works well.