NHibernate Forge
The official new home for the NHibernate for .NET community

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>
        <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
    </session-factory>
</hibernate-configuration>

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")
    .List();
Note the above query is HQL, so NHibernate knows about freetext and can operate with it.

Posted mar 13 2009, 10:04 a.m. by Dario Quintana

Comments

jobou wrote re: Registering FREETEXT or CONTAINS functions into a NHibernate dialect
on 06-26-2009 18:30

Hi, I have tried to use Nhibernate ( 2.1.0.1001 ) and ActiveRecord (1.0.3) to use Sql Server 2005 Full-Text Search just like you are using it but I receive an error and I cannot fix it.

I think that the SQL function are not registered because I receive the same error if I put the dialect MsSql2005Dialect to my custom dialect or my custom dialect.

ERROR :

NHibernate.QueryException : Incorrect query syntax [FROM ChronoSteril.Application.Model.Instrument as Instrument where contains(Instrument.DescriptionEnFrancais,'something')]

 ----> System.ArgumentOutOfRangeException : Index was out of range. Must be non-negative and less than the size of the collection.

Parameter name: index

at NHibernate.Hql.Classic.QueryTranslator.Compile()

at NHibernate.Hql.Classic.QueryTranslator.Compile(IDictionary`2 replacements, Boolean scalar)

at NHibernate.Engine.Query.HQLQueryPlan..ctor(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)

at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String queryString, Boolean shallow, IDictionary`2 enabledFilters)

at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String query, Boolean shallow)

at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)

C:\jbouillon\ATMS\trunk\ChronoSteril.NET\ChronoSteril.Application.IntegrationTests\Repositories\InstrumentRepositoryFixture.cs(189,0): at ChronoSteril.Application.IntegrationTests.Repositories.InstrumentRepositoryFixture.FindInstrumentFts(String searchString)

C:\jbouillon\ATMS\trunk\ChronoSteril.NET\ChronoSteril.Application.IntegrationTests\Repositories\InstrumentRepositoryFixture.cs(175,0): at ChronoSteril.Application.IntegrationTests.Repositories.InstrumentRepositoryFixture.FindFtsByDescription_avec_recherche_par_description_en_anglais_lorsque_existe_1_instrument_et_1_instruments_correspond_a_la_recherche_expect_retourne_1_instrument()

--ArgumentOutOfRangeException

at System.ThrowHelper.ThrowArgumentOutOfRangeException(ExceptionArgument argument, ExceptionResource resource)

at System.ThrowHelper.ThrowArgumentOutOfRangeException()

at System.Collections.Generic.List`1.get_Item(Int32 index)

at NHibernate.Hql.Classic.WhereParser.CloseBLOCKED EXPRESSION;

       }

   }

}

------------------------------------------------------------------

Dario Quintana wrote re: Registering FREETEXT or CONTAINS functions into a NHibernate dialect
on 07-01-2009 16:15

I see you're using the Classic-Query-Translator and it's working this feature for that case:

nhibernate.svn.sourceforge.net/.../HQLFunctionFixture.cs

If you still getting problems with this, submit a ticket to our Jira: jira.nhforge.org

mhnyborg wrote re: Registering FREETEXT or CONTAINS functions into a NHibernate dialect
on 10-26-2009 16:59

Is it possible to use CONTAINSTABLE and RANK with HQL.

The native sql goes like this:

string sql = string.Format(@"SELECT *

                                  FROM tblSite AS S

                                  inner join tblStatus ST on S.CalculatedStatusId = ST.StatusId

                                  INNER JOIN CONTAINSTABLE (tblSite, ({0}), :SearchString) AS KEY_TBL

                                  ON S.siteId = KEY_TBL.[KEY]

                                  WHERE KEY_TBL.RANK > 10

                                  ORDER BY KEY_TBL.RANK DESC", searchFields);

the tblSite and tblStatus is mapped. The problem is the KEY_TBL created from the CONTAINSTABLE function.

Hope you can help. The end goal is to have the result mapped to class Sitebase.

Powered by Community Server (Commercial Edition), by Telligent Systems