NHibernate LINQ Add Query Hints

Question

I'm trying to add "OPTION(RECOMPILE)" to the end of some of my NHibernate queries. I found the following post:

http://www.codewrecks.com/blog/index.php/2011/07/23/use-sql-server-query-hints-with-nhibernate-hql-and-icriteria/

This describes how I can add an interceptor to append the SQL. However they are using ICriteria whereas I use LINQ to query my data. Ideally I'd like to be able to say something like:

var query = session.Query<Foo>().OptionRecompile().ToList();

I was wondering if it's possible to add an extension method to IQueryable which will inject some string into the query which I can then detect for in my interceptor. This is similar to the approach used in the article above where they add a comment and detect for that.

For further information. I've dealt with LINQ extensions before and I know you can add extension properties/methods using a HQL generator. But from my understanding this will only allow me to say:

var query = session.Query<Foo>().Where(f => f.Bar.OptionRecompile()).ToList();

This isn't ideal and seems more of a hack. I'd appreciate it if someone can help. Thanks

jvukovich jvukovich · Accepted Answer · 2016-05-04T19:07:27

I ran into this issue recently, too. We came up with a fairly decent/robust solution. The important thing is that it utilizes Rhino.Commons.LocalData to provide an execution scope.

// First part
using System;
using System.Collections;
using System.Web;
using Rhino.Commons.LocalDataImpl;

namespace Rhino.Commons
{
    /// <summary>
    /// This class is key for handling local data, data that is private
    /// to the current context, be it the current thread, the current web
    /// request, etc.
    /// </summary>
    public static class Local
    {
        static readonly ILocalData current = new LocalData();
        static readonly object LocalDataHashtableKey = new object();
        private class LocalData : ILocalData
        {
            [ThreadStatic]
            static Hashtable thread_hashtable;

            private static Hashtable Local_Hashtable
            {
                get
                {
                    if (!RunningInWeb)
                    {
                        return thread_hashtable ??
                        (
                            thread_hashtable = new Hashtable()
                        );
                    }
                    Hashtable web_hashtable = HttpContext.Current.Items[LocalDataHashtableKey] as Hashtable;
                    if(web_hashtable==null)
                    {
                        HttpContext.Current.Items[LocalDataHashtableKey] = web_hashtable = new Hashtable();
                    }
                    return web_hashtable;
                }
            }

            public object this[object key]
            {
                get { return Local_Hashtable[key]; }
                set { Local_Hashtable[key] = value; }
            }

            public void Clear()
            {
                Local_Hashtable.Clear();
            }
        }

        /// <summary>
        ///     Gets the current data
        /// </summary>
        /// <value>The data.</value>
        public static ILocalData Data
        {
            get { return current; }
        }

        /// <summary>
        ///     Gets a value indicating whether running in the web context
        /// </summary>
        /// <value><c>true</c> if [running in web]; otherwise, <c>false</c>.</value>
        public static bool RunningInWeb
        {
            get { return HttpContext.Current != null; }
        }
    }
}

// Second part
using System;
using Rhino.Commons;

namespace IDL.Core.Util.NHibernate
{
    public class NhSqlAppender : IDisposable
    {
        private static string sql;
        private int usages = 1;

        public NhSqlAppender()
        {
        }

        public NhSqlAppender(string sqlToAppend)
        {
            sql = sqlToAppend;
        }

        public static NhSqlAppender Append(string sqlToAppend)
        {
            var currentAppender = Current;

            if (currentAppender == null)
            {
                Current = new NhSqlAppender(sqlToAppend);
                currentAppender = Current;
            }
            else
                currentAppender.IncrementUsages();

            return currentAppender;
        }

        public static NhSqlAppender Current
        {
            get { return Local.Data["NhSqlAppender"] as NhSqlAppender; }
            protected set { Local.Data["NhSqlAppender"] = value; }
        }

        public static string Sql
        {
            get { return (IsValid) ? sql : string.Empty; }
        }

        public static bool AppendSql
        {
            get { return IsValid; }
        }

        public void IncrementUsages()
        {
            ++usages;
        }

        public void DecrementUsages()
        {
            --usages;
        }

        private static bool IsValid
        {
            get { return (Current != null && !string.IsNullOrWhiteSpace(sql)); }
        }

        public void Dispose()
        {
            if (usages <= 1)
                Current = null;
            else
                DecrementUsages();
        }
    }
}

// Third part
namespace IDL.Core.Util.NHibernate
{
    public class NhQueryHint : NhSqlAppender
    {
        public static NhSqlAppender Recompile()
        {
            return Append("OPTION(RECOMPILE)");
        }
    }
}

// Fourth part
using System;
using IDL.Core.Util.NHibernate;
using NHibernate;

namespace IDL.Core.Configuration
{
    [Serializable]
    public class NhSqlAppenderInterceptor : EmptyInterceptor
    {
        public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
        {
            if (NhSqlAppender.AppendSql)
                return sql.Insert(sql.Length, (" " + NhSqlAppender.Sql));

            return base.OnPrepareStatement(sql);
        }
    }
}

// Fifth part
// You need to register the interceptor with NHibernate
// cfg = NHibernate.Cfg.Configuration
cfg.SetInterceptor(new NhSqlAppenderInterceptor());

// Finally, usage
using (NhQueryHint.Recompile())
    var results = IQueryable<T>.ToList();

You'll have to modify to fit your environment. Hope it helps!

NHibernate LINQ Add Query Hints

1 Answers