CODEWITHSUNDEEP
c#linqnhibernate
Ruba
Ruba

Reputation: 877

LINQ to Nhibernate user defined function in where clause

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?

Upvotes: 3

Views: 4117

Answers (4)

Fr&#233;d&#233;ric
Fr&#233;d&#233;ric

Reputation: 9854

Declare a BasicSearch extension method. Supposing your udf is on dbo:

using NHibernate.Linq;

...

public static class CustomLinqExtensions
{
    [LinqExtensionMethod("dbo.BasicSearch")]
    public static bool BasicSearch(this string searchField, string pattern)
    {
        // No need to implement it in .Net, unless you wish to call it
        // outside IQueryable context too.
        throw new NotImplementedException("This call should be translated " +
            "to SQL and run db side, but it has been run with .Net runtime");
    }
}

Then use it on your entities:

session.Query<A>()
    .Where(a => a.SomeStringProperty.BasicSearch("yourPattern") == true);

Beware, trying to use it without referencing an entity in its usage will cause it to get evaluated with .Net runtime instead of getting it translated to SQL.

Adapt this BasicSearch example to whatever input types it has to handle. Your question was calling it directly on the entity, which does not allow your readers to know on how many columns and with which types it need to run.

Upvotes: 0

Oskar Berggren
Oskar Berggren

Reputation: 5629

Some examples to extend NHibernate LINQ:

http://fabiomaulo.blogspot.se/2010/07/nhibernate-linq-provider-extension.html

https://nhibernate.jira.com/browse/NH-3301

Upvotes: 1

Jarrett Meyer
Jarrett Meyer

Reputation: 19573

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.

Upvotes: 1

jeroenh
jeroenh

Reputation: 26782

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.

Upvotes: 7

Related Questions