Nhibernate escaping special character within linq query

Question

I have a table like:

TABLE_XY

|ID |SOURCE|
|1  |value_aa|
|2  |other_aa|
|3  |eeeaa|  

And the generated query should be:

select * from TABLE_XY where SOURCE like '%\_aa' ESCAPE '\'​

I know that there are two options which would fit my needs

var result = 
            session.QueryOver<TableXy>()
            .WhereRestrictionOn(x => x.Source)
            .IsLike("%\_aa", MatchMode.Exact, '\\')
            .List();

or

var result2 = session
     .CreateCriteria<TableXy>()
     .Add(LikeExpression("Source", "%\\_aa", MatchMode.Exact, '\\', false))
     .List(); 

But I have to use a Linq based implementation. I'm working with dynamically created expression trees, which sometimes will be executed with the Linq to Object Provider or Linq to Nhibernate. But currently only this method is supported:

  var result = session
       .Query<TableXy>()
       .Where(x => NHibernate.Linq.SqlMethods.Like(x.Source, "%\\_aa"))
       .ToList();

How can I extend the Nhibernate Linq Provider to support?

SqlMethods.IsLike(string source, string pattern, char? escape);

Answer 1

Okay, this is a pretty involved answer and there may very well be problems with it, but I was able to get a like operator with an escape piece working.

This involves a few steps, but basically what we're doing is adding a new type of HqlTreeNode that can handle the escape portion of the like operator.

1. Create an extension method that you'll use in LINQ queries. This method does not need an implementation--we'll provide that later:

   public static class LinqExtensions
   {
       public static bool IsLikeWithEscapeChar(
           this string input,
           string like,
           char? escapeChar)
       {
           throw new NotImplementedException();
       }
   }
   

2. Create an HqlEscape tree node that we will use to represent the escape portion of the like operator:

   public class HqlEscape : HqlExpression
   {
       public HqlEscape(IASTFactory factory, params HqlTreeNode[] children)
           : base(HqlSqlWalker.ESCAPE, "escape", factory, children)
       {
       }
   }
   

3. Create an HqlLikeWithEscape tree node. The default HqlLike node cannot handle the escape part, so we need to create a new node that can handle three children:

   public class HqlLikeWithEscape : HqlBooleanExpression
   {
       public HqlLikeWithEscape(IASTFactory factory, HqlExpression lhs, HqlExpression rhs, HqlEscape escape)
           : base(HqlSqlWalker.LIKE, "like", factory, lhs, rhs, escape)
       {
       }
   }
   

4. Create a generator for the IsLikeWithEscapeChar extension method we defined earlier. This class' responsibility is to take the information the method is invoked with and return an HQL tree structure that will ultimately be turned into SQL:

   public class CustomLikeGenerator : BaseHqlGeneratorForMethod
   {
       public CustomLikeGenerator()
       {
           this.SupportedMethods = new[]
           {
               ReflectionHelper.GetMethodDefinition(
                   () => LinqExtensions.IsLikeWithEscapeChar(null, null, null))
           };
       }
   
       public override HqlTreeNode BuildHql(
           MethodInfo method,
           System.Linq.Expressions.Expression targetObject,
           ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
           HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
       {
           // Is there a better way to do this?
           var factory = new ASTFactory(new ASTTreeAdaptor());
           HqlTreeNode escapeCharNode = visitor.Visit(arguments[2]).AsExpression();
           var escapeNode = new HqlEscape(factory, escapeCharNode);
   
           HqlLikeWithEscape likeClauseNode =
               new HqlLikeWithEscape(
                   factory,
                   visitor.Visit(arguments[0]).AsExpression(),
                   visitor.Visit(arguments[1]).AsExpression(),
                   escapeNode);
   
           return likeClauseNode;
       }
   }
   

   As you can see, we've utilized the new HQL tree nodes we defined earlier. The major downside to this approach is that it required me to manually create an ASTFactory and ASTTreeAdaptor. The use of these classes is usually encapsulated inside of HqlTreeBuilder, but HqlTreeBuilder doesn't lend itself to being subclassed. Would appreciate some input on this if someone has some advice.

5. Create a new LINQ to HQL generators registry. This class just just associates our extension method with the HQL implementation we provided in step 4:

   public class LinqToHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
   {
       public LinqToHqlGeneratorsRegistry() : base()
       {
           RegisterGenerator(
               ReflectionHelper.GetMethodDefinition(() => LinqExtensions.IsLikeWithEscapeChar(null, null, null)),
               new CustomLikeGenerator());
       }
   }
   

6. Update your configuration to use the new LinqToHqlGeneratorsRegistry:

   cfg.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();
   

7. (Finally) use your new extension method in a query:

   session.Query<Person>().Where(p => p.FirstName.IsLikeWithEscapeChar("%Foo", '\\'))
   

   Note that you need to specify the wildcard character. This could be smoothed out, but that wouldn't be too hard to do.

This is the first time I've extended HQL this way so again, there could be issues with this solution. I was only able to test on SQL Server, but I'm reasonable confident it should work given that it creates the same tree structure that an HQL query does.

Answer 2

Solution here, should be surprisingly simple:

var result = session
    .Query<TableXy>()

    // instead of this
    //.Where(x => NHibernate.Linq.SqlMethods.Like(x.Source, "%\\_aa"))

    // This will add sign % at the beginning only
    .Where(x => x.Source.EndsWith("[_]aa"));
    // or wrap it on both sides with sign: % 

    .Where(x => x.Source.Contains("[_]aa"));
    .ToList();

Trick is to use ruglar like expression style for underscore [_]