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);
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:If your query returns a mapped class (unmapped classes will not work), you can use
return-class
instead of a list ofreturn-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:
Use
GetNamedQuery
to create the query, then set the parameters and execute the query:That's really it. You should now be able to use your TVF with NHibernate.