Variable of type referenced from scope, but it is not defined. Error to compare string in QueryOver

Question

Yeah, I know that there are many questions of this subject open and answered in stackoverflow, but none solved my problem.

I'm trying to compare two strings in where clause of QueryOver, but I had the error “variable '' of type '' referenced from scope '', but it is not defined”. I don't know what I'm doing wrong and didn't find an example for my case. Can anyone help me?

That's my code:

var queryOverPaciente = _session.QueryOver(() => pacienteAlias).
    Where(() => pacienteAlias.DataCadastro.Value.IsBetween(dataInicio).And(dataFinal));


// Other things....

// the error occurs here. "Identificacao" and "MatriculaInicio" are strings 
// and I want to select all registers that "Identificacao" >= "MatriculaInicio".

queryOverPaciente = queryOverPaciente.Where(p => 
     p.Identificacao.CompareTo(filtroRelatorio.MatriculaInicio) >= 0);

Answer 1

This statement (the issue above)

queryOverPaciente = queryOverPaciente
    .Where(p => p.Identificacao.CompareTo(filtroRelatorio.MatriculaInicio) >= 0);

representing comparison defined in C# as string.CompareTo(VALUE):

• Condition Less than zero This instance precedes VALUE.
• Zero This instance has the same position in the sort order as VALUE.
• Greater than zero This instance follows VALUE.-or- VALUE is null.

so these are sort of equal:

// negative means preceeds
string.CompareTo(`**`VALUE`**`) >= 0
// we only want follows or equals
string >= VALUE

and with SQL, we can express it directly:

column >= @VALUE

Back to our NH/C# solution. We can define it like this

queryOverPaciente
    .UnderlyingCriteria
     // GeProperty will generate >=
    .Add(Restrictions.GeProperty(
        // column
        Projections.Property(nameof(pacienteAlias.Identificacao)),
        // constant
        Projections.Constant(filtroRelatorio.MatriculaInicio)
    ));

A curiosity - to handle even the NULL as stated in C# definition above, we can for example add ISNULL statement (more general and suitable COALESCE in fact)

// use the Identificacao if filter is null
var isNull = Projections.SqlFunction("Coalesce"
    , NHibernate.NHibernateUtil.String
    , Projections.Constant(filtroRelatorio.MatriculaInicio)
    , Projections.Property(nameof(pacienteAlias.Identificacao))
);

queryOverPaciente
    .UnderlyingCriteria
    .Add(Restrictions.GeProperty(
        Projections.Property(nameof(pacienteAlias.Identificacao)),
        // the ISNULL projection instead of constant
        isNull
    ));