Colin Mackie - 152 - Jan 23, 2014

Just upgraded from v3 to v4.

First test hit error: seems LastInsertId for OrmLite.SqlServer always returns zero. Database has a value, and it works with MySql.

Any obvious gotchas I should be aware of?

Scott McArthur:

What does the SQL look like if you do db.GetLastSql(); ?

Does this work:
var itemId = db.Insert(item, selectIdentity:true);

Colin Mackie:


Passing true for selectidentity does return the Id. Calling LastInsertId right after still returns zero.

Colin Mackie:

I looked at the SQL (2008) profiler. My insert code is using db.Insert<T>(obj). which is getting wrapped up in a stored procedure “exec sp_executesql”, so the subsequent SELECT SCOPE_IDENTIFY()" is out of scope, and is always going to be zero.

I ran my v3 version, and the INSERT is sent directly and not wrapped in an sp, which is why it worked before.

Since Insert<T>(obj) is now paramterised, LastInsertId is never going to work.

Now I know the issue, I could search for it, and Demis closed an issue here: For me it’s a breaking change and perhaps LastIdentidId should be deprecated,

Different ways of retrieving the autoincremnt id is also answered on:

It’s still useful when inserting rows in any non-parameterized way e.g with ExecuteSql or for anyone creating high-level APIs with custom extension methods. 

Colin Mackie:

Thanks Demis. Didn’t know about Save().