Nhibernate formula appending template word to sql keyword

Question

I am using the following formula in my mapping file :

select top 1 SI.ID from Table SI with(nolock) where SI.GUID = GUID And SI.IsArchive = '0'

the genrated sql is : select top 1 SI.ID from Table SI with(this_.nolock) where SI.GUID = this_.GUID And SI.IsArchive = '0'

The nolock is a keyword. I don't want it to be qualified with this_.(template keyword).

how can I change this behaviour ?

Answer 1

It may be possible to work around this with query substitutions in your nhibernate config; for example:

<property name="hibernate.query.substitutions">mynolock nolock</property>

And map as such:

select top 1 SI.ID from Table SI with(mynolock) where SI.GUID = GUID And SI.IsArchive = '0'

Answer 2

You can try to refactor the formula logic into a user-defined function (assuming this is sql server here) and call that directly from the formula. You could even make the function part of your schema generation by using the database-object mapping.

For example:

    CREATE FUNCTION [dbo].[GetMyValue] ( @entityId INT ) 
    RETURNS INT
    AS BEGIN
      DECLARE @RtnValue AS INT 
      SELECT @RtnValue = top 1 SI.ID from Table SI with(nolock) where SI.id = @entityId AND SI.IsArchive = '0'
      RETURN @RtnValue
    END

And map as such

formula="dbo.GetMyValue(id)"

That should, in theory, prevent any sort of keyword replacement from happening.