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

Question

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

I am using C# in Visual Studio 2010 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

Did this question not help? http://stackoverflow.com/questions/2958921/entity-framework-4-how-to-find-the-primary-key — Chris Diver, Aug 12 '11 at 09:41
@Chris , I looked at it while I was searching and it did not mention anything about the next key, the question and answer seemed to me that they were talking about finding the field that is the primary key, not the next primary key that will be used in the field. — JakeJ, Aug 12 '11 at 09:48
What are you going to do with the next ID? Even if you find a way to get it you can not be sure it is **you** who gets the ID. — Mikael Eriksson, Aug 12 '11 at 09:50
@Mikael , I see your point there, the reason I need to display it is because the form that will be submitted needs to show the ID that will be used as it's going to be used as a reference number. Id doesn't HAVE to show so I could get away with leaving it for now but in the future I think it may be something needed. — JakeJ, Aug 12 '11 at 10:06
For the future you might want to take a look at a new feature (SEQUENCE) in the next version of SQL Server. Here is a great blog post by @Aaron Bertrand that also shows some techniques how to create a SEQUENCE today. https://sqlblog.org/2010/11/11/sql-server-v-next-denali-using-sequence — Mikael Eriksson, Aug 12 '11 at 11:32

score 9 · Accepted Answer · answered Aug 12 '11 at 10:52

There is no reliable way to use auto incremented ID and show it in a form before you do the save. That is wrong architecture. If you want to have ID shown before saving the form you must either:

Not use auto incremented column as ID and handle uniqueness yourselves
Save the form immediately when user starts creating it in some initial empty state and the final form confirmation will do only update

Why you cannot ask for next ID? Because if you do it in any way nobody says that received ID will be really used for your form. If another process / thread / application inserts form between your ID retrieval and your form persistence, the Id you shown will be assigned to that inserted form.

Also if you are using auto incremented primary keys in the database you cannot assign the key value in your application - the value will not be used and database can override it with its own.

score 0 · Answer 2 · answered Feb 08 '12 at 15:00

Quite agree with Ladislav Mrnka, though, this kind of functionality could be needed. One bypass suggestion would be :

        // Insert existing object in data base via EF, but PK is missing ...
        using (var Es = new MyEntities())
        {
            try
            {
                // Allows you to get next pk
                var e = new  Model.TableXXX();
                // Set pk to existing entity which has no pk
                existingEntity.PkField = e.PkField;
                // Save existing object and let garbage collector take care
                // of newly created entity
                Es.TableXXX.AddObject(existingEntity);
                Es.SaveChanges();
            }
            catch (Exception ex)
            {
                // implement exception
            }
        }

score -1 · Answer 3 · answered Aug 12 '11 at 09:38

-1

DBCC CHECKIDENT ( table_name, NORESEED )

Returns the current identity value and the current maximum value of the identity column.

answered Aug 12 '11 at 09:38

Paul Creasey

28,321
10
54
90

I need to do the command and use the returned value in the C# side of my application using Entity Framework 4, it seems like that command is to be done in the SQL Query, not Entity Framework. If I can do that through Entity Framework 4, I don't know how so you will have to tell me. – JakeJ Aug 12 '11 at 09:43

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

3 Answers3