IDENTITY_INSERT ON not being respected for Entity

Question

context.Database.SqlCommand("SET IDENTITY_INSERT [Songs] ON;");

just before I do the Add method (the Add method from DbSet).

My data still is using the identity value instead of the ID that I'm supplying.

I only want this temporarily (for data migration) and then it should remain auto generated identity in the future.

I'm using SQL CE 4 for my database.

Answer 1

This can be a problem. DbContext handles db connection itself. This works only if inserts are executed on the same connection as turning on identity insert. I think it releases the connection after this command. You can try to use different DbContext's constructor and pass your own opened DbConnection instance and handle its closing by yourselves.

Answer 2

EF can't guess that you've executed an IDENTITY_SET command and change its internal logic accordingly.

If you need to insert specific Id values, use SqlCommand for the inserts too.