The object-relational mapper for .NET

Registering FREETEXT or CONTAINS functions into a NHibernate dialect

Ms Sql Server FREETEXT and CONTAINS functions are used into FullText search capabilities to querying. These functions are of course natives to this particular RDBMS and even comes a with particular structure: they don’t return a value. So far, till NH 2.0, you couldn’t do it because a little parser issue, but from NHibernate 2.1 in forward you’re enable register them.

First of all, we define the new dialect with the new functions in order that when we run a query, NHibernate can recognize those functions and can transform to native-sql, in this case, Transact-SQL.

using NHibernate.Dialect;
using NHibernate.Dialect.Function;

namespace MyCompany.Data
    public class MyDialect : MsSql2008Dialect
        public MyDialect()
            RegisterFunction("freetext", new StandardSQLFunction("freetext", null));
            RegisterFunction("contains", new StandardSQLFunction("contains", null));

Note that we are using StandardSQLFunction, we can also use SQLFunctionTemplate or implement our ISQLFunction class with all the constraints (ie: parameter number accepted) we need.

Once our new dialect is ready let’s call it from our hibernate.cfg.xml file, then NHibernate can know that this dialect will be inject. Suppose MyDialect is placed into the assembly MyCompany.Data, so the configuration file should look like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory name="NH">        
        <property name="dialect">MyCompany.Data.MyDialect, MyCompany.Data</property>
        <property name="connection.connection_string">
            Data Source=(local)\sqlexpress;Initial Catalog=test;Integrated Security = true
        <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>

Everything is ready, you just have to do this 2 steps and the functions are ready to use it, then we can query using them.

session.CreateQuery("from Documento where freetext(Texto,:keywords)")
    .SetString("keywords","hey apple car")
Note the above query is HQL, so NHibernate knows about freetext and can operate with it.

Posted Fri, 13 March 2009 03:04:00 AM by darioquintana
Filed under: SQL Server, NH2.1, Dialect, Function

comments powered by Disqus
© NHibernate Community 2024