5

I have a question about the Behavior of Entity Framework when SaveChanges method is executed.

I have an entity that has an Identity Column, and just realize that when if I call SaveChanges (for a new insert), and it fails then my Identity Column in my DB is increased.

I used to have the Identity column value equals 7, and I was debugging my code and SaveChanges method failed 5 times, because I missed to insert a required value, and when it worked out I noticed that my identity column value was now 13.

enter image description here

Is this a normal behavior? Is there a way to avoid increasing identity column value when Entity Framework fail to do a commit into my DB?

halfer
  • 19,824
  • 17
  • 99
  • 186
VAAA
  • 14,531
  • 28
  • 130
  • 253
  • 3
    This is nothing to do with EF, when any insert fails on a table with an identity, it gets incremented anyway, just try it in sql management studio. Why do you care? – Ben Robinson Oct 02 '14 at 15:56
  • Ok interesting. Is there a way to avoid this behavoir or is something normal? – VAAA Oct 02 '14 at 15:56
  • 3
    I don't think so, its a design feature. Imagine a long transaction started, then another transaction starts, it would be bad if the second one had to wait to see if the first one succeed or not to determine what identity it should use. As I mentioned previously why do you care if your ids are not sequential? – Ben Robinson Oct 02 '14 at 16:01
  • Thanks Bed, any way I can handle a unique sequential column (I need this to show on reports and the user get confused when the last report had Folio 7 and the next one Folio 13. – VAAA Oct 02 '14 at 16:04
  • @VAAA You could manually manage a sequential # column, but without a trigger or something similar you'd have the same issue once you delete some rows. Is there something more descriptive you can use to describe each Folio, other than the ID value? – Brian Driscoll Oct 02 '14 at 16:24
  • @BrianDriscoll Folio is just a int number and it must be sequential, so I wont allow any gap. – VAAA Oct 02 '14 at 16:25
  • 2
    @VAAA I hope you won't take this the wrong way, but that's a very brittle approach and IMHO it's poor design. Let's say you go ahead with this and at some point you have to update the numbers due to a deletion. So what was "Folio 8" last week is now "Folio 7". Wouldn't that be confusing to the user? – Brian Driscoll Oct 02 '14 at 16:27
  • @BrianDriscoll Totally right, so what do you recommend me to use? a Sequence? – VAAA Oct 02 '14 at 16:29
  • 2
    Don't confuse the Identity of a row with displaying number of rows. If the ID is 13, then make sure the user can see 13. If you want to display sequential numbers for the user experience you could use [Row_Number](http://msdn.microsoft.com/en-us/library/ms186734.aspx). – Erik Philips Oct 02 '14 at 16:32
  • Identity values are for internal use only. Don't connect any business meaning to them. There are case where consecutive numbers are necessary (e.g. invoice numbers). That always requires some hand-written mechanism that's concurrency-proof and transaction-sensitive. Maybe [this question](http://stackoverflow.com/q/20469208/861716) gives some ideas. – Gert Arnold Oct 02 '14 at 21:15

1 Answers1

1

This is Sql's behaviour and not Entity Framework.You should have a look at Dbreseed command and execute it for your table if you don't want to Identity to be wasted.check this So question RESEED identity columns on the database If I were you I wouldn't have bothered.Instead just use Bigint datatype for the identity column

Community
  • 1
  • 1
Pavitar
  • 4,282
  • 10
  • 50
  • 82