How to implement limit with Nhibernate and Sybase

Question

We use Nhibernate 3.3 to connect to our Sybase Ase 15 database. Everything is fine except for the non support of the limit (or top). It is implemented in sybase but not in Nhibernate. Do you have a solution?

I tried to create a CustomSybaseAse15Dialect where I change this:

     public override bool SupportsLimitOffset
{
    get { return true; }
}

public override SqlString GetLimitString(SqlString sql, SqlString offset, SqlString limit)
{    
    int insertionPoint = GetAfterSelectInsertPoint(sql);

    if (insertionPoint > 0)
    {
        SqlStringBuilder limitBuilder = new SqlStringBuilder();
        limitBuilder.Add("select");
        if (insertionPoint > 6)
        {
            limitBuilder.Add(" distinct ");
        }
        limitBuilder.Add(" top ");
        limitBuilder.Add(limit);
        if (offset != null)
        {
            limitBuilder.Add(" start at ");
            limitBuilder.Add(offset);
        }
        limitBuilder.Add(sql.Substring(insertionPoint));
        return limitBuilder.ToSqlString();
    }
    else
    {
        return sql; // unchanged
    }
}

/// <summary>
/// Copied from MsSql2000Dialect.
/// </summary>
private int GetAfterSelectInsertPoint(SqlString sql)
{
    if (sql.StartsWithCaseInsensitive("select distinct"))
    {
        return 15;
    }
    if (sql.StartsWithCaseInsensitive("select"))
    {
        return 6;
    }
    throw new NotSupportedException("The query should start with 'SELECT' or 'SELECT DISTINCT'");
}

Using the Linq2Nhibernate syntax, it works good with

Session.Query<product>().First()

limit is correctly set to 1 but if I do this

Session.Query<product>().Take(3).ToList()

limit is set to "?".

What can I do?

Answer 1

as mentioned in the previous comment there is a bug in nHibernate. It is already fixed but not yet included in an official version.

https://nhibernate.jira.com/browse/NH-3281

You can download the main source and build the dll manually from https://github.com/nhibernate/nhibernate-core .

Michael

Answer 2

For anyone finding this question recently:

This is fixed in the release version of nHibernate 4.1.

You also need to add to the dialect:

public override bool SupportsVariableLimit { get { return false; } }

to address the lack of support for placeholders in "select top X" (it defaults to whatever SupportsLimit is)