Linq to Sql - Retrieving the value of an identity column BEFORE inserting new register.

Question

Hello, I'm wondering if there is a way to retrieve the identity value from an identity column before saving a new register.

When I run this query: "SELECT IDENT_CURRENT ('alpa_Animais') AS AnimalID" directly, using the SQL Server Management Studio I get this value.

Does anyone know if there is a way to get this from a method inside a web app using Linq?

Thank you

Josi

Answer 1

You cannot - the IDENTITY is only defined and stored once the row IS inserted.

What you can do at any time is check the current state of an IDENTITY with this code:

DBCC CHECKIDENT('your-table-name')

WARNING: there is NO guarantee that you will get the numerically next number in this IDENTITY once you insert a row in a heavily used concurrent system!

Marc

UPDATE:
OK, I have to ask this: why do you need to know the value of the IDENTITY field before actually inserting the data? Why not just insert your row and then read out the IDENTITY value that SQL Server has assigned to your ID field?

In your Linq-To-SQL class diagram, can you check two things:

• the field that holds your IDENTITY ID, is it set to "Auto Generate Value = True" ?
• the field that holds your IDENTITY ID, is it set to "Auto-Sync = OnInsert" or "Auto-Sync = Always" ?

If so - just insert your data, the value assigned to your ID will be reflected in your class right away. Isn't that good enough for your app?

Answer 2

SELECT IDENT_CURRENT does not respect scope. That means it returns the last identity from any user. For example:

Thread 1: Insert a record (1)
Thread 2: Insert a record (2)
Thread 1: SELECT IDENT_CURRENT -- 2
Thread 2: SELECT IDENT_CURRENT -- 2

Both threads get the same result - this is a concurrency problem.

SQL Server provides SCOPE_IDENTITY and @@IDENTITY which limits to the current connection (so thread 1 would get the identity for the last column it inserted, not the last column inserted by anyone). @@IDENTITY will return the last identity value used by the connection, while SCOPE_IDENTITY is limited to the current scope - so you won't get odd results due to triggers or nested stored procedures.

Answer 3

Hello, I don't exacly have to get the next identity value, if I could at least retrieve the currently value, it would work, I could add + 1 before save my file. I have tryed used it in linq, not using stored procedures, but the ExecuteQuery, like that:

public IEnumerable LastAnimalID() { return ExecuteQuery(@"SELECT IDENT_CURRENT ('alpa_Animais') AS AnimalID"); }

I don't get an exception, but in the result's view it shows a message informing about an invalid casting and the only way to make this line run is returning it as IENumerable.

Some clue?

Answer 4

marc_s: I'm not working in a heavily used concurrent systemm actually I'll have just one user admin updating the site's content. Is there a way to use CHECKIDENT('your-table-name') in Linq?

David: I don't think I'll have concurrency problems since I'll have just one user admin updating the homepage. Is there a way to use @@IDENTITY from inside a linq query?

Thank you