How to truncate a float value in NHibernate Criteria Query?

Question

We have a value stored in our database. This value is of data type float.

Our application uses NHibernate Criteria to query the database.

When querying this value, we want to filter on a value, say 66.66.

The database will contain the value 66.6666667

So, when querying, we want to truncate (not round up) the value to two places, so that if we query on 66.66, we get all records where the truncated value equals 66.66.

In SQL Server, we could use the following query to truncate the value:

CAST(ROUND(CAST([Score] AS DECIMAL (13,5)), 2, 1) AS FLOAT) = 66.66

Is there any way to do the same query using NHibernate criteria?

Answer 1

You can wrap whatever truncate logic you want in projection with Projections.SqlFunction and SQLFunctionTemplate:

//Use ?1, ?2, ?3... for arguments in template
private static readonly SQLFunctionTemplate TruncateTemplate = 
    new SQLFunctionTemplate(NHibernateUtil.Double, "CAST(ROUND(CAST( ?1 AS DECIMAL (13,5)), 2, 1) AS FLOAT)");

public static IProjection TruncateProjection(IProjection value)
{
    return Projections.SqlFunction(TruncateTemplate, NHibernateUtil.Double, value);
}

And example of usage in Criteria:

session
    .CreateCriteria<Entity>()
    .SetProjection(TruncateProjection(Projections.Property("FloatProp")))
    .Add(
        Restrictions.Eq(
            TruncateProjection(Projections.Property("FloatProp")),
            66.66))
    .List<double>();