Add a record with a pre-defined PK value in Entity Framework 4.1 (POCO)

I need to recreate a database with exactly the same values it开发者_C百科 has been originally created. So I need to add records with a pre-defined PK value. In this case, the PK is Identity in the database and when I try to define it's value, it is simply ignored, getting its value from the identity. No error is raised but the PK value that I supply is ignored.

example:

Category category = new Category()
                        {
                            CategoryID=1,
                            CategoryName="Beverages",
                            Description="Soft drinks, coffees, teas, beers, and ales"                              
                        };
ctx.Categories.Add(category);
ctx.SaveChanges();

Notes:

• I'm using POCO, code first, so, I don´t have an EDMX Model to configure.
• I don´t want to use ctx.Database.ExecuteSqlCommand(). I wish to maintain an Database agnostic approach.

In this case, the PK is Identity

In such case you should never manually insert its value. Once you set column as identity DB should be responsible for controlling the Id. Because of that there is no way to pass the value from EF (unless you want to break other functionality). You must use ExecuteSqlCommand and create complex SQL which will:

• Turn on identity insert for the table
• Insert record
• Turn off identity insert for the table

Inserting value into identity column must be allowed by SET IDENTITY_INSERT tableName ON

I don't know if you scenario will let you do this, but if you define a composite key like as follows:

modelBuilder.Entity<Category>().HasKey(s => new { s.CategoryID, s.Name });

(using HasKey while running the DbContext.OnModelCreating method and EF 4.1 Code First), then you actually can control which values get inserted when you save the POCO object to the database.

I will say that, however, I would agree with Ladislav insofar as that the primary key values you are trying to maintain here are conceptually really more like data than record identifiers, and should be treated as such. Meaning, treat them as just data fields, and create a new primary key field on your POCO class in order to uniquely identify database records. e.g. for Category

public Int32 PK {get; set;}

and be sure to indicate it's intended to be the PK field from OnModelCreating

modelBuilder.Entity<Category>().HasKey(c => c.PK)