CODEWITHSUNDEEP
nhibernate
tlum
tlum

Reputation: 933

NHibernate: DB2400Dialect: Dialect does not support variable limits

I'm working with a S#arp Architecture project that includes some database Tasks that have worked in the past. Specifically:

var principals = _principalTasks.GetAll().AsPagination(page, limit);

where the task is defined as:

    public IQueryable<Principal> GetAll()
    {
        return _principalRepository.FindAll().OrderBy(o => o.PrincipalName.ToLower());
    }

This is effectively using NHibernate.Linq.

This is using the DB2400Dialect. Now it throws:

System.NotSupportedException: Dialect does not support variable limits.
  at NHibernate.Dialect.Dialect.GetLimitString(SqlString queryString, Nullable`1 offset, Nullable`1 limit, Parameter offsetParameter, Parameter limitParameter)
  at NHibernate.Hql.Ast.ANTLR.SqlGenerator.GetSqlStringWithLimitsIfNeeded(QueryWriter queryWriter)
  at NHibernate.Hql.Ast.ANTLR.SqlGenerator.EndQuery()
  at NHibernate.Hql.Ast.ANTLR.SqlGenerator.selectStatement()
  at NHibernate.Hql.Ast.ANTLR.SqlGenerator.statement()
  at NHibernate.Hql.Ast.ANTLR.HqlSqlGenerator.Generate()
  .
  .
  .

It looks like the SQLGenerator insists on parameterizing the skip and take parameters which this dialect does not support.

Is there a way around this or is this an NHibernate bug?

EDIT:

BTW, this is the Expression Debug string from the NHibernate.Linq.DefaultQueryProvider call:

.Call System.Linq.Queryable.Take(
    .Call System.Linq.Queryable.Skip(
        .Call System.Linq.Queryable.OrderBy(
            .Constant<NHibernate.Linq.NhQueryable`1[SolutionExample.Domain.Principal]>(NHibernate.Linq.NhQueryable`1[SolutionExample.Domain.Principal]),
            '(.Lambda #Lambda1<System.Func`2[SolutionExample.Domain.Principal,System.String]>)),
        0),
    25)

.Lambda #Lambda1<System.Func`2[SolutionExample.Domain.Principal,System.String]>(SolutionExample.Domain.Principal $o) {
    .Call ($o.PrincipalName).ToLower()
}

Upvotes: 2

Views: 1057

Answers (1)

tlum
tlum

Reputation: 933

After much research I've decided that while I could solve this question by either creating my own custom dialect that implements - or extending the existing DB2400Dialect to implement -

public SqlString GetLimitString(SqlString queryString, int? offset, int? limit, Parameter offsetParameter, Parameter limitParameter)

that would be pointless since while the iSeries allows a limit with the

... FETCH FIRST n ROWS ONLY

syntax, it has no equivalent syntax for doing an offset... so, there isn't much point to fixing the broken bits.

Upvotes: 3

Related Questions