.NET Core - ExecuteSqlRaw - last inserted id?

Question

This seems silly, but I cannot find any example code that shows how to retrieve the last inserted id when doing an insert query with ExecuteSqlRaw

var sql = @"INSERT INTO [MyDb-Dev].[dbo].[" + table + "] (...) VALUES (...)";
_context.Database.ExecuteSqlRaw(sql);

How do I retrieve the last inserted id ?

Thanks for your help

Pretty sure you can do this with an output parameter. Have a look at this link: https://www.yogihosting.com/stored-procedures-entity-framework-core/ — Andrew, Dec 10 '20 at 12:36
but how do you get the id of the inserted record to be stored in the output parameter? — phil123456, Dec 10 '20 at 12:40

Emin Mesic · Answer 1 · 2020-12-10T13:16:51.727

0

You can get inserted id with OUTPUT in MS SQL Server.

OUTPUT returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements.

var sql = @"INSERT INTO [MyDb-Dev].[dbo].[" + table + "] (...) OUTPUT inserted.Id VALUES (...)";
int returnValue = _context.Database.SqlQuery<int>(sql).FirstOrDefault();

edited Dec 10 '20 at 13:16

answered Dec 10 '20 at 12:30

Emin Mesic

1,681
2
8
18

it does not work, I insert a record, forcing the Id, it should be 23 (the id in db too), the return value is 1 – phil123456 Dec 10 '20 at 12:50
it does so for all inserted records – phil123456 Dec 10 '20 at 12:54
I tried using ExecuteSqlCommand, same issue, while testing a dummy query in SQL returns the correct value...is there a bug somewhere in the API? – phil123456 Dec 10 '20 at 13:06
@phil123456 I have updated my answer, instead of ExecuteSqlCommand you can use SqlQuery and cast value into int. – Emin Mesic Dec 10 '20 at 13:17

score 0 · Answer 2 · answered Dec 10 '20 at 13:22

0

add this to the query

;SELECT @LastCreatedId = CAST(SCOPE_IDENTITY() as int)

add a parameter to the call

var returnCode = new SqlParameter("@LastCreatedId", SqlDbType.Int);
returnCode.Direction = ParameterDirection.Output;

get the value like this

_context.Database.ExecuteSqlCommand(sql3, parameters);
returnValue = (int)returnCode.Value;

answered Dec 10 '20 at 13:22

phil123456

1,192
2
10
26

This works only if there is property in table with identity on it ... – Kadaj Sep 29 '21 at 12:54

.NET Core - ExecuteSqlRaw - last inserted id?

2 Answers2

Linked