I've been optimising some code by attempting to add lock hints and I get a different exception depending on where I put the WithLock call in my query. I initially tried this on NHibernate 5.5.2 and then updated to 5.6 to see if it resolved the issue. I get a different exception based on where I add the WithLock call and it fails wherever I put it. I can only find oblique references to the existence of WithLocks and I can't find any examples but I can't see why what I'm doing shouldn't work. . Just in case its pertinent this is on Windows using MySql.

This query works:

var pages = session.Query<Page>() .Where(x => x.File.Id == lookupFile.Id) .OrderBy(x => x.PageNumber) .Select(x => new PageDto { Id = x.Id, PageNumber = x.PageNumber, Status = x.Status, }) .ToList(); 

If I add locking after the where clause:

var pages = session.Query<Page>() .Where(x => x.File.Id == lookupFile.Id) .WithLock(LockMode.Upgrade) .OrderBy(x => x.PageNumber) .Select(x => new PageDto { Id = x.Id, PageNumber = x.PageNumber, Status = x.Status, }) .ToList(); 

It throws:

System.InvalidOperationException: could not locate alias to apply lock mode : x

Note that I get a different exception from each of the 4 possible places to put the WithLock call.

If I remove the Select clause entirely and retrieve the whole entity then the query doesn't throw an exception.

var pages = session.Query<Page>() .Where(x => x.File.Id == lookupFile.Id) .WithLock(LockMode.Upgrade) .OrderBy(x => x.PageNumber) .ToList(); 

I don't want the memory overhead of loading the whole row if I can avoid it but not sure if this is a bug or a deliberate NHibernate design with opaque error handling.