How to control primary key values ​​when sowing data using Entity Framework codefirst

Question

I am creating an asp.net mvc4 site using a framework 5 entity with codefirst and sql server express 2012.

I have enabled migration and am now doing this in my Configuration.Seed: (note that I want to set the primary key to 8, even if this is the first record in the database).

context.ProductCategoryDtoes.AddOrUpdate(x => x.Id, new ProductCategoryDto() { Id = 8, Name = "category1" } ); 

The My Model object is defined as follows:

 [Table("ProductCategory")] public class ProductCategoryDto { public long Id { get; set; } public string Name { get; set; } } 

This results in a table in (SQL SERVER EXPRESS 2012), where the Id column has Identity = true, Identity seed = 1, identifier increment = 1.

Now, when I start the migration, doing PM> Update-Database this result in a line with Id = 1.

So my question is:

1) How can I control the values ​​of automatically increasing primary keys when sowing data.

2) If the solution is to increase the initial value of the key columns, how to do it when I use Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>()); . This will nuke and rebuild the database every time I update the database, so how will the seed value in the new database be updated?

Answer 1

Just create dummy objects with default values, then add your real data and subsequently delete the layouts. Not the best way, but I think there is no other ...

Answer 2

You tried to add this on top of your Id property:

 [DatabaseGenerated(DatabaseGeneratedOption.None)] public long Id { get; set; } 

Answer 3

It seems you are trying to defeat the goal of an identity column. If you want to do this, your only choice is to use the Set IDENTITY_INSERT SQL command so that you can insert this value and then run DBCC CHECKIDENT to update the seed. Not a good idea. These options have security and performance limitations.

Instead, you may want to use a GUID. You can create a GUID in code that is guaranteed to be unique, and you can also generate a GUID in SQL as the default column.

With GUIDs that are not sequential, you will need to consider a good indexing strategy. This approach is also controversial.

Ultimately, it looks like you need a different strategy than using the Identity column.

Answer 4

These are very hacks, but I came across a script when I had to do this because some report had hard-coded PK values. Recording reports was beyond my work.

 Context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.ProductCategoryDto ON " + "INSERT INTO dbo.ProductCategoryDto (Id, Name) VALUES (8, 'category1') " + "SET IDENTITY_INSERT dbo.ProductCategoryDto OFF");