Force entity framework to add identity column

Question

But what I need to know that if I set

dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[User] OFF");

before I call the SaveChanges() method will it have the same effect?, I mean why should I turn Identity to off after I call SaveChanges().

I think it is simple, you are setting the identity and once you are done with your operation, you are again resetting it to have old state for the table. :) — Hitesh, Jul 04 '17 at 08:17
I know, I'm asking here to reset it back after or before SaveChanges, do they have the same effect to the underlying database? — M Hilal, Jul 04 '17 at 08:20
The reason I'm asking before or after, because in my case I'm writing into 2 tables at the same time(the second relies on the first) and since you can't set the identity to 2 tables at the same time, I needed to first enable identity for the first table and after I finish i enable the identity for the second table then I call SaveChanges(). — M Hilal, Jul 04 '17 at 08:26

score 0 · Accepted Answer · edited Jul 04 '17 at 08:32

0

I was doing this exact thing a few days ago. Using

dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Table] ON");

and

dataContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[Table] OFF");

did nothing.

I had to use a SQL Command that turned it on, did what I wanted to do, and then turned it off within the same command.

edited Jul 04 '17 at 08:32

Chandan Kumar

answered Jul 04 '17 at 08:21

O.Bisseker

1

To me the solution in the thread I mentioned works perfectly, but here I'm asking whether to set Identity OFF after or before the call of SaveChanges. – M Hilal Jul 04 '17 at 08:30
I'll have to have another play, I couldn't get it to work. It stayed as an auto incremental number when using EF. I'd probably say after, until that point everything is held in a buffer. EF does everything in one transaction upon SaveChanges – O.Bisseker Jul 04 '17 at 08:43

1 Answers1