How can I force entity framework to insert identity columns?

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".

You don't need to do any funny business with the connection, you can cut out the middle man and just use ObjectContext.ExecuteStoreCommand.

You could then achieve what you want by doing this:

context.ExecuteStoreCommand("SET IDENTITY_INSERT [dbo].[MyUser] ON");

I don't know of any inbuilt way of telling EF to set identity insert on though.

It's not perfect, but it'd be more flexible and less "hacky" than your current approach.

Update:

I just realised that there is a second part to your problem. Now that you've told SQL that you want to do identity inserts, EF isn't even trying to insert values for said identity (why would it? we haven't told it to).

I've not got any experience with a code first approach, but from some quick searches it seems that you need to tell EF that your column shouldn't be generated from the store. You'll need to do something like this.

Property(obj => obj.MyUserId)    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None)    .HasColumnName("MyUserId");

Hopefully this will get you pointed in the right direction :-)

Bit late to the party, but in case somebody encounters this problem in EF5 with DB first: I couldn't get either solution to work, but found another workaround:

Before the running the .SaveChanges() command, I reset the table's identity counter:

Entities.Database.ExecuteSqlCommand(String.Format("DBCC CHECKIDENT ([TableNameHere], RESEED, {0})", newObject.Id-1););Entities.YourTable.Add(newObject);Entities.SaveChanges();

This means that .SaveChanges() needs to be applied after every addition - but at least it works!