Entity Framework SQL Server increments an Id Key out of order if previous values are available
I have an MVC4 website and it has a SQL Server DB that contains its posts. On each post there is a Next and Previous button. These buttons are calculated from databaseContext.Post.Count() plus or minus 1 and (if item = zero don't display Previous, and the other way round.) However due to some Posts being deleted there are some spots in between that are null. This doesn't bother me except now for some reason the DB has been adding new entries to these empty spots, so the Count() vs what is actualy their is off and this causes the highest value's next to point to a higher value than what actually exists (since it is over the total count, keep in mind that even its Id is over the total count.)
I know I can programmatically check for this type of issue however I was wondering if there a way to change how the Id keys are created?
You are assuming that a generated ID will be dense monotonous, with no gaps. This is incorrect assumption. There will always be gaps in the generated IDs. You can never assume that counting the records will give you valid IDs. If you want a dense, monotonous row number use the dedicated ROW_NUMBER function, but leveraging this for navigation is problematic.