NHibernate Criteria generate invalid SQL for Sybase when using Subqueries

Question

the criteria:

var subQuery = DetachedCriteria.For<RecordInfo>();
    .Add(Restrictions.Eq("Property1", "some string"))
    .Add(Restrictions.EqProperty("Property2", "record.Id"))
    .SetProjection(Projections.Max("Id"));

var criteria = session.CreateCriteria(typeof(Record), "record")
    .CreateAlias("record.ListOfRecordInfo", "recordInfo")
    .Add(Subqueries.PropertyEq("recordInfo.Id", subQuery));

will generate something like this for Sybase ASE 15.5 dialect:

SELECT this_.Record_Id as Record1_2_3
...
FROM Record this_
INNER JOIN Record_Info Record_Info_
ON this_.Record_Id = Record_Info_.Property_2
WHERE Record_Info_.Record_Info_Id = 
(
    SELECT max(this_0_.Record_Info_Id) as y0_
    FROM Record_Info this_0_
    WHERE this_0_.Property_1 = @p0
    AND this_0_.Property_2 = this_.Record_Id
)

The problem is the as y0_ that was generated, Sybase doesn't allow alias for sub query for the version of Sybase I'm using.

How do I modify my queries so that NHibernate doesn't generate the alias for the sub query?

Answer 1

We can adjust the projection of the subquery with custom SQL statement. So instead of:

var subQuery = DetachedCriteria.For<RecordInfo>(); 
    ...
    .SetProjection(Projections.Max("Id")); // instead of this

we can do this:

var subQuery = DetachedCriteria.For<RecordInfo>(); 
    ...
    .SetProjection(Projections.SqlProjection(" MAX(RecordInfoId) " // use this
      , new string[] {"ID"}
      , new IType[] {NHibernate.NHibernateUtil.Int32}));

We have to use the column name, because we are skipping the NHibernate Property-to-Column (Id to RecordInfoId).

This will avoid the

• SELECT max(this_0_.Record_Info_Id) as y0_ replacing it with
• SELECT MAX(RecordInfoId) (our custom SQL)