Identity Insert just for initial Data with EF Code-First Migrations

Question

I'm currenty trying to bring some local data into a database working with EF Code-First mirgations. These data already have unique id so initially the first 'set' of data should inserted as is.

Now I'm thinking of setting 'DatabaseGeneratedOption.None' in the first place, add the dataset to the database, calculate the last used ID, reseed using the T-SQL command 'dbcc checkident (tablename, reseed, [NEW AUTO ID])' and then add another migration while reseting the GeneratedOption to 'DatabaseGeneratedOption.Identity'.

Is this the right approach and/or even possible or am I on the wrong track?

Any leads would be appreciated
Best regards

EDIT - Michals answer

Tried to implement Michals answer but still the IDs are 'generated' by the database and my explicit values are ignored. Maybe it is the way I insert the values:

MyDbContext context = // GetContext;
DbSet<SomeClass> dbSet = context.SomeClass;
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + someClassTableName + " ON");

foreach(SomeClass sc in toAdd)
{
    // sc already contains all data including its unique ID
    sbSet.Add(sc);
}

context.SaveChanges();
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + someClassTableName + " OFF");

Solution /w Michals answers

Well, I tried my best in implementing Michals answer in different ways and finally came to a (really strange) solution.

In first place I have my DBContext class and from that a dervied class called DBContextEditor. What that does is nothing else then overriding OnModelCreating where I change all 'Identity' Properties.

public class MyContextEditor : MyContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SomeClass>()
        .Property(e => e.ID)
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

Using this class in combination with some other techniques from other posts (s. sources below) finally gave me the result I was looking for.
Finally I had something like this:

MyContext context = new MyContextEditor();
DbSet<SomeClass> dataSet = context.SomeClass;

// this.Clear(dataSet); Just for testing to reset Identity Auto ID and remove all Data
// this.Reset(dataSet);

context.Database.Connection.Open();
// Add data to dataSet...
// dataSet.Add(toAdd);
// Apply Michals answer and save
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + tableName + " ON");
context.SaveChanges();
context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT " + tableName + " OFF");

// Close Connection
context.Database.Connection.Close();

I also tried to implement the override insied the MyContext class which ended in an error saying 'the baking model is different...' which also changed the the ID Property to no longer being 'identity'.

Finally, this approach gave me the desired control over adding Identity Data while keeping the possibility to let the identity values being generated. In Addition any further migration does not change the model and does not remove the 'Identity' Flag whereas the [DatabaseGeneratedOption] did removed it. It is also possible and recommended to store this inside a using like so:

using( var transaction = context.Database.BeginTransaction'))

Just for reference the Model in Pseudo-code

class SomeClass
{
    [Key]
    public int ID { get; set; }

    public string Title { get; set; }
    // More Properties
}

Related Posts and Sources

• Answer from this Post (Identity Insert)
• Another Question related to Identity Inserts

Answer 1

What I personally do is the following:

1. SET IDENTITY_INSERT sometableWithIdentity ON
2. Seed All Data
3. SET IDENTITY_INSERT sometableWithIdentity OFF.

When seeding the data, I attach all the entities to the context as added as which pre-existing Id's.

The only issue is sometimes if there is a circular reference of Foreign Key's. To get around I remove one side of the Foreign Keys's, add all entities, and put the foreign keys back.

E.g. If I have the following:

public class Customer 
{
    public List<Address> Addresses { get; set; }

    public long? DefaultAddressId { get; set; }
    public Address DefaultAddress { get; set; }
}

public class Customer 
{
    public long Id { get; set; }

    public long CustomerId { get; set; }
    public Customer Customer { get; set; }
}

I would do something like the following:

1. Unsert the Customer.DefaultAddressId
2. Attach Customer + Addresses as EntityState.New
3. Context.SaveChanges() to save to DB
4. Re-attach Customer.DefaultAddressId to tracked Customer from step 2
5. Context.SaveChanges() to save the default address Id's

The other option is to turn off referential integrity during the seed if possible. But as we use multiple databases (Sqlite for local dev, PostegreSql for prod, and sometimes MariaDb) We couldn't successfully turn it off + turn it back on + force check that there is referential integrity/