A recognition error occurred when nolock is used in hibernate query

Question

I have simple hibernate query with nolock used in query. I am getting error - A recognition error occurred and hibernate error is Exception thrown: 'NHibernate.Hql.Ast.ANTLR.QuerySyntaxException' in NHibernate.dll. it works without nolock. I don't want to use

<property name="connection.isolation">ReadUncommitted</property>

becasue I have to apply nolock to specific table only.

below is hql query -

select d from Users d with (nolock) where d.Userid = 2 

Am I missing anything?

Answer 1

HQL will not support direct with (nolock). But we can use native SQL query.

So, for example, instead of something like this (getting list of users):

var hql    = "select d from Users d with (nolock) where d.Userid = 2";
var query  = session.CreateQuery(sql);
var result = query.List<User>();

we would need to use raw sql API

var sql    = "select d.* from schema.UserTable d with (nolock) where d.user_id = 2 ";
var query  = session.CreateSQLQuery(sql);
var result = query
    .AddEntity(typeof(User))
    .List<User>();

In case, we know that by ID only one user will be returned.. we can use UniqueResult<> instead of List