Using T-SQL SEQUENCE with Entity Framework 6

Question

After reading many posts about how to handle sequences in EF 6 (see below) I found a way that was working for me. I'm using Code First and want to include the sequence from scratch, so without using migrations (tell me if I'm getting this wrong).

Task was to get an unique and gapless incrementing extra ID for my entity Car, which is not being used as primary key.

Here is what I'm doing now:

Entity:

public class Car
{
    public int Id { get; set; }
    public int CarNr { get; set; }
    public string Name { get; set; }
}

Context:

public class Context : DbContext
{
    public Context() : base()
    {
        SetDefaultConfiguration();
    }

    public Context(string connectionString) : base(connectionString)
    {
        SetDefaultConfiguration();
    }

    public Context(DbConnection existingConnection) : base(existingConnection, true)
    {
        SetDefaultConfiguration();
    }

    public override int SaveChanges()
    {
        SetCarNr();
        return base.SaveChanges();
    }

    public override Task<int> SaveChangesAsync()
    {
        SetCarNr();
        return base.SaveChangesAsync();
    }

    public override Task<int> SaveChangesAsync(CancellationToken cancellationToken)
    {
        SetCarNr();
        return base.SaveChangesAsync(cancellationToken);
    }

    private void SetDefaultConfiguration()
    {
        Database.SetInitializer(new CreateSequenceInitializer());
    }

    public class CreateSequenceInitializer : DropCreateDatabaseIfModelChanges<Context>
    {
        protected override void Seed(Context context)
        {
            context.Database.ExecuteSqlCommand("CREATE SEQUENCE CarNrSequence AS INT START WITH 200000 NO CACHE;");
            base.Seed(context);
        }
    }

    private void SetCarNr()
    {
        var carsToSave = ChangeTracker.Entries().Select(e => e.Entity).OfType<Car>();
        foreach (var c in carsToSave)
        {
            c.CarNr = GetNextSequenceValue();
        }
    }

    public int GetNextSequenceValue()
    {
        var rawQuery = Database.SqlQuery<int>("SELECT NEXT VALUE FOR CarNrSequence;");
        var task = rawQuery.SingleAsync();
        int nextVal = task.Result;
        return nextVal;
    }

    public DbSet<Car> Cars { get; set; }
}

So I'm using a DatabaseInitializer to create the sequence for me and intercept the SaveChanges to insert the extra ID queried from the DB.

Question: Is there a better way? Is this "save"? What problems do you see?

Thanks!

PS: Here are some links that helped me a lot

• https://stackoverflow.com/questions/27077461/how-to-get-next-value-of-sql-server-sequence-in-entity-framework
• https://stackoverflow.com/questions/24162895/entity-framework-6-and-sql-server-sequences
• https://stackoverflow.com/a/34894274/226278
• https://stackoverflow.com/questions/20136504/how-can-set-a-default-value-constraint-with-entity-framework-6-code-first

Answer 1

Your C# code looks fine, but using non-cached sequences instead of IDENTITY (which would be much easier to implement in Entity Framework) does not ensure a gapless incrementing identifier.

According to this Microsoft article, sequences can generate gaps in some scenarios:

The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used. Sequence numbers inserted into a table can have gaps when a transaction is rolled back, when a sequence object is shared by multiple tables, or when sequence numbers are allocated without using them in tables.

So, error on saving changes will have consumed the sequence numbers and thus you obtain gaps.

On the database side, this thread approaches the generation of gapless incrementing identifier, but I am also thinking about generating identifiers within the application layer and restoring the value on save failure:

private int PrevSequenceId;
private int SequenceId;

private void SetCarNr()
{
    var carsToSave = ChangeTracker.Entries().Select(e => e.Entity).OfType<Car>();
    foreach (var c in carsToSave)
    {
        c.CarNr = Interlocked.Increment(ref SequenceId);
    }
}

public override int SaveChanges()
{
    try
    {
        SetCarNr();
        return base.SaveChanges();
        // locking might be required here for thread safety
        PrevSequenceId = SequenceId;
    }
    catch (Exception exc)
    {
        // log stuff if needed

        // locking might be required here for thread safety
        SequenceId = PrevSequenceId;
    }
}

However, this solution may work only if identifiers are generated by the application layer only.