linqnhibernatelinq-to-nhibernate

NHibernate LINQ Add Query Hints


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

https://www.codewrecks.com/post/old/2011/07/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


Solution

  • 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!