2

I need to recreate a database with exactly the same values it 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.
outlookrperson
  • 2,761
  • 7
  • 32
  • 49
  • Why are you doing data migration in code? Are you writing a data migration app? If you are just bringing in a bunch of data then why not just script it all in SQL. I suspect you can't do this in a database agnostic way as different RDBMS will use different way of doing identity inserts – Ben Robinson Jun 29 '11 at 12:49
  • 1
    Does this help http://stackoverflow.com/questions/3332003/using-identity-insert-with-ef4 – Ben Robinson Jun 29 '11 at 12:52
  • @Ben Robinson, lets change the question: I need to recreate the database with exactly the same values. – outlookrperson Jun 29 '11 at 13:26
  • @Ben RObinson, I´m using EF 4.1 POCO (Code First), I cannot use StoreGeneratedPattern in SSDL, as proposed in your link, because I don´t have it! – outlookrperson Jun 29 '11 at 13:33

2 Answers2

0

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

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • actually I need to recreate the database exactly with the same values it has been originally created. But I don´t know the underlying database (so I need an agnostic database approach). – outlookrperson Jun 29 '11 at 13:43
  • If you don't know the underlying database you cannot use EF anyway. What about backup / restore (it can be scripted with TSQL) instead of creating some custom solution? – Ladislav Mrnka Jun 29 '11 at 14:12
  • I´m using a Repository pattern. The database implementation (EF + Sql Server, NHibernate + Sql Server, etc) is defined in configuration file and the access to all database methods is done using an interface IRepository. It is what I mean with "I don´t know the underling database". – outlookrperson Jun 29 '11 at 15:07
0

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)
T. Webster
  • 9,605
  • 6
  • 67
  • 94