ef code first - IDENTITY_INSERT ON not being respected for Entity Framework DBSet.Add method

Question

I run this query:

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

just before I do the .Add() method from a 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 Server 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.