Is it possible to autoincrement an ID with a certain step size in EntityFramework 6?

Question

I need to start my model id at 100'000 and increment it by 100'000 for every following item. Is there a way to do this in EF6? I already looked for Annotations and Fluent API solutions, but couldn't find anything.

I only found a way to do it in EF Core but there seems to be no similar way to do it in EF 6 (https://learn.microsoft.com/en-us/ef/core/modeling/relational/sequences)

Current Model:

public class Bubble
{
    // Keys
    [Required]
    [Key]
    public Int64 BubbleId { get; set; }

    // Members (needs max. string length that you can set it to unique -> https://stackoverflow.com/questions/10614575/entity-framework-code-first-unique-column)
    [Required]
    [StringLength(450)]
    [Index(IsUnique = true)]
    public string BubbleName { get; set; }

    // Navigation propertys
    public virtual ICollection<BubbleNode> BubbleNodes { get; set; }
}

Possible duplicate from: https://stackoverflow.com/questions/24162895/entity-framework-6-and-sql-server-sequences — Bruno, Apr 15 '19 at 21:42

score 0 · Accepted Answer · answered Apr 15 '19 at 22:55

If you are starting with a fresh schema then it is possible to intercept the schema creation and alter the identity creation. If the schema is already created (I.e. counting on migrations) not so. SQL for instance will allow for reseeding an identity, but not changing the increment.

This example uses a table called [Somethings] and checks for when the table is created, substituting the "IDENTITY" with IDENTITY(100000,100000) if found. Unfortunately to my knowledge with EF 6 the interceptor can't be attached/detached from the context, so you can use a flag to ensure the interceptor check/replace code doesn't run all the time by default.

To intercept the schema creation:

public class Interceptor : IDbCommandInterceptor
{

    void IDbCommandInterceptor.NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    { }

    void IDbCommandInterceptor.NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        if (interceptionContext.DbContexts.OfType<SomethingDbContext>().Any(x => !x.ShouldIntercept))
            return;

        var regex = "NOT NULL IDENTITY,";
        var replacement = "NOT NULL IDENTITY(100000,100000),";
        if (command.CommandText.StartsWith("CREATE TABLE [dbo].[Somethings]"))
            command.CommandText = Regex.Replace(command.CommandText, regex, replacement);
    }

    void IDbCommandInterceptor.ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    { }

    void IDbCommandInterceptor.ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    { }

    void IDbCommandInterceptor.ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    { }

    void IDbCommandInterceptor.ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    { }
}

public class SomethingConfiguration : DbConfiguration
{
    public SomethingConfiguration()
    {
        AddInterceptor(new Interceptor());
    }
}

Then in your DbContext:

[DbConfigurationType(typeof(SomethingConfiguration))]
public class SomethingDbContext : DbContext
{
    public bool ShouldIntercept { get; set;} = false;
    //  .. DbSets, etc.
}

By default your DbContext will not actively intercept the commands commands since we really only want to activate this overhead check once. When your application starts you can initialize a DbContext, set the ShouldIntercept to True, then execute a simple query which will ensure the DbContext schema inspection/creation runs:

using (var context = new SomethingDbContext())
{
    context.ShouldIntercept = true;
    var test = context.Somethings.Any(); // Triggers schema check/creation.
}

Thank you very mutch for that detailed answer. This seems like one hell of an act for something that takes a few lines in EF core. Would it maybe be smart to use EF Core for a new project? I could probably migrate everything in ~1 day. — TheGame402, Apr 15 '19 at 23:01
:) I'd say it isn't that common of a requirement, especially for code-first. For specific schema control my default recommendation would be to use DB-first. EF just needs to know it is an identity, the DB can be set up with whatever generation option you want. — Steve Py, Apr 15 '19 at 23:15

Is it possible to autoincrement an ID with a certain step size in EntityFramework 6?

1 Answers1