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.

Phraseologist

Similar question to #13086506 – Raisin

Answer 1

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.

Zooid

This is what I ended up doing. I had to use SqlCeCommand. I wanted to keep my code agnostic of the database, but I guess I can't. – Phraseologist

Well, SET IDENTITY_INSERT isn't particularly db-agnostic anyway :-) – Zooid

Answer 2

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.

Acceptance