Write Round in NHibernate criteria

Question

I need to write this in NHibernate Criteria as a projection:

The subAlias is _not_ the root alias, so {alias} cannot replace the correct sql alias, and my problem is that other parts of the query makes the subAlias vary in the generated sql

ROUND(alias.Property / parameterValueFromMethodParameter + ", 0) 
   * parameterValueFromMethodParameter2 AS SQLAlias

This is how far (off) I got:

.Add(Projections.SqlFunction(new VarArgsSQLFunction("(", "/", ")")
     , NHibernateUtil.Int32
     , Projections.SqlFunction("round"
          , NHibernateUtil.Decimal
          , Projections.Property("subAlias.Property"))), "SQLAlias"))

This produces the following SQL code:

ROUND( subAlias3(4).Property
            )AS y1_

Does anyone have experience with projections like this? I found this patch i hibernate, but seems like it was not implemented.

Answer 1

I Solved it by writing my own SQL IProjection. With a litte help from this example.

 public SqlString ToSqlString(ICriteria criteria, int loc, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        string replacedString = Regex.Replace(
            this.sql, 
            @"{([a-zA-Z_]((\.)?[a-zA-Z0-9_])*)}", 
            m =>
                {
                    ICriteria critter = criteria.GetCriteriaByAlias(m.Groups[1].Value);
                    if (critter != null)
                    {
                        return criteriaQuery.GetSQLAlias(critter);
                    }

                    return m.Groups[0].Value;
                });

        return new SqlString(replacedString);
    }

So now I can do (In a SqlGroupProjection):

ROUND({subAlias}.XCoord / " + aggregationSize + ", 0) * " + aggregationSize + " AS SQLAlias

There are some other attempts on extending (N)Hibernate to handle this alias in raw SQL problem:

Expression.Sql should support aliases other than {alias}
Support for referencing non-root entities in Criteria SQL expressions