How to set initial value for auto incremented property (DatabaseGeneratedOption.Identity)
c#, entity-framework
Solution
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.
Problem
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.