WHERE 1=fnCheckSomething(...)

devvvy · **Joined:** Mon Jan 05, 2009 7:08 am **Posts:** 24

hello

How can I add Criteria to retrieve an entity with a WHERE clause which involves a function call?

Code:

select * from Person WHERE ... AND 1=fnCheckSomething(Person.Id, OwnerId)

btw, for our app, fnCheckSomething is only implemented for Oracle and M$SQL. For MySQL, because it can't do recursive function calls, it's in form of a stored proc which makes it even more difficult. At the momet, we've resorted to hand crafted stored proc because "fnCheckSomething" is crucial and it interferes with paging/sort order as well.

Note that input to func includes:
a. Person.Id --- person is the entity we're trying to retrieve
b. OwnerId --- input param to "Stored Proc" (or FUNCTION) which contains this SELECT statement.
So, there're two sorts of param...

I suppose I need something which resembles: (1) Named query (with dynamic param, see above a and b) + (2) ICriteria which can add a *dynamic* Named-query somehow.

Thanks

wolli · **Posted:** Mon May 11, 2009 10:12 am

You could use Expression.SQL(...) in your criteria.

devvvy · **Joined:** Mon Jan 05, 2009 7:08 am **Posts:** 24

Thanks this is exactly what I needed (Although for MySQL there's no such function in where clause for me to call unfortunately ... MySQL does not support recursively function call)

Quoting REF here in case if someone else need it:
https://www.hibernate.org/hib_docs/nhib ... teria.html

There are quite a range of built-in criterion types (Expression subclasses), but one that is especially useful lets you specify SQL directly.

Code:

 // Create a string parameter for the SqlString below
        Parameter paramName = new Parameter("someName", new StringSqlType());

        IList cats = sess.CreateCriteria(typeof(Cat))
            .Add( Expression.Sql(
                new SqlString( new object[] {
                    "lower({alias}.Name) like lower(",
                    paramName,
                    ")" } ),
                "Fritz%",
                NHibernateUtil.String )
            .List();

The {alias} placeholder with be replaced by the row alias of the queried entity. 

Thanks again