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.

Similar question to stackoverflow.com/questions/13086006/… — Jez
– Jez, Commented Oct 26, 2012 at 13:12

Ladislav Mrnka · Accepted Answer · 2011-02-15 10:00:42Z

4

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.

edited Feb 15, 2011 at 10:00

answered Feb 15, 2011 at 9:37

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego Mijelshon · Accepted Answer · 2011-02-15 13:10:30Z

3

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.

answered Feb 15, 2011 at 13:10

Diego Mijelshon

52.7k16 gold badges119 silver badges159 bronze badges

2 Comments

ajma Over a year ago

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.

Diego Mijelshon Over a year ago

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

Collectives™ on Stack Overflow

IDENTITY_INSERT ON not being respected for Entity Framework DBSet.Add method

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related