CODEWITHSUNDEEP
oracle-databasenhibernatepagination
Tony
Tony

Reputation: 12715

NHibernate pagination with oracle

As always for pagination, I use

var users = session.QueryOver<User>()
                   .Skip(10)
                   .Take(50);
                   .List<User>();

but I've noticed that sql query is generated (for oracle db)

SELECT
      this_.ID as ID6_1_,
      this_.Name as Name6_1_
FROM
     Users this_

without any expressions for pagination. Why? So all Users rows will be loaded into the memory and then the pagination fires?

Upvotes: 0

Views: 148

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123901

Well, NHiberante of course supports native DB paging. With proper dialect setting - all should work as expected

In case we are using Oracle12cDialect, there is a code snippet:

namespace NHibernate.Dialect
{
    /// <summary> 
    /// A dialect specifically for use with Oracle 10g.
    /// </summary>
    /// <remarks>
    /// The main difference between this dialect and <see cref="Oracle12cDialect"/>
    /// is the use of "ANSI join syntax" here...
    /// </remarks>
    public class Oracle12cDialect : Oracle10gDialect
    {
        /// <summary>
        /// Oracle 12c 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 override SqlString GetLimitString(SqlString querySqlString, SqlString offset, SqlString limit)
        {
            var result = new SqlStringBuilder(querySqlString);

            if (offset != null)
            {
                result.Add(" OFFSET ");
                result.Add(offset).Add(" ROWS");
            }

            if (limit != null)
            {
                result.Add(" FETCH FIRST ").Add(limit).Add(" ROWS ONLY");
            }

            return result.ToSqlString();
        }
    }
}

Previous dialects used Oracle8iDialect implementation

Upvotes: 1

Related Questions