Nhibernate中如何在LINQ查询中转义特殊字符

Question

I have a table like:

TABLE_XY

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

生成的查询应该是：

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

我知道有两个选项可以满足我的需求。

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

或者

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

但我必须使用基于Linq的实现。我正在使用动态创建的表达式树，有时将使用Linq to Object Provider或Linq to Nhibernate执行。但目前仅支持此方法：

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

如何扩展Nhibernate Linq Provider以支持？

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

- Matthias

Answer 1

好的，这是一个比较复杂的答案，可能存在问题，但我能够使用带有escape元素的like操作符。

这需要一些步骤，但基本上我们正在添加一种新类型的HqlTreeNode，它可以处理like运算符的escape部分。

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.

这是我第一次以这种方式扩展HQL，所以可能会存在问题。我只能在SQL Server上测试过，但是我很有信心，因为它创建了与HQL查询相同的树形结构，所以应该能够正常工作。

- Andrew Whitaker

---

不错！你的解决方案和我预期的一样！唯一的改变是：我没有创建一个新的ASTFactory，而是使用var factory = treeBuilder.Constant(null).Factory来获取内部工厂。我不确定这是否完全正确，但它可以工作。 - Matthias

Answer 2

解决方案在这里，应该会令人惊讶地简单：

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

诀窍在于使用类似于表达式的样式来表示下划线[_]。

- Radim Köhler

---

这个解决方案适用于 MS-SQL，但不适用于 Oracle 数据库。 - Matthias

很有可能是的... MS SQL Server确实支持这样的正则表达式。是的...但是老实说，我没有在Oracle上有这样的经验...抱歉。但是你仍然可以使用像**EndsWith()，StartsWith()，Contains()**这样的方法...并传递类似Oracle风格的表达式。那应该就是方法了... - Radim Köhler

很遗憾，当我执行类似于Contains("%_aa")的操作时，它会返回"val_aa"和"valaa"这样的值，这是错误的。如果我执行Contains("%\_aa")操作，则根本没有返回任何内容。我需要想办法将转义字符传递给服务器。 - Matthias

我无法测试Oracle，但这应该可以/应该工作.EndsWith（@ "_ aa"）（在SQL Server上也适用）。 - Radim Köhler

很遗憾，这种方法返回了太多的条目，因为“_”将被解释为任何字符，所以“_aa”、“1aa”或“Xaa”也将符合条件。 - Matthias