Entity Framework 6 and SQL Server Sequences

It's clear that you can't escape from this catch-22 by playing with DatabaseGeneratedOptions.

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. This is concurrency-safe, because you will be the only one drawing that specific value from the sequence (let's assume no one resets the sequence).

However, there is a possible hack...

A bad one, and I should stop here...

EF 6 introduced the command interceptor API. It allows you to manipulate EF's SQL commands and their results before and after the commands are executed. Of course we should not tamper with these commands, should we?

Well... if we look at an insert command that is executed when DatabaseGeneratedOption.Identity is set, we see something like this:

INSERT [dbo].[Person]([Name]) VALUES (@0)
SELECT [Id]
FROM [dbo].[Person]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

The SELECT command is used to fetch the generated primary key value from the database and set the new object's identity property to this value. This enables EF to use this value in subsequent insert statements that refer to this new object by a foreign key in the same transaction.

When the primary key is generated by a default taking its value from a sequence (as you do) it is evident that there is no scope_identity(). There is however a current value of the sequence, which can be found by a command like

SELECT current_value FROM sys.sequences WHERE name = 'PersonSequence'

If only we could make EF execute this command after the insert instead of scope_identity()!

Well, we can.

First, we have to create a class that implements IDbCommandInterceptor, or inherits from the default implementation DbCommandInterceptor:

using System.Data.Entity.Infrastructure.Interception;

class SequenceReadCommandInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command
           , DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }
}

We add this class to the interception context by the command

DbInterception.Add(new SequenceReadCommandInterceptor());

The ReaderExecuting command runs just before command is executed. If this is an INSERT command with an identity column, its text looks like the command above. Now we could replace the scope_identity() part by the query getting the current sequence value:

command.CommandText = command.CommandText
                             .Replace("scope_identity()",
                             "(SELECT current_value FROM sys.sequences
                               WHERE name = 'PersonSequence')");

Now the command will look like

INSERT [dbo].[Person]([Name]) VALUES (@0)
SELECT [Id]
FROM [dbo].[Person]
WHERE @@ROWCOUNT > 0 AND [Id] = 
    (SELECT current_value FROM sys.sequences
     WHERE name = 'PersonSequence')

And if we run this, the funny thing is: it works. Right after the SaveChanges command the new object has received its persisted Id value.

I really don't think this is production-ready. You'd have to modify the command when it's an insert command, choose the right sequence based on the inserted entity, all by dirty string manipulation in a rather obscure place. And I don't know if with heavy concurrency you will always get the right sequence value back. But who knows, maybe a next version of EF will support this out of the box.