Nhibernate escapes special character in linq query

Question

Nhibernate escapes special character in linq query

I have a table like:

TABLE_XY

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

And the generated request should be:

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

I know there are two options to suit 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 am working with dynamically generated expression trees that will sometimes execute with 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 renew my Nhibernate Linq provider support?

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

+3

c # sql linq nhibernate fluent-nhibernate

Matthias 19 oct. 14 at 18:10

source to share

2 answers

The 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();

The trick is to use a ruglar-like expression style to underline [_]

+1

Radim Köhler Oct 20 '14 at 6:41

source to share

Andrew Whitaker · Accepted Answer · 2014-10-20T19:45:52+0000

Okay, this is a pretty tricky answer and there might be problems with it, but I was able to get the operator like

to work escape

.

It's a few steps, but basically we're adding a new type HqlTreeNode

that can handle a part of the escape

statement like

.

Create an extension method that you will use in LINQ queries. This method doesn't need to be implemented - we'll provide this later:

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

Create a tree HqlEscape

node that we will use to represent the escape

statement part like

:

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

Create tree HqlLikeWithEscape

node. By default, a HqlLike

node cannot handle the part escape

, 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)
    {
    }
}

Create a generator for the extension method IsLikeWithEscapeChar

we defined earlier. The answer of this class is to take the information with which the method is called and return an HQL tree structure that will eventually be converted to 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 have used the new HQL tree nodes that we defined earlier. The main disadvantage of this approach is that I had to manually create ASTFactory

and ASTTreeAdaptor

. The use of these classes is usually encapsulated internally HqlTreeBuilder

, but HqlTreeBuilder

cannot be subclassed. Would be grateful for any input on this if anyone has any advice.

Create a new LINQ to HQL generator registry. This class simply links our extension method to the HQL implementation presented in step 4:

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

Update the config to use the new one LinqToHqlGeneratorsRegistry

:

cfg.LinqToHqlGeneratorsRegistry<LinqToHqlGeneratorsRegistry>();

(Finally) use the new extension method in your request:

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

Note that you need to supply a wildcard character. It could be ironed out, but it wouldn't be easy to do.

This is the first time I've extended HQL in this way, so there might be problems with this solution. I could only test SQL Server, but I'm pretty sure it should work given that it creates the same structure as the HQL query.

Nhibernate escapes special character in linq query

More articles: