Issues with constant values in LINQ Query on NH 4.X

[ramarivera]

We've implemented a custom method for doing an InsensitiveLike comparison on LINQ queries like this:

public override HqlTreeNode BuildHql(MethodInfo method,
                                        Expression targetObject,
                                        ReadOnlyCollection<Expression> arguments,
                                        HqlTreeBuilder treeBuilder,
                                        IHqlExpressionVisitor visitor)
{
    StringMatchMode matchMode = (StringMatchMode)((ConstantExpression)arguments[2]).Value;

    var toLowerGenerator = new ToLowerGenerator();

    // We use a concat expression instead of doing in memory string concatenation
    // so the pattern can also be an Nhibernate LINQ expression.

    HqlExpression firstArgument = toLowerGenerator.BuildHql(method, arguments[0], null, treeBuilder, visitor).AsExpression();
    HqlExpression likePatternLower = toLowerGenerator.BuildHql(method, arguments[1], null, treeBuilder, visitor).AsExpression();

    switch (matchMode)
    {
        case StringMatchMode.Anywhere:
            likePatternLower = treeBuilder.Concat(treeBuilder.Constant("%"), likePatternLower, treeBuilder.Constant("%"));
            break;
        case StringMatchMode.Start:
            likePatternLower = treeBuilder.Concat(likePatternLower, treeBuilder.Constant("%"));
            break;
        case StringMatchMode.End:
            likePatternLower = treeBuilder.Concat(treeBuilder.Constant("%"), likePatternLower);
            break;
        case StringMatchMode.Exact:
        default:
            break;
    }

    return treeBuilder.Like(firstArgument, likePatternLower);
}

which we use in tests as follows:

[Test]
[TestCase("Description Starts with")]
[TestCase("DEScRIpTION START")]
[TestCase("D")]
[TestCase("")]
public void InsensitiveLike_UsingMatchModeStart_Matches(string testPattern)
{
    // Arrange
    var pattern = testPattern;
    var matchMode = StringMatchMode.Start;
    var comparisionType = StringComparison.InvariantCultureIgnoreCase;
    var descriptionNote = new Note
    {
        Description = "Description starts with 123456"
    };

    var session = InitializeSession();
    session.Save(descriptionNote);
    session.Flush();

    // Act
    var query = session.Query<Note>().Where(x => x.Description.InsensitiveLike(pattern, matchMode));
    Console.WriteLine($"*****InsensitiveLike_UsingMatchModeStart_Matches '{testPattern}':  {GetSqlStringFromQuery(query, session)}*******");

    var results = query.ToList();

    // Assert
    Assert.AreEqual(1, results.Count, "Note counts matches expected.");
    Assert.AreEqual(results.First(), descriptionNote, "Query returned corresponding note instance.");
    Assert.IsTrue(results.First().Description.StartsWith(pattern, comparisionType), "Note description starts with pattern.");
    //Assert.IsTrue(sqlString.Contains("lower("), "Query uses lowercase method.");

    EndSession(session);
}

The problem is that if we run all 4 cases of the test, one query uses the result from the last one.

I read thro the issues and found 3 that seem similar to what I'm experiencing: #1330 (NH-3673), #1363 (NH-2500) and #866 (NH-2658). I can also see that #1544 seems to fix most of these issues and that it was merged to 5.1. However we're running the latest 4.X release (4.1.2.400 according to Nuget) and we're unable to update due to other dependencies at the moment.

Is there some user code workaround I could implement in order to prevent this issue? I solved it during tests by creating a new SessionFactory on each test contrary to creating only one for all tests and just starting a new Session, but that is not possible on production code for obvious reasons.

Additionally, the GetSqlStringFromQuery method (which I copied as a test utils) shows the right SQL:

protected string GetSqlStringFromQuery<T>(IQueryable<T> queryable, ISession session)
{
    var translators = BuildQueryTranslators(queryable, session);
    return translators[0].SQLString;
}

private IQueryTranslator[] BuildQueryTranslators<T>(IQueryable<T> queryable, ISession session)
{
    var sessionImp = (ISessionImplementor)session;
    var nhLinqExpression = new NhLinqExpression(queryable.Expression, sessionImp.Factory);
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(
        nhLinqExpression,
        null,
        false,
        sessionImp.EnabledFilters,
        sessionImp.Factory);

    return translators;
}

[bahusoid]

If I got it right query with first used matchMode is cached and used for others queries. Then it's #866

That's because matchMode is considered to be a SQL parameter. I guess the best you can do with 4.x is to introduce custom sql function that takes matchMode as parameter and handles it inside SQL. Or just drop matchMode and introduce separate methods like InsensitiveStartsWith, InsesistiveEndsWith, ...

And some dirty hacks to try matchMode be embedded in cache key as constant value :
1) Try to wrap matchMode in bogus method call:

session.Query<Note>().Where(x => x.Description.InsensitiveLike(pattern, GetMatchMode(matchMode)));

public StringMatchMode GetMatchMode(StringMatchMode  mode)
{
return mode;
}

2) Try some bogus calculation in expression:

session.Query<Note>().Where(x => x.Description.InsensitiveLike(pattern,   matchMode== 0 ? matchMode: matchMode))

[ramarivera]

I tried both hacks but unfortunately, neither of them worked.
I thought about the SQL function for handling matchMode but it would be a deployment hell to make sure every environment has it, so I don't think I can go down that route.

[bahusoid]

Duplicate of #866

Sorry then. I've seen similar hacks somewhere but they were for some early versions of 5.x.
You can still use different methods instead of matchMode parameter. But it's not the place for workaround discussion - so I'm closing it.

[ramarivera]

I understand your reason for closing the issue, it's not your fault that we can't move to NH 5.x, nor that the NHibernate users group and/or stack overflow sections are not as active as the github repo.

With that said, I'm going to post one workaround I found, hoping it might help someone in a similar position (we haven't decided to go with this workaround yet, but so far it passes all our unit tests so that's a win):

public override HqlTreeNode BuildHql(MethodInfo method,
                                        Expression targetObject,
                                        ReadOnlyCollection<Expression> arguments,
                                        HqlTreeBuilder treeBuilder,
                                        IHqlExpressionVisitor visitor)
{
    var toLowerGenerator = new ToLowerGenerator();

    HqlExpression firstArgument = toLowerGenerator.BuildHql(method, arguments[0], null, treeBuilder, visitor).AsExpression();
    HqlExpression likePatternLower = toLowerGenerator.BuildHql(method, arguments[1], null, treeBuilder, visitor).AsExpression();
    HqlExpression matchConstant = visitor.Visit(arguments[2]).AsExpression();

    return treeBuilder.Like(
        firstArgument,
        treeBuilder.Case(new[]
        {
            treeBuilder.When(
                treeBuilder.Equality(matchConstant, treeBuilder.Constant((int)StringMatchMode.Start)),
                treeBuilder.Concat(likePatternLower, treeBuilder.Constant("%"))),
            treeBuilder.When(
                treeBuilder.Equality(matchConstant, treeBuilder.Constant((int)StringMatchMode.End)),
                treeBuilder.Concat(treeBuilder.Constant("%"), likePatternLower)),
            treeBuilder.When(
                treeBuilder.Equality(matchConstant, treeBuilder.Constant((int)StringMatchMode.Anywhere)),
                treeBuilder.Concat(treeBuilder.Constant("%"), likePatternLower, treeBuilder.Constant("%")))
        },
        likePatternLower));
}

This way (at least so far) we haven't seen caching issues and the HQL gets translated (as you might expect) into something like this:

select
        note0_.NoteId as NoteId1_1_,
        note0_.Description as Descri2_1_ 
    from
        Note note0_ 
    where
        lower(note0_.Description) like case 
            when @p0=1 then (lower(@p1)||'%') 
            when @p0=2 then ('%'||lower(@p1)) 
            when @p0=0 then ('%'||lower(@p1)||'%') 
            else lower(@p1) 
        end;
    @p0 = 
End [Type: Int32 (0:0:0)], @p1 = '123456' [Type: String (0:0:0)]

Thank you @bahusoid for giving me the idea to try this out!

[bahusoid]

Yes it should work. So basically just keep in mind that in 4.x you can't change generated SQL by code depending on constant parameter values from LINQ expression. You must do all such parameters calculations in SQL - which you did. Yeah good idea.