How to set initial value for auto incremented property (DatabaseGeneratedOption.Identity)

Question

I have a class called Offer as follows:

public class Offer
{
    public Guid Id { get; set; }

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int OfferNo { get; set; }

    public OfferType OfferType { get; set; }
    public DateTimeOffset DateAdded { get; private set; }
    public DateTimeOffset DateEdited { get; set; }
    public bool IsActive { get; set; }
}

I am using Id property as my PK obviously. But I also need to display the Id of offers (as users will search for offers using this value) and the Guid property is too long for that.

Thus, I tried to use DatabaseGeneratedOption.Identity to auto increment the integer column OfferNo, but I can not set an initial value to increment on. I inserted a dummy entry and then tried to set OfferNo to something else than 1, but received the following exception:

Modifying a column with the 'Identity' pattern is not supported. Column: 'OfferNo'. Table: 'CodeFirstDatabaseSchema.Offer'.

I would like to set the initial value of this auto incremented column using code-first. An alternative solution will also be appreciated.

Answer 1

Just found a solution for this matter. You can simply call a Sql() method in your Up() method.

public override void Up()
    {
        CreateTable(
            "Offers",
            c => new
                {
                    OfferNo = c.Int(nullable: false, identity: true),
                    ...
                })
            .PrimaryKey(t => t.OfferNo);
        Sql("DBCC CHECKIDENT ('Offers', RESEED, 100);");
    }

Answer 2

According to the comment, "but starting from a seed value that I provide instead of 1", you can use an identity column, and customize your database initialization or migration to set the seed of your identity column.

The T-SQL command to do this is:

DBCC CHECKIDENT ('Offer', RESEED, 123);

Note that the next inserted value is not 123, but 123 + increment (124 if default increment of 1).

You can also use a column with the DatabaseGeneratedOption.Computed and a sequence as default value for your field (if you're using a recent SQL server version). When you create a sequence, you can specify the initial value and increment:

CREATE SEQUENCE OfferNoSeq
START WITH 1 -- Initial Value
INCREMENT BY 1 -- Increment

An attach this sequence as a default for the OfferNo column like this:

ALTER TABLE Offer ADD CONSTRAINT OfferNoSeq 
DEFAULT (NEXT VALUE FOR OfferNoSeq)  FOR OfferNo;

There is no direct way to implement this in Code First. So, for using any of these options, you need to

• customize the DB initialization This is done by implementing your own database initializer class and execute the desired SQL commands from the Seed methods (look for the implementation of public class MyInitializer in the linked article)
• or to customize a migration: you can execute any SQL Command in the Up() or Down() method of your migration, as shown in the linked SO answer

Please, if you use SEQUENCE, please, read this: EF6 does not work with primary key from sequence.