Fluent NHibernate and MySQL cast syntax?

Question

I have a table in MySQL which has a string column 'Id' of the form "Xnnnn" where nnnn is a number.

I want to find the largest nnnn defined. So I have:

var c = s.CreateCriteria(typeof(Item))
  .AddOrder(Order.Desc(
    Projections.Cast(
       NHibernateUtil.Int32,
       Projections.SqlFunction("substring", NHibernateUtil.String, 
                               Projections.Property("Id"), 
                               Projections.Constant(2), Projections.Constant(10)))
       ))
       .SetProjection(Projections.Property("Id"))
       .SetMaxResults(1)
       .List<string>();

But NHibernate generates the SQL:

SELECT this_.Id as y0_ FROM `Item` this_ 
  ORDER BY cast(substring(this_.Id, ?p0, ?p1) as INTEGER) desc limit ?p2

Which MySQL doesn't like ... it insists on:

cast(substring(this_.Id, ?p0, ?p1) as SIGNED INTEGER) 

I've tried various types in the cast and none of them produce the right output. Everything else I need to do with the mapping works just fine.

Any suggestions?

Answer 1

It is a bug in NH. It's fixed in the 3.0 Alpha2 version. See NH-2261 and NH-2149 for reference.

I see 2 options here:

1. Native SQL Query (session.CreateSQLQuery())
2. Upgrade to NH 3.0 (remember that it's still an Alpha, quite stable though)