How can I get the next Primary Key for SQL 2008 in Entity Framework 5

Question

4.00/5 (1 vote)

See more:

, +

I am using an Entity Framework 5 connection to connect to a SQL 2008 server to do database handling for my application.

I am using MVC4 in Visual Studio 2013 and my question is regarding Primary Key fields that are incremented by the SQL itself, is it possible at all for me to be able to find the ID that will be used next by the database.

The reason why I cannot just find the last item and +1 is because if my table contains items 1,2,3,4 and 5, removing item 5 then adding another will make the next item become item 6, rather than 5 again (As I use the Identity Specification in SQL, but this must be used).

I cannot find any method such as Item.ID.GetNextIdentity() or something like that and have looked through as many similar questions like this but to no avail.

Any help would be appreciated

Thanks in Advance

Krishanpal Singh

Posted 15-Oct-14 23:55pm

KP Singh Chundawat

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2014-10-16T01:06:00

Solution 1

The answer is very simple. You can't. Because of the web inherent parallel access you cannot query the next ID and then count on it being available at the time of the saving data.

What you can do however is query additional table COUNTERS in which you will keep current last saved item and each time the table is queried for new ID it should really insert one entry which will have ID and empty data (NULLS) which you will fill later and that same get will update counters.

This is by no means full proof and you will end up with empty rows if users decide to cancel before commiting.

In general, you should't need that value for user interactions - it should be auto-added on your insert and then the grid refreshed (at which point you will have ID for updates). If you don't want to refresh whole grid at least return scalar value of new ID from the insert and update your current row) - if you have to show some number to the user, use non-PK number that you control (and that CAN be max +1).

If this helps, please take time to accept the solution.

Posted 16-Oct-14 1:06am

Sinisa Hajnal

Comments

KP Singh Chundawat 16-Oct-14 9:15am

ok Thank you Sinisa Hajnal ..if it is possible in SQL server, if yes then How? Let me know please.
Thanks once Again
Krishanpal Singh

Sinisa Hajnal 16-Oct-14 9:27am

It is possible, just as I described.
your table needs to have all but PK columns nullable and stored procedure querying next ID essentially does insert, you return new ID to UI and work with that, if the user fails to save, you get empty or partially empty row

Another route is to have COUNTER table - you reserve the number by picking it and increasing the counter for the next user. BUT then you cannot have auto-increment column since that second user could save before the first one. Thus, you will have unique numbers, but not neccessarily in order - later number can be saved before earlier or some numbers may not get saved at all

Finally, you could redesign your pages and routines so that user never sees auto-increment number which is only used internally. That value is saved when the user saves and at no other times...thus you have order kept, only skipped numbers will be deleted ones. Drawback is that if you need some number to show to the users, you need another column for which you have full control on number generation - now you can use COUNTER table without consideration of the save order.

KP Singh Chundawat 17-Oct-14 2:06am

I got Solution Sinisa Hanjal. if you run this query then you get next identity value Select IDENT_CURRENT('YourtableName')+IDENT_INCR('YourTablename') as NecxtPrimeryIdentityValue

Thanks

Sinisa Hajnal 17-Oct-14 2:16am

You still don't understand. That will NOT solve your problem.
Imagine a man in Australia and another in India. Both access your page within seconds. Now they both query IDENT_CURRENT + IDENT_INR and get the same number - since neither of them saved anything.

Now they both work at whatever it is your page does...and save. First one to save will indeed get the number he sees on the screen...the other one will (depending on your logic) either insert and get new ID number or worse, update via ID the item of the previous user.

It gets worse if they query the table while third user is almost done (and he got the same number too!) and he saves some time before these two finish. Now you have the situation where neither of the users get the number they queried for or worse, overwrite one item over and over again.