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:

GetLastSql is "SELECT SCOPE_IDENTITY()"

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: https://github.com/ServiceStack/Issues/issues/12. For me it’s a breaking change and perhaps LastIdentidId should be deprecated,

Different ways of retrieving the autoincremnt id is also answered on: http://stackoverflow.com/a/21202459/85785

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().