<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
   <class name="KennelFinder.City, KennelFinder" lazy="false">
      <composite-id>
         <key-property name="Name" column="City"/>
         <key-property name="State" column="Statecode"/>
      </composite-id>
   </class>

   <sql-query name="GetCityListByPartialName-CityOnly">
      <return alias="City" class="KennelFinder.City, KennelFinder"/>
      select distinct
         city, statecode
      from
         geoinformation
      where
         city like :city
      order by
         city, statecode
   </sql-query>

</hibernate-mapping>

   [Serializable]
   public class City
   {
      private string name;
      private string state;

      public string Name
      {
         get { return name; }
         set { name = value; }
      }

      public string State
      {
         get { return state; }
         set { state = value; }
      }

      public string FormattedName
      {
         get { return string.Format("{0}, {1}", name, state); }
      }

      public override bool Equals(object obj)
      {
         //Check for null and compare run-time types.
         if (obj == null || GetType() != obj.GetType()) return false;

         City city = (City)obj;
         return name.Equals(city.Name) &&
                state.Equals(city.State);
      }

      public override int GetHashCode()
      {
         return unchecked(name.GetHashCode() ^ state.GetHashCode());
      }

      public static IList<City> FindByPartialName(string partialCityName, string partialStateName, int maxCount)
      {
         if (partialStateName != null && partialStateName.Length > 0)
            return FindByPartialCityAndState(partialCityName, partialStateName, maxCount);
         else
            return FindByPartialCity(partialCityName, maxCount);
      }
      
      private static IList<City> FindByPartialCity(string partialCityName, int maxCount)
      {
         IQuery query = DbSessionContext.Current.Session.GetNamedQuery("GetCityListByPartialName-CityOnly");
         query.SetString("city", partialCityName + "%");
         query.SetMaxResults(maxCount);
         return query.List<City>();
      }

      private static IList<City> FindByPartialCityAndState(string partialCityName, string partialStateName, int maxCount)
      {
         IQuery query = DbSessionContext.Current.Session.GetNamedQuery("GetCityListByPartialName-State");
         query.SetString("city", partialCityName + "%");
         query.SetString("state", partialStateName + "%");
         query.SetMaxResults(maxCount);
         return query.List<City>();
      }
   }

exec sp_executesql N'WITH query AS (SELECT DISTINCT TOP 20 ROW_NUMBER() OVER (order by
         city, statecode) as __hibernate_row_nr__,
         city, statecode
      from
         geoinformation
      where
         city like @p0
      order by
         city, statecode) SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY __hibernate_row_nr__',N'@p0 nvarchar(8)',@p0=N'eden pr%'

exec sp_executesql N'select distinct top 20
         city, statecode
      from
         geoinformation
      where
         city like @p0
      order by
         city, statecode',N'@p0 nvarchar(8)',@p0=N'eden pr%'

   <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
      <session-factory name="KennelFinder">
         <property name="hibernate.dialect">NHibernate.Dialect.MsSql2005Dialect</property>
         <property name="hibernate.connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
         <property name="hibernate.connection.connection_string">Server=(local);initial catalog=mobyweb;Trusted_Connection=true;</property>
         <property name="hibernate.show_sql">true</property>
         <property name="hibernate.connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
         <property name="hibernate.connection.isolation">ReadCommitted</property>
         <property name="hibernate.use_proxy_validator">False</property>
         <property name="hibernate.default_schema">mobyWeb.dbo</property>
         
         <mapping assembly="KennelFinder"/>
      </session-factory>
   </hibernate-configuration>

using System;
using System.Data;
using NHibernate.SqlCommand;

namespace Common.Data.NHibernate
{
   public class MsSql2005Dialect : NHibernate.Dialect.MsSql2000Dialect
   {
      public MsSql2005Dialect()
      {
         RegisterColumnType(DbType.String, 1073741823, "NVARCHAR(MAX)");
         RegisterColumnType(DbType.AnsiString, 2147483647, "VARCHAR(MAX)");
         RegisterColumnType(DbType.Binary, 2147483647, "VARBINARY(MAX)");
      }

      /// <summary>
      /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
      /// </summary>
      /// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
      /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
      /// <param name="last">Maximum number of rows to be returned by the query</param>
      /// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
      /// <remarks>
      /// The <c>LIMIT</c> SQL will look like
      /// <code>
      ///
      /// SELECT TOP last * FROM (
      /// SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ {sort direction 1} [, __hibernate_sort_expr_2__ {sort direction 2}, ...]) as row, query.* FROM (
      ///      {original select query part}, {sort field 1} as __hibernate_sort_expr_1__ [, {sort field 2} as __hibernate_sort_expr_2__, ...]
      ///      {remainder of original query minus the order by clause}
      /// ) query
      /// ) page WHERE page.row > offset
      ///
      /// </code>
      /// </remarks>
      public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
      {
         int fromIndex = querySqlString.IndexOfCaseInsensitive(" from ");
         SqlString select = querySqlString.Substring(0, fromIndex);

         int orderIndex = querySqlString.LastIndexOfCaseInsensitive(" order by ");
         SqlString from;
         string[] sortExpressions;
         if (orderIndex > 0)
         {
            from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
            string orderBy = querySqlString.Substring(orderIndex).ToString().Trim();
            sortExpressions = orderBy.Substring(9).Split(',');
         }
         else
         {
            from = querySqlString.Substring(fromIndex).Trim();
            // Use dummy sort to avoid errors
            sortExpressions = new string[] { "CURRENT_TIMESTAMP" };
         }

         SqlStringBuilder result = new SqlStringBuilder()
            .Add("SELECT TOP ")
            .Add(last.ToString())
            .Add(" * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ");

         for (int i = 1; i <= sortExpressions.Length; i++)
         {
            if (i > 1)
               result.Add(", ");

            result.Add("__hibernate_sort_expr_")
               .Add(i.ToString())
               .Add("__");

            if (sortExpressions[i - 1].Trim().ToLower().EndsWith("desc"))
               result.Add(" DESC");
         }

         result.Add(") as row, query.* FROM (")
            .Add(select);

         for (int i = 1; i <= sortExpressions.Length; i++)
         {
            string sortExpression = sortExpressions[i - 1].Trim().Split(' ')[0];
            if (sortExpression.EndsWith(")desc", StringComparison.InvariantCultureIgnoreCase)) {
               sortExpression = sortExpression.Remove(sortExpression.Length - 4);
            }

            result.Add(", ")
               .Add(sortExpression)
               .Add(" as __hibernate_sort_expr_")
               .Add(i.ToString())
               .Add("__");
         }

         result.Add(" ")
            .Add(from)
            .Add(") query ) page WHERE page.row > ")
            .Add(offset.ToString());

         return result.ToSqlString();
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality.
      /// </summary>
      /// <value><c>true</c></value>
      public override bool SupportsLimit
      {
         get
         {
            return true;
         }
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality with an offset.
      /// </summary>
      /// <value><c>true</c></value>
      public override bool SupportsLimitOffset
      {
         get
         {
            return true;
         }
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality with an offset.
      /// </summary>
      /// <value><c>false</c></value>
      public override bool UseMaxForLimit {
         get {
            return false;
         }
      }
   }
}

   public class MsSql2005Dialect : NHibernate.Dialect.MsSql2000Dialect
   {
      public MsSql2005Dialect()
      {
         RegisterColumnType(DbType.String, 1073741823, "NVARCHAR(MAX)");
         RegisterColumnType(DbType.AnsiString, 2147483647, "VARCHAR(MAX)");
         RegisterColumnType(DbType.Binary, 2147483647, "VARBINARY(MAX)");
      }

      private string[] getSortExpression(string orderby)
      {
          int parenthesis = 0;
          List<string> orderlist = new List<string>();
          int lastcomma = 0;

          int orderbylength = orderby.Length;

          string currentchar;

          for (int i = 0; i < orderbylength; i++)
          {
              currentchar = orderby.Substring(i, 1);
              if ((currentchar == ",") && (parenthesis == 0))
              {
                  orderlist.Add(orderby.Substring(lastcomma, i - lastcomma));
                  lastcomma = i + 1;
              }

              if (currentchar == "(")
                  parenthesis++;
              if (currentchar == ")")
                  parenthesis--;
          }

          orderlist.Add(orderby.Substring(lastcomma, orderby.Length - lastcomma));

          return orderlist.ToArray();
      }

      /// <summary>
      /// Add a <c>LIMIT</c> clause to the given SQL <c>SELECT</c>
      /// </summary>
      /// <param name="querySqlString">The <see cref="SqlString"/> to base the limit query off of.</param>
      /// <param name="offset">Offset of the first row to be returned by the query (zero-based)</param>
      /// <param name="last">Maximum number of rows to be returned by the query</param>
      /// <returns>A new <see cref="SqlString"/> with the <c>LIMIT</c> clause applied.</returns>
      /// <remarks>
      /// The <c>LIMIT</c> SQL will look like
      /// <code>
      ///
      /// SELECT TOP last * FROM (
      /// SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_1__ {sort direction 1} [, __hibernate_sort_expr_2__ {sort direction 2}, ...]) as row, query.* FROM (
      ///      {original select query part}, {sort field 1} as __hibernate_sort_expr_1__ [, {sort field 2} as __hibernate_sort_expr_2__, ...]
      ///      {remainder of original query minus the order by clause}
      /// ) query
      /// ) page WHERE page.row > offset
      ///
      /// </code>
      /// </remarks>
      public override SqlString GetLimitString(SqlString querySqlString, int offset, int last)
      {

          int parenthesis = 0;

          int fromIndex = 0;

          string querystring = querySqlString.ToString();

          string currentchar;

          int querystringlength = querystring.Length;

          for (int i = 0; i < querystringlength; i++)
          {
              currentchar = querystring.Substring(i, 1);

              //if ( ((i + 6) <= querystring.Length) && (querystring.Substring(i, 6).ToLower() == " from ") && (parenthesis == 0))
              if (((i + 6) <= querystringlength) && (querystring.Substring(i, 6).IndexOf(" from ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
              {
                  fromIndex = i;
                  break;
              }
              if (currentchar == "(")
                  parenthesis++;
              if (currentchar == ")")
                  parenthesis--;

          }


         SqlString select = querySqlString.Substring(0, fromIndex);

         int orderIndex = 0;

         querystringlength = querystring.Length;

         for (int i = querystringlength - 1; i >= 0; i--)
         {
             currentchar = querystring.Substring(i, 1);
             if (((i + 10) <= querystringlength) && (querystring.Substring(i, 10).IndexOf(" order by ", StringComparison.InvariantCultureIgnoreCase) == 0) && (parenthesis == 0))
             {
                 orderIndex = i;
                 break;
             }
             if (currentchar == "(")
                 parenthesis++;
             if (currentchar == ")")
                 parenthesis--;

         }


         SqlString from;
         string[] sortExpressions;
         if (orderIndex > 0)
         {
            from = querySqlString.Substring(fromIndex, orderIndex - fromIndex).Trim();
            string orderBy = querySqlString.Substring(orderIndex).ToString().Trim();
            //sortExpressions = orderBy.Substring(9).Split(',');
            sortExpressions = getSortExpression(orderBy.Substring(9));
         }
         else
         {
            from = querySqlString.Substring(fromIndex).Trim();
            // Use dummy sort to avoid errors
            sortExpressions = new string[] { "CURRENT_TIMESTAMP" };
         }

         SqlStringBuilder result = new SqlStringBuilder()
            .Add("SELECT TOP ")
            .Add(last.ToString())
            .Add(" * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ");

         for (int i = 1; i <= sortExpressions.Length; i++)
         {
            if (i > 1)
               result.Add(", ");

            result.Add("__hibernate_sort_expr_")
               .Add(i.ToString())
               .Add("__");

            if (sortExpressions[i - 1].Trim().EndsWith("desc", StringComparison.InvariantCultureIgnoreCase))
               result.Add(" DESC");
         }

         result.Add(") as row, query.* FROM (")
            .Add(select);

         for (int i = 1; i <= sortExpressions.Length; i++)
         {

            string sortExpression = sortExpressions[i - 1].Trim();
            if (sortExpression.EndsWith("desc", StringComparison.InvariantCultureIgnoreCase)) {
               sortExpression = sortExpression.Remove(sortExpression.Length - 4);
            }

            result.Add(", ")
               .Add(sortExpression)
               .Add(" as __hibernate_sort_expr_")
               .Add(i.ToString())
               .Add("__");
         }

         result.Add(" ")
            .Add(from)
            .Add(") query ) page WHERE page.row > ")
            .Add(offset.ToString());

         bool ordering = false;
         for (int i = 1; i <= sortExpressions.Length; i++)
         {
             if (ordering == false)
             {
                 result.Add(" order by ");
                 ordering = true;
             }

             if (i > 1)
                 result.Add(", ");

             result.Add("__hibernate_sort_expr_")
                .Add(i.ToString())
                .Add("__");

             if (sortExpressions[i - 1].Trim().EndsWith("desc", StringComparison.InvariantCultureIgnoreCase))
                 result.Add(" DESC");
         }

         return result.ToSqlString();
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality.
      /// </summary>
      /// <value><c>true</c></value>
      public override bool SupportsLimit
      {
         get
         {
            return true;
         }
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality with an offset.
      /// </summary>
      /// <value><c>true</c></value>
      public override bool SupportsLimitOffset
      {
         get
         {
            return true;
         }
      }

      /// <summary>
      /// Sql Server 2005 supports a query statement that provides <c>LIMIT</c>
      /// functionality with an offset.
      /// </summary>
      /// <value><c>false</c></value>
      public override bool UseMaxForLimit {
         get {
            return false;
         }
      }
   }