Select from the table Valuable function nhibernate

Question

I need a little help with this. I am trying to use the table-value function in select, but I got an error that this is not displayed.

dbo.FnListEvnt is not displayed [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));
        }
    }

Inquiry

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

Answer 1

You cannot use RegisterFunctionfor this. This is for registering scalar functions.

However, you can create a named query and execute it. This includes a few steps:

• XML . *.hbm.xml. , XML , . , XML :

  <?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>
  

  (unmapped classes ), return-class return-scalar.

• ( ). , :

  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; }
  }
  

• GetNamedQuery , :

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

. TVF NHibernate.