How to retrieve last insert id from database

Question

I inserted the data into a table using Entity Framework but now I need to retrieve the last id from last executed query.

Here is my code

var filedbdata = "'" + FileTitle + "'" + "," +
                 "'" + path + "'" + "," +
                 "'" + Session["UserID"] + "'";

var query = "insert into File_Upload(file_title,file_loc,file_userid) values(" + filedbdata + ")";

db.Database.ExecuteSqlCommand(query);
db.SaveChanges();

int lastid = ??

Why string concatenation if you can use safe sql parameters? — Tim Schmelter, Jan 21 '16 at 09:33
Not sure on the code exactly, but you should be able to order your data by descending for the id using .OrderByDescending and then select the first item in this list. — James, Jan 21 '16 at 09:34
And why are you using EntityFramework if you're building simple SQL queries like that manually anyways? See http://stackoverflow.com/questions/5212751/how-can-i-get-id-of-inserted-entity-in-entity-framework for an answer to your questoin, and an example of how to code it. If you want to return the newest ID from your query, add `SELECT SCOPE_IDENTITY()` to it. — Arve Systad, Jan 21 '16 at 09:34
It seems you have Auto Generated ID than just fetch the max(id) — Mohit S, Jan 21 '16 at 09:34
What exacly `lastid` means? Is it an auto generated id or something? — Irshad, Jan 21 '16 at 09:35
What RDBMS? I don't know if entity framework have some built in property for this, but to get the last auto generated id from the database is dependent on the RDBMS, and also, the phrase "the last auto generated id" itself is ambiguous at best. — Zohar Peled, Jan 21 '16 at 09:36
@MohitShrivastava: Bad idea. what if between the insert and select there was another insert from a different source? — Zohar Peled, Jan 21 '16 at 09:37
A good way to follow EF; http://stackoverflow.com/a/26053354/2130976 — Irshad, Jan 21 '16 at 09:37
@MaheshMalpani scope_identity() has it's limitations. for instance, if your target table have an instead of insert trigger, scope_identity() will not give you the correct number. — Zohar Peled, Jan 21 '16 at 09:38
if you would actually **USE** Entity Framework, create a new object and save that - EF would give you back the newly inserted `Id` for free - just like that ..... **USE** EF ! — marc_s, Jan 21 '16 at 09:47

score 3 · Answer 1 · answered Jan 21 '16 at 09:50

If you would be actually using EF by creating a new object and storing it, you'd get back the new Id from EF - for free, just like that!

// create new object to insert
FileUpload fu = new FileUpload();
fu.file_title = FileTitle;
fu.file_loc = path;
fu.file_userid = Session["UserID"];

// add new object to database context and save it
db.FileUploads.Add(fu);
db.SaveChanges();

// you get back the newly created "ID" FOR FREE!
int lastid = fu.file_id;

If you're using Entity Framework - use EF and stop doing everything the hard way in "raw", low-level SQL

Rahul Tripathi · Answer 2 · 2016-01-21T09:44:39.073

1

You can try to use OUTPUT like this:

OUTPUT Clause

Returns inserted rows as part of the insert operation. The results can be returned to the processing application or inserted into a table or table variable for further processing.

var query = "insert into File_Upload(file_title,file_loc,file_userid) OUTPUT INSERTED.ID values(" + filedbdata + ")";

And to retrieve it you can simply use

int id = (int)command.ExecuteScalar

Also it would be better to use parameterized query instead of using string concatenation. It is a standard practice to avoid SQL Injection.

edited Jan 21 '16 at 09:44

answered Jan 21 '16 at 09:39

Rahul Tripathi

168,305
31
280
331

+1 I liked this idea but I would like you to read [parameterized SQL: Comment by Jon Skeet](http://stackoverflow.com/questions/34848805/getting-value-from-a-database-using-textbox/34848901#comment57437927_34848901) – Mohit S Jan 21 '16 at 09:42
@MohitShrivastava:- Added the note to it. – Rahul Tripathi Jan 21 '16 at 09:44

How to retrieve last insert id from database

2 Answers2