Able to Select with NOLOCK

ServiceStack.OrmLite SQL Server
1

I am wrapping my connecting within a transaction, where there are some reads (selects). However, some of these selects could be with NOLOCK.
Is there any possibility to specify this in the Select?

2

The latest v5.7.1 on MyGet you can use the new NoLock SQL Server Table Hint, e.g:

var q = db.From<Car>()
    .Join<Car, CarType>((c, t) => c.CarId == t.CarId, SqlServerTableHint.NoLock);

Otherwise you can customize the generated SQL with a Custom SqlExpression Filter,

1 Like
3

Thanks! Exactly what I needed!

4

It’s a bit of an older post, but wondering if anyone else has come across the Custom SQL Filter WithSqlFilter being appended “after” the Where clause? This means that if using it for “with (nolock)”, you get select * from <table> where id = x with (nolock) instead of the with being before the where.

var q = Db.From<Foo>().Where(x => x.Id ==1).WithSqlFilter(sql => sql + " with (nolock)";
return Db.Select(q);

Would almost need a WithSqlFromFilter. Alternatively, I might need to write a dbconn ext method for something like a Db.SelectWithNoLock(q)

Thoughts?

thanks

5

I personally wouldn’t hide the query but just have a static custom delegate I’d reuse, e.g:

var q = Db.From<Foo>().Where(x => x.Id ==1)
    .WithSqlFilter(MySqlFilters.WithNoLock);

Or if you need to do this a lot, have the ext method on SqlExpression<T> instead, e.g:

var q = Db.From<Foo>().Where(x => x.Id ==1).WithNoLock();
6

Agree with both of those approaches - it’s exactly what I was testing; however, it doesn’t work for the with (nolock) example because the sql is appended after the where clause. The sql would need to read:

select * from Foo WITH (NOLOCK) where...

The only way for now I’ve found is to create an ext method on the dbcon, but downside is it means transactions for standard select statements.

public static List<T> SelectWithNoLock<T>(this IDbConnection db, SqlExpression<T> expression)
{
    using (var tran = db.OpenTransaction(System.Data.IsolationLevel.ReadUncommitted))
    {
        return db.Select(expression);
     }
}
1 Like