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

I'm fairly sure RegisterFunction is only useful for scalar valued functions. You may have to use a named query--is that acceptable? — Andrew Whitaker
– Andrew Whitaker, Commented Jun 12, 2015 at 21:19

Andrew Whitaker · Accepted Answer · 2015-06-12 22:09:03Z

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:

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.

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

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.

Answers like this (I really enjoy it, I do ;) make me feel more desperate... that the NHibernate age is over... such a shame... because we/them could do so much with it...

Collectives™ on Stack Overflow

Select from Table Valued Function nhibernate

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related