Add Sql Query Options To Nhibernate Query

I'm working with some code that generates ICriteria queries for NHibernate. They get executed using ActiveRecord and ActiveRecordMediator.FindAll() For certain queries, I need to

Solution 1:

Implement an IInterceptor and do your modifications in OnPrepareStatement(). Then pass your interceptor to ISessionFactory.OpenSession().

Here's an example.

Or you could try registering a custom function in your dialect. (example)

Solution 2:

While answer of Mauricio Scheffer is extremly useful I've desided to extend it with working sample for implementing Interceptor to be used with NHibernate and Castle Active Records.

The Interceptor

using NHibernate;
using NHibernate.SqlCommand;

namespaceCommon.FTS
{

publicclassFtsHashInterceptor : EmptyInterceptor
{
    privatestatic FtsHashInterceptor instance = new FtsHashInterceptor();

    protectedFtsHashInterceptor() { }

    publicstatic FtsHashInterceptor Instance
    {
        get { return instance; }
        set { instance = value; }
    }

    publicoverride SqlString OnPrepareStatement(SqlString sql)
    {
        return sql.Replace("inner join Product fts1_", "inner hash join Product fts1_");
    }
}
}

Wiring up the Interceptor with a Facility

using Castle.ActiveRecord.Framework;
using Castle.Core.Configuration;
using Castle.MicroKernel;
using NHibernate;

namespaceCommon.FTS
{
////// Allows for the system to pick up the audit facility which will be used to/// audit all transactions in the system.///publicclassFtsHashFacility : IFacility
{
    #region IFacility MemberspublicvoidInit(IKernel kernel, IConfiguration facilityConfig)
    {
        InterceptorFactory.Create = new InterceptorFactory.CreateInterceptor(CreateFtsHashInterceptor);
    }

    publicvoidTerminate()
    {
        // Nothing to terminate
    }

    #endregionprivate IInterceptor CreateFtsHashInterceptor()
    {
        return FtsHashInterceptor.Instance;
    }
}
}

The class above creates a Facility for Active Record. We wire this up in the Global.asax.cs file like so:

staticprivate IWindsorContainer _container;

protectedvoidApplication_Start(object sender, EventArgs e)
{
try
{
    _container = new WindsorContainer(Server.MapPath("~/config/windsor.config"));
    var app = _container.Resolve();
    app.RegisterFacilities(_container);
    app.RegisterComponents(_container);
}
}

In the Application.cs file we add the facility as such:

publicvoidRegisterFacilities(IWindsorContainer container){
container.AddFacility("fts.support", newFtsHashFacility());
}

Conclusion The container now contains the facility which will wire up the Full Text Search interceptor which will intercept all ActiveRecordMediator calls.

We have not changed a line of code in our existing system, yet we have added the ability to analyse all of our SQL Request operations in a simple yet effective manner.

Special Thanks to Donn Felker