Nhibernate where substring error

Question

I have a working query like this:

SELECT this_.ID, this_.NomeFile, this_.DataGenerazioneFile, esitiopera_.ID, esitiopera_.Date, esitiopera_.ID_FileTelematico
FROM FileTelematico this_ 
LEFT OUTER JOIN EsitoOperazione esitiopera_ ON this_.ID = esitiopera_.ID_FileTelematico 
WHERE SUBSTRING (this_.NomeFile, 1, LEN(this_.NomeFile)-4)
IN ('filename1', 'filename2', 'filename3')   
ORDER BY this_.DataGenerazioneFile DESC;

and I'm trying to write the same with QueryOver

FileTelematico ft = null;
string[] nomi = {'filename1', 'filename2', 'filename3'}
var files = session.QueryOver<FileTelematico>(() => ft)                     
            .Where(() => ft.NomeFile.Substr(1, ft.NomeFile.StrLength() -4)
            .IsIn(nomi))
            .Fetch(x => x.EsitiOperazioni).Eager
            .OrderBy(() => ft.DataGenerazioneFile).Desc
            .List()

but it just end with a System.NullReferenceException

    in System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   in System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   in System.Delegate.DynamicInvokeImpl(Object[] args)
   in System.Delegate.DynamicInvoke(Object[] args)
   in NHibernate.Impl.ExpressionProcessor.FindValue(Expression expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 248
   in NHibernate.Impl.ExpressionProcessor.FindMemberProjection(Expression expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 258
   in NHibernate.Criterion.RestrictionExtensions.ProcessIsInCollection(MethodCallExpression methodCallExpression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Criterion\RestrictionsExtensions.cs:riga 138
   in NHibernate.Impl.ExpressionProcessor.ProcessCustomMethodCall(MethodCallExpression methodCallExpression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 662
   in NHibernate.Impl.ExpressionProcessor.ProcessBooleanExpression(Expression expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 620
   in NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 671
   in NHibernate.Impl.ExpressionProcessor.ProcessLambdaExpression(LambdaExpression expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 676
   in NHibernate.Impl.ExpressionProcessor.ProcessExpression(Expression`1 expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Impl\ExpressionProcessor.cs:riga 697
   in NHibernate.Criterion.QueryOver`2.Add(Expression`1 expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Criterion\QueryOver.cs:riga 755
   in NHibernate.Criterion.QueryOver`2.Where(Expression`1 expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Criterion\QueryOver.cs:riga 362
   in NHibernate.Criterion.QueryOver`2.NHibernate.IQueryOver<TRoot,TSubType>.Where(Expression`1 expression) in d:\Projects\NHibernate\nhibernate-core\src\NHibernate\Criterion\QueryOver.cs:riga 803
   in 

this will not happen if I don't use Substr but I need to use it (or Substring).

P.S. I'm using Nhibernate 4.0.3 GA

Answer 1

Unfortunately, the .SubStr extension method is not powerful enough to handle anything but constant values as arguments.

Luckily, you can get around this by using Projections.SqlFunction and calling the substring method that way instead. You'll also need to add a function that can do subtraction, since unfortunately you can't do arithmetic operations with built-in QueryOver functionality.

First, create a static class that contains your custom projections:

public static class CustomProjections
{
    public static IProjection Subtract(IProjection one, IProjection other)
    {
        return Projections.SqlFunction(
            new VarArgsSQLFunction(NHibernateUtil.Int32, "(", "-", ")"),
            NHibernateUtil.Int32,
            one,
            other);
    }

    public static IProjection SubStr(IProjection prop, IProjection start, IProjection end)
    {
        return Projections.SqlFunction(
            "substring",
            NHibernateUtil.String,
            prop,
            start,
            end);
    }

    public static IProjection Length(IProjection prop)
    {
        return Projections.SqlFunction(
            "length",
            NHibernateUtil.Int32,
            prop);
    }
}

Next, use Restrictions.In and use the custom projections, passing the result of the Length projection to SubStr:

FileTelematico ft = null;

session.QueryOver<FileTelematico>(() => ft)
    .Where(
        Restrictions.In(
            CustomProjections.SubStr(
                Projections.Property(() => ft.NomeFile),
                Projections.Constant(1),
                CustomProjections.Subtract(
                    CustomProjections.Length(
                        Projections.Property(() => ft.NomeFile)),
                        Projections.Constant(4))),
            nomi))
    .Fetch(x => x.EsitiOperazioni).Eager
    .OrderBy(() => ft.DataGenerazioneFile).Desc
    .List();

Kind of messy, but should get the job done.

You can clean this up a little if you want to by refactoring the various parts of the .Where call into variables:

FileTelematico ft = null;

IProjection length = 
    CustomProjections.Subtract(
        CustomProjections.Length(Projections.Property(() => ft.NomeFile)),
        Projections.Constant(4));

IProjection substring =
    CustomProjections.SubStr(
        Projections.Property(() => ft.NomeFile),
        Projections.Constant(1),
        length);

AbstractCriterion inRestriction =
    Restrictions.In(substring, nomi);


session.QueryOver<FileTelematico>(() => ft)
    .Where(inRestriction)
    .Fetch(x => x.EsitiOperazioni).Eager
    .OrderBy(() => ft.DataGenerazioneFile).Desc
    .List();