Combine UDFs in a criteria.

itsiksaban · **Joined:** Mon Dec 24, 2007 2:04 am **Posts:** 6

Hello everybody,

I would like to use a UDF (an SQL User Defined Function) within a criteria.
I know how to do it with HQL:

"from Customer c where dbo.some_udf(c.id) = [something]"

I don't know how to do it using an ICriteria. I would love to see an example.

Regards.

wolli · **Posted:** Sun Mar 23, 2008 8:34 am

You can use a custom sql expression in a criteria, like

Code:

ICriteria crit = session.CreateCriteria( typeof( Customer), "c" );
crit.Add( Expression.Sql( "dbo.some_udf(c.id) = ?", something , NHibernateUtil.Int32 ) );

itsiksaban · **Joined:** Mon Dec 24, 2007 2:04 am **Posts:** 6

Hi Wolli,

I have tried your code, but it did not work.
It throws an ADO exception and in the generated SQL I see that it uses: "dbo.some_udf(c.id)" even though the alias in the SQL for Customer is not 'c' but 'this_'...
So, the expected syntax is "dbo.some_udf(this_.id)"...

wolli · **Posted:** Tue Mar 25, 2008 3:24 am

Probably the sql expression is put into the generated sql "as is" without substitution. Try to call the function without the alias.

wolli · **Posted:** Fri Mar 28, 2008 4:21 am

I just was remembered of how to specifiy the alias there:

Code:

ICriteria crit = session.CreateCriteria( typeof( Customer), "c" ); 
crit.Add( Expression.Sql( "dbo.some_udf({alias}.id) = ?", something , NHibernateUtil.Int32 ) );