Add SQL query options to NHibernate query

Question

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 optimise them by adding on an OPTION (HASH JOIN) hint at the end of the SELECT statement. Is there a quick way I can do this?

I don't want to rewrite the queries using plain SQL. Well, I'd love to, but they're too complicated. But if, for example, there's an easy way I can trap and modify the SQL before it's pumped off to SQL Server, I'd be happy.

Answer 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)

Answer 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;

namespace Common.FTS
{

public class FtsHashInterceptor : EmptyInterceptor
{
    private static FtsHashInterceptor instance = new FtsHashInterceptor();

    protected FtsHashInterceptor() { }

    public static FtsHashInterceptor Instance
    {
        get { return instance; }
        set { instance = value; }
    }

    public override 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;

namespace Common.FTS
{
/// 
/// Allows for the system to pick up the audit facility which will be used to
/// audit all transactions in the system.
/// 
public class FtsHashFacility : IFacility
{
    #region IFacility Members

    public void Init(IKernel kernel, IConfiguration facilityConfig)
    {
        InterceptorFactory.Create = new InterceptorFactory.CreateInterceptor(CreateFtsHashInterceptor);
    }

    public void Terminate()
    {
        // Nothing to terminate
    }

    #endregion

    private 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:

static private IWindsorContainer _container;

protected void Application_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:

public void RegisterFacilities(IWindsorContainer container)
{
container.AddFacility("fts.support", new FtsHashFacility());
}

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