Obtener el máximo valor numérico de una columna tipo texto o varchar con NHibernate

El problema de obtener el máximo valor numérico de una columna varchar o texto utilizando NHibernate con SQL Server aparece cuando hay valores que no se pueden convertir con cast o convert.

Suponiendo la siguiente consulta:

SELECT max(cast(ban_codigo as INT)) FROM Banco

Siendo ban_codigo un varchar(10)

y donde tenemos los siguientes valores para dicha columna:

0000000003
0000000004
0000000055
0000000232
0000000233
0000000234
b1
b2
b3
b4

al ejecutar la consulta obtenemos:

Mens. 245, Nivel 16, Estado 1, Línea 1
Error de conversión al convertir el valor varchar 'b1' al tipo de datos int.

Para solucionar esto podemos utilizar la función isnumeric(valor) de SQL server de la siguiente manera:

SELECT max(cast(ban_codigo as INT)) FROM Banco
where isnumeric(ban_codigo) = 1

al ejecutar la consulta obtenemos:

234

Ahora bien, la función isnumeric(valor) no es soportada directamente en el dialecto MsSql2008Dialect de NHibernate, por lo cual debemos adicionar una restricción Eq que contenga como IProjection una Proyección de tipo SqlFunction que contenga una nueva StandardSQLFunction.

Projections.SqlFunction(new StandardSQLFunction("isnumeric", NHibernateUtil.String), NHibernateUtil.Boolean, Projections.Property("Nombre de Columna"))

Ej:


int maximo = (int)session.CreateCriteria(
                    typeof(Banco).ToString()).Add(
                        Restrictions.Eq(
                            Projections.SqlFunction(
                                new StandardSQLFunction("isnumeric", NHibernateUtil.String),
                                NHibernateUtil.Boolean, Projections.Property("Codigo")),
                                1))
                    .SetProjection(
                        Projections.Max(
                            Projections.Cast(
                                NHibernateUtil.Int32,
                                Projections.Property("Codigo")
                                )
                            )
                        )
                    .UniqueResult();