LINQ to Nhibernate user defined function in where clause

Question

I'm trying to do the following:

var query =
    (from a in session.Query<A>()
    where a.BasicSearch(searchString) == true
    select a);

But it keeps giving me this exception "System.NotSupportedException"!

Any idea how to solve this?

Answer 1

It is not possible to use user-defined functions in a LINQ query. The NHibernate linq provider does not 'know' how to translate your function into SQL.

LINQ to NHibernate works by inspecting the LINQ expression that you provide at runtime, and translating what it finds in this expression tree into a regular SQL expression. Here's a good article to get some background on expression trees: http://blogs.msdn.com/b/charlie/archive/2008/01/31/expression-tree-basics.aspx

You CAN reuse predicates like this in another way however, using the techniques discussed here. (I'm not sure if this works with NHibernate however.) IF it works it would look something like this:

// this could be a static method on class A
public static Expression<Func<A, bool>> BasicSearch(string criteria)
{
    // this is just an example, of course
    // NHibernate Linq will translate this to something like 
    // 'WHERE a.MyProperty LIKE '%@criteria%'
    return a => criteria.Contains(a.MyProperty); 
}

Usage:

from a in Session.Query<A>().Where(A.BasicSearch(criteria))

UPDATE: apparently there will be issues with NHibernate. See this blog post for a version that ought to work.

Answer 2

It is possible to call your own and SQL functions, but you have to make a wrapper for them so that NHibernate knows how to translate the C# to SQL.

Here's an example where I write an extension method to get access to SQL Server's NEWID() function. You would use the same techniques to get access to any other function on your database server, built-in or user-defined.