Select from Table Valued Function nhibernate

Question

I need a little help on this. I'm trying to use a table-valued function in a select, but I got the error that this is not mapped.

dbo.FnListEvnt is not mapped [from dbo.FnListEvnt(:dt, :id, :code) ]

Function

CREATE FUNCTION [dbo].[FnListEvnt]
(@DT DATETIME, @ID INT, @CODE VARCHAR (4)) 
RETURNS 
    @RESULTADO TABLE (
        ID            INT          ,
        DT_INIC            DATETIME     ,
        DT_TMNO            DATETIME     ,
        CD_EVNT            VARCHAR (5)  )
AS
BEGIN

Custom Dialect (this is defined in .config )

public class CustomFunctionsMsSql2008Dialect : MsSql2008Dialect
    {
        public CustomFunctionsMsSql2008Dialect()
        {
            RegisterFunction("dbo.FnListEvnt", new StandardSQLFunction("dbo.FnListEvnt", null));
        }
    }

Query

var query = Session.CreateQuery("from dbo.FnListEvnt(:dt, :id, :code) ")
                  .SetDateTime("dt", dt)
                  .SetInt32("id", id)
                  .SetString("code", code);

Answer 1

You won't be able to use RegisterFunction for this. That's for registering scalar functions.

However, you can create a named query and execute that instead. That involves a few steps though:

1. Create the named query XML file. This must end in *.hbm.xml. I find it useful to keep the name of the named query, the function, and the XML file all the same, but that's not required. Here's an example of what the XML file could look like:

   <?xml version="1.0" encoding="utf-8" ?>
   <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
     <sql-query name="FnListEvnt">
       <return-scalar column="ID" type="integer"/>
       <return-scalar column="DT_INIC" type="DateTime"/>
       <return-scalar column="DT_TMNO" type="DateTime"/>
       <return-scalar column="CD_EVNT" type="string" />
   
       select
         *
       from
         dbo.[FnListEvnt](:dt, :id, :code);
   
     </sql-query>
   </hibernate-mapping>
   

   If your query returns a mapped class (unmapped classes will not work), you can use return-class instead of a list of return-scalar elements.

2. Create a result class (unless you're returning a mapped class). Note that your class' property names must match the column names of the resultset:

   public class Result
   {
       public int ID { get; set; }
   
       public DateTime DT_TMNO { get; set; }
   
       public DateTime DT_INIC { get; set; }
   
       public string CD_EVNT { get; set; }
   }
   

3. Use GetNamedQuery to create the query, then set the parameters and execute the query:

   var results = session.GetNamedQuery("FnListEvnt")
       .SetDateTime("dt", DateTime.Now)
       .SetInt32("id", 4)
       .SetString("code", "code")
       .SetResultTransformer(Transformers.AliasToBean<Result>())
       .List<Result>();
   

That's really it. You should now be able to use your TVF with NHibernate.