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

EF 6 method, using the msdn article:

using (var dataContext = new DataModelContainer())
using (var transaction = dataContext.Database.BeginTransaction())
{
    var user = new User()
    {
        ID = id,
        Name = "John"
    };

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] ON");

    dataContext.User.Add(user);
    dataContext.SaveChanges();

    dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

    transaction.Commit();
}

Update: To avoid error "Explicit value must be specified for identity column in table 'TableName' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column", you should change value of StoreGeneratedPattern property of identity column from Identity to None in model designer.

Note, changing of StoreGeneratedPattern to None will fail inserting of object without specified id (normal way) with error "Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF".