Auto-increment IDs after importing data in the Entity Framework

Question

I'm developing a new version of an old application using Entity Framework 6 Code First and ASP.NET with SQL Server 2014 as the back end. The old database is an Access database and has about a dozen tables with auto-increment IDs. I want to import the old data while keeping the IDs intact so that the relationships between the tables is preserved. I've set the key attribute on the new entities to

DatabaseGeneratedOption.None

This works, and I'm able to import all of the values from the old database, however I'd like to have the primary keys auto-increment from this point on. Since the DatabaseGeneratedOption is set to None it seems that I have to manually generate IDs in

You *can* add new entities to EF with adhering entities. If yo do that in one `SaveChanges` action, EF will use the auto-generated Id values as FK values. Of course this is only doable if the database is not too large, or if you can copy the data in clearly distinct sets. — Gert Arnold, May 24 '16 at 20:37

score 4 · Accepted Answer · answered May 24 '16 at 20:43

4

You probably want to set the keys as identity as they were and then disable the identity-constraint during import.

You do this with SET IDENTITY_INSERT tablename ON before the insert statement. Dont forget to set it to OFF when you are done.

answered May 24 '16 at 20:43

Mattias Åslund

3,877
2
18
17

I was importing using the Entity Framework Code First (i.e. NewClient.ID = reader("ClientID")). Is there a way to do this through the Entity Framework or will I need to script it using SQL commands? I'm fine using SQL commands, however I will have to refactor some of my code. – Nse May 24 '16 at 21:26
1

@Nse It's straightforward to run SQL in a migration: `Sql("SET IDENTITY_INSERT tablename ON")`. Also, be aware that it may not be as straightforward as you might expect to alter a column to IDENTITY once it has been created http://stackoverflow.com/a/18917348/150342 – Colin May 25 '16 at 08:32

score 0 · Answer 2 · answered May 24 '16 at 20:44

I never tried this, but you can have different configurations and switch them as per requirement/environment, hope this helps

public class IdentityDbConfiguration : EntityTypeConfiguration<Foo>
{
    public IdentityDbConfiguration()
    {
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    }
}

public class DbConfiguration : EntityTypeConfiguration<Foo>
{
    public DbConfiguration()
    {
        Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

public class AppContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new IdentityDbConfiguration());
    }
}

public class AppContext2 : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new DbConfiguration());
    }
}

Auto-increment IDs after importing data in the Entity Framework

2 Answers2