ServiceStack OrmLite Select with update lock

Question

I have the following code:

protected static void InsertOrUpdate<T>(
    IDbConnection connection,
    T item,
    Expression<Func<T, bool>> singleItemPredicate,
    Expression<Func<T, object>> updateOnlyFields = null)
{
    var type = item.GetType();
    var idProperty = type.GetProperty("Id");
    var currentId = (int)idProperty.GetValue(item);
    if (currentId != 0)
    {
        throw new ApplicationException("Cannot insert or update with non-zero ID");
    }

    var existingItem = connection.Select(singleItemPredicate).SingleOrDefault();
    if (existingItem == null)
    {
        Insert(connection, item);
        return;
    }

    var existingId = (int)idProperty.GetValue(existingItem);
    idProperty.SetValue(item, existingId);

    var affectedRowCount = connection.UpdateOnly(item, onlyFields: updateOnlyFields, where: singleItemPredicate);
    if (affectedRowCount != 1)
    {
        throw new ApplicationException("Update failed");
    }
}

But using default isolation level, this may produce errors with multiple threads working on the same table.

What I would like is the select-row to specify that I want an update lock, something like:

var existingItem = connection.Select(singleItemPredicate).WithUpdateLock().SingleOrDefault();

• For SQL Server, this would add WITH UPDLOCK at the end of the SELECT row

How would I best go about this in ServiceStack OrmLite?

Answer 1

You could use the new SqlExpression SQL Filter just added in v4.5.5 that's now (available on MyGet) to customize the generated SQL then execute the custom Typed SqlExpression, e.g:

var q = connection.From<T>()
    .Where(singleItemPredicate)
    .WithSqlFilter(sql => sql + " WITH UPDLOCK");

var existingItem = connection.Select(q).SingleOrDefault();
if (existingItem == null)
{
    connection.Insert(item);
    return;
}