How can I force entity framework to insert identity columns?

Question

I want to write some C# code to initialize my database with some seed data. Clearly, this is going to require the ability to be able to set the values of various Identity columns when inserting. I'm using a code-first approach. By default, DbContext handles the database connection and so you can't SET IDENTITY_INSERT [dbo].[MyTable] ON. So, what I've done so far is use the DbContext constructor that lets me specify a DB connection to be used. Then, I set IDENTITY_INSERT to ON in that DB connection, and then try to insert my records using entity framework. Here's an example of what I've got so far:

public class MyUserSeeder : IEntitySeeder {
    public void InitializeEntities(AssessmentSystemContext context, SqlConnection connection) {
        context.MyUsers.Add(new MyUser { MyUserId = 106, ConceptPersonId = 520476, Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginId = "520476", Password="28c923d21b68fdf129b46de949b9f7e0d03f6ced8e9404066f4f3a75e115147489c9f68195c2128e320ca9018cd711df", IsEnabled = true, SpecialRequirements = null });
        try {
            connection.Open();
            SqlCommand cmd = new SqlCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON", connection);
            int retVal = cmd.ExecuteNonQuery();
            context.SaveChanges();
        }
        finally {
            connection.Close();
        }
    }
}

So close and yet so far - because, although cmd.ExecuteNonQuery() works fine, when I then run context.SaveChanges(), I'm informed that "Explicit value must be specified for identity column in table 'MyUser' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column."

Presumably, because MyUserId (which is the Identity column in the MyUser table) is the primary key, entity framework doesn't try to set it when I call context.SaveChanges(), even though I gave the MyUser entity a value for the MyUserId property.

Is there a way to force entity framework to try and insert even primary key values for an entity, then? Or maybe a way to temporarily mark MyUserId as not being a primary key value, so EF tries to insert it?

Answer 1

After careful consideration, I've decided that entity framework's refusal to insert identity columns is a feature, not a bug. :-) If I were to be inserting all entries in my database including their identity values, I'd also have to create an entity for every link table that entity framework had created automatically for me! It's just not the right approach.

So what I'm doing is setting up seeding classes that just use C# code and create EF entities, then use a DbContext to save the newly-created data. It takes a bit longer to take the dumped SQL and turn it into C# code, but there isn't (and shouldn't be) too much data just for "seeding" data - it should be a smallish amount of data which is representative of the kind of data that would be in a live DB that can quickly be put into a fresh DB for debugging/development purposes. This does mean that if I want to link entities together, I do have to do queries on what has already been inserted or my code wouldn't know their generated identity value, eg. This kind of thing will appear within the seeding code, after I have set up and done context.SaveChanges for MyRoles:

var roleBasic = context.MyRoles.Where(rl => rl.Name == "Basic").First();
var roleAdmin = context.MyRoles.Where(rl => rl.Name == "Admin").First();
var roleContentAuthor = context.MyRoles.Where(rl => rl.Name == "ContentAuthor").First();

MyUser thisUser = context.MyUsers.Add(new MyUser {
    Salutation = "Mrs", Firstname = "Novelette", Surname = "Aldred", Email = null, LoginUsername = "naldred", Password="c1c966821b68fdf129c46de949b9f7e0d03f6cad8ea404066f4f3a75e11514748ac9f68695c2128e520ca0275cd711df", IsEnabled = true, SpecialRequirements = null
});
thisUser.Roles.Add(roleBasic);

Doing it this way also makes it more likely I will update my seeding data when I change the schema, because I will likely break the seeding code when I change it (if I remove a field or entity, the existing seeding code that uses that field/entity will fail to compile). With a SQL script for doing seeding, that wouldn't be the case, and nor would the SQL script be database-agnostic.

So I think that if you're trying to set the identity fields of entities for doing DB seeding data, you've definitely taken the wrong approach.

If I were actually dragging a load of data from say SQL Server to PostgreSQL (a full live DB, not just some seeding data), I could do it via EF, but I'd want to have two contexts open at the same time, and write some code to grab all the various entities from the source context and put them into the destination context, then save changes.

Generally, the only time it's appropriate to insert identity values is when you're copying from one DB to another DB within the same DBMS (SQL Server -> SQL Server, PostgreSQL -> PostgreSQL, etc.), and then you'd do it in a SQL script and not EF code-first (the SQL script wouldn't be DB-agnostic, but it wouldn't need to be; you're not going between different DBMSs).