UPDATE - ONLY IDENTITY SUPPORTED FOR DB GENERATED PK
You can create a key column marked as computed, which has a DataseGeneratedOption.Computed
. (See DataseGeneratedOption enum).
To indicate this you can decorate the column with DatabaseGeneratedAttribute, or use the fluent API, in the OnModelCreating
method of your DbContext, like so:
modelBuilder.Entity<EntityType>()
.Property(c => c.KeyColumn)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed)
This sample code works perfectly with EF6.1
public class MyDbContext : DbContext
{
public IDbSet<ComputedKey> ComputedKeys { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Computed Key:
modelBuilder.Entity<ComputedKey>()
.HasKey(c => c.Id)
.Property(c => c.Id)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
}
}
public class ComputedKey
{
public int Id { get; set; }
}
When you run the app, the table is created correctly.
The problem arises when you try to add the first entity to the entity collection and save changes. You get: Modifications to tables where a primary key column has property 'StoreGeneratedPattern' set to 'Computed' are not supported. Use 'Identity' pattern instead. Key column: 'Id'. Table: 'CodeFirstDatabaseSchema.ComputedKey'
.
This is a limitation of EF (until 6.1) that only allows to have integer type or GUID as DB generated value for a PK.
WORKAROUNDS
First:
One would be to use the column generated on the DB as an alternative key.
From EF 6.1 on you can create the AK, decorating the AK column with an attribute like this:
[Index("MyIndex", unique: true)]
Second:
The reason for using a sequence is defining the seed and the increment. If this is what you need you can modify the identity like this:
DBCC CHECKIDENT ('SchemaName.TableName', RESEED, 10);
This means that the next generated identity value will be 11, and the increment will be 1.
If you need to use a different increment you'd need to drop and re-create the identity column, indicating the seed and increment. But for doing this you also need to drop and create the associated foreingk keys, so that's too hard to implement.
Third:
You could use a trigger. Inside the trigger you can use SET IDENTITY_INSERT tableName ON/OFF
, but then again you can have problems because the @@identity
will mismatch.
NOTE: if you need to run custom SQL commands to apply this customizations, you need to implement the Seed method of your db initializer
CONCLUSION
This scenario is only partially supported, so you'd rather find an alternative solution, unless one of the previous work arounds work for you.
REQUEST THIS FUNCTIONALITY
if you're interested in this functionality, go to the Entity Framework Feature Suggestions, and vote for this: Allow using SQL Server 2012 sequence for generating primary key