Get CURRENT IDENTITY value of a table in linq.

Question

0.00/5 (No votes)

See more:

Hi,
I am working on a C#/.NET application where I need to get Current Identity value of the table using LINQ. Or else I can use the sql query to get the task done by either using var num = DataContext.ExecuteCommand("Select IDENT_CURRENT ('table')");, by using this code, i am getting the value = -1.
or
var num = DataContext.ExecuteQuery<short>(("Select IDENT_CURRENT ('table')");</short>, by using this code, I am getting an exception "Specified cast is not valid." and the value remains NULL. Correct me if I am doing something wrong. let me know if there are any better ways to do this task.

Thanks for ur help in advance.

Posted 29-Jun-11 6:35am

madhubabu

Updated 29-Jun-11 6:45am

Tarakeshwar Reddy

v4

Add a Solution

2 solutions

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)

#realJSOP · Answer 1 · 2011-06-29T07:23:00

Solution 1

You could probably do this instead:

SQL

select MAX(id) from table

EDIT =====================

Something just occurred to me. If you're doing this in a currently loaded dataContext, your code won't work. You have to actually query the database as opposed to your data context object. When you get a dataset back from the database, none of the table's system info comes back with it. You're going to HAVE to use SQLCommand again, or return the IDENT_CURRENT from the stored procedure as an output variable and store that for use when it's needed.

Posted 29-Jun-11 7:23am

#realJSOP

Updated 29-Jun-11 8:52am

v2

Comments

madhubabu 29-Jun-11 13:52pm

Thanks for the reply.........but my scenario is different where Last few records in the table are deleted.............So in that scenario i will not be getting the right value needed.
presently 30 records --> Max(id) gives me 30
4 records added to the table---> Max(id) gives me 34
Now i am deleting the last 4 records added this means that Current Identity value will be 34 but when i do Max(id) it gives me 30 but the correct value is 34.right?

#realJSOP 29-Jun-11 14:49pm

I see your point. See my edit.

V'vk Joshi · Answer 2 · 2015-10-02T00:38:00

Solution 2

use this:
int lastrouteid = Convert.ToInt32(routingDbContext.Database.SqlQuery<decimal>("Select IDENT_CURRENT ('Routing.RouteDescriptionTable')", new object[0]).FirstOrDefault());

Posted 2-Oct-15 0:38am

V'vk Joshi