Nhibernate - Using a column value in QueryOver.IsLike

Question

I need to execute a query that looks roughly like this in SQL:

select * from Codes where Value1 like '%' + Value2 + '%'

Is there a way to do it using FluentNHibernate and QueryOver? I was looking for something like this

Session.Query<Code>().WhereRestrictionOn(c => c.Value1).IsLike(c => c.Value2);

but unfortunately IsLike requires an object or a string value as an argument.

Answer 1

One way here would be to use just a raw SQL Criterion:

Session
    .Query<Code>()
    //.WhereRestrictionOn(c => c.Value1).IsLike(c => c.Value2);
    .Where(Expression.Sql(" Value1Column LIKE '%' + Value2Column + '%' "));

It represents the raw SQL, so we have to use column names, not mapped properties here... But for exceptional scenarios like this, this is the NHibernate extension point

Answer 2

As suggested you could use a SQL criterion. I'd put it in an extension method though so it's a bit more reusable if it's something you find yourself using every so often. Something like this...

    public static IQueryOver<TRoot, TSubType> WhereLike<TRoot, TSubType>(this IQueryOver<TRoot, TSubType> query, Expression<Func<TSubType, object>> thisLike, Expression<Func<TSubType, object>> likeThis)
    {
        var propName1 = ExpressionProcessor.FindMemberExpression(thisLike.Body);
        var propName2 = ExpressionProcessor.FindMemberExpression(likeThis.Body);
        query.Where(NHibernate.Criterion.Expression.Sql(string.Format(" {0} LIKE '%' + {1} + '%' ", propName1, propName2)));
        return query;
    }

Use like so

            var query = session.QueryOver<Code>()
                .WhereLike(thisLike => thisLike.Value1, likeThis => likeThis.Value2);