How to retrieve auto-incremented Id in ServiceStack OrmLite?

Question

For a table that has an identity:

    [AutoIncrement]
    public int Id { get; set;}

When inserting a new row into the database, what is the best way to retrieve the Id of the object?

For example:

db.Insert<> (new User());

The value of the Id is 0 after the insert, but in the database this obviously is not the case. The only possibility I can see is the following:

Id = (int)db.GetLastInsertId();

However I don't believe this would be a safe call to make. If there are 100's of inserts happening at the same time, an Id for another insert may be returned. In EF when you do an insert the Id is set for you.

Does anyone know the best way to go about this?

Answer 1

In ServiceStack.OrmLite v4 which defaults to using parameterized queries there are a couple of options in db.Save() which automatically populates the AutoIncrement Id, e.g:

db.Save(item);
item.Id //populated with the auto-incremented id

Otherwise you can select the last insert id using:

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

Here are more examples showcasing OrmLite's new API's.

---

For OrmLite v3

The correct call is db.GetLastInsertId() which for SQL Server under the hood for example calls SELECT SCOPE_IDENTITY() which returns the last inserted id for that connection.

This is safe because all the other concurrent inserts that might be happening are using different DB connections. In order for anyone else to use the same connection it needs to be disposed of and released back into the pool.

Answer 2

You should definitely using the Unit of Work pattern, particularly in this scenarios, you wrap the db related codes in a transaction scope.

In ormLite, you can implement this via IDbCommand and IDbTransaction (see example here http://code.google.com/p/servicestack/source/browse/trunk/Common/ServiceStack.OrmLite/ServiceStack.OrmLite.Tests/ShippersExample.cs)

Looking at the code, you'll notice it's going to be less magical and more manual coding, but it's one way.

Answer 3

Update: As seen here, if you are using ServiceStack/ORMLite v4, you need to utilize the parameterized query to get the inserted ID. For example:

var UserId = db.Insert<User>(new User(), selectIdentity: true);