c# - Entity Framework 6 and SQL Server Sequences

Question

I am using EF6 with a database first project. We have a requirement to use sequences which was a feature introduced in SQL server 2012 (I believe).

On the table the identity column has a default value set using:

(NEXT VALUE FOR [ExhibitIdentity])

This is used as we have two tables which store exhibit information for separate departments but we need the identity to be unique across both of the tables as it is then used as a reference in lots of other shared common tables.

My problem is using this within the Entity Framework, I have googled but couldn't find much information in relation to whether EF6 supports them. I have tried setting StoreGeneratedPatttern in the EFdesigner to Identity but when saving this complains that zero rows were affected as it is using scope_identity to see if the insert succeeded but as we are using sequences this comes back as null.

Setting it to computed throws an error saying I should set it to identity and setting it to none causes it to insert 0 as the id value and fail.

Do I need to call a function/procedure in order to get the next sequence and then assign it to the id value before saving the record?

Any help is much appreciated.

Answer 1

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.