search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.8k stars 3.2k forks source link

Provide a mechanism for getting the next value in a sequence #8403

Open ExplodingCabbage opened 7 years ago

ExplodingCabbage commented 7 years ago

Per https://docs.microsoft.com/en-us/ef/core/modeling/relational/sequences, Fluent provides a mechanism for creating sequences, but there doesn't seem to be any mechanism to get the next value from a sequence without hand-writing SQL. (At least, the docs don't mention any such mechanism, and http://stackoverflow.com/q/27077461/1709587 and http://www.talkingdotnet.com/use-sql-server-sequence-in-entity-framework-core-primary-key/ both use some SQL statement or expression like SELECT NEXT VALUE FOR dbo.TestSequence explicitly.)

This is a nuisance when I need to get sequence values from the application layer - especially combined with https://github.com/aspnet/EntityFramework/issues/1862, which forces me to step outside the EF framework to do the SELECT.

It would be useful if there were a built-in GetValueFromSequence method, or some other API for getting sequence values.

josephcorbett commented 1 year ago

Any movement on this at all? It is a bummer to have to use inline SQL to do this.

roji commented 1 year ago

This can be done today by using the following:

var updateSqlGenerator = ctx.GetService<IUpdateSqlGenerator>();
var sql = updateSqlGenerator.GenerateObtainNextSequenceValueOperation("SeqName", "SeqSchema");

This gives: NEXT VALUE FOR [SeqSchema].[SeqName]

@ajcvickers we could make this nicer by just exposing a relational extension over DatabaseFacade, though that could also pollute that surface with a seldom-used API.