How to use database procedure in fluent nhibernate mapping

Question

I have this class

public class Bill : EntityBase
{
      public virtual decimal Value { get; set; }
}

and in below mapping, I fill in the value of 'Value' using a procedure in a Formula()

public class MapBill : ClassMap<Bill>
{
    public MapBill()
    {
        Table("cabrec");
        Map(m => m.Value)
            .Formula(
"(select t.VALOR_IND from ret_vlorind(1,1,cast('02/06/1993' as Date)) as t)")
            .CustomType(typeof(decimal));
    }
}

But it returns the error when executing:

{"Dynamic SQL Error\r\nSQL error code = -104\r\nToken unknown - line 1, column 279\r\n."}

Is there any way to use procedure in fluent nhibernate?

Answer 1

The formula mapping expression is later converted by NHibernate to statement like this

// source in code
(select t.VALOR_IND from ret_vlorind(1,1,cast('02/06/1993' as Date)) as t)
// sent SQL statement
(select t.VALOR_IND from ret_vlorind(1,1,cast('02/06/1993' as Date)) as this_.t)

The this_. prefix is injected at places, where NHibernate thinks it should properly use MAIN table alias.

This is not what we want.. and the only way I found, is to introduce our own Dialect (I am working with SQL Server) and define some "CONSTANTS" to be treated as - I do not need prefix

public class CustomMsSql2012Dialect : MsSql2012Dialect
{
    public CustomMsSql2012Dialect()
    {
        RegisterKeyword("my_table_alias");
        ...

This must be used for configuration

<property name="dialect">MyNamespace.CustomMsSql2012Dialect,MyLib</property>

And finally, we have to adjust our statement

// wrong t is not known keyword.. would be prefixed
(select t.VALOR_IND from ret_vlorind(1,1,cast('02/06/1993' as Date)) as t)
// into this
(select my_table_alias.VALOR_IND from ret_vlorind(1,1,cast('02/06/1993' as Date)) as my_table_alias)

NOTE: keywords, my experience, must be just lower case