NHibernate LinqToHqlGenerator for SQL Server 2008全文索引'Containing'关键字 [英] NHibernate LinqToHqlGenerator for SQL Server 2008 full text index 'Containing' keyword

问题描述

我已经成功注册了SQL Server 2008 contains 使用此注册的自定义方言进行查询：

  RegisterFunction（contains，new StandardSQLFunction（contains ， 空值））; 
  

我只有一个类需要查询全文索引：

  public class SearchName 
 {
 public virtual Guid Id {get; set;} 
 public virtual string Name {get; set;} //这是搜索字段
} 
  

contains函数在HQL：

  var names = Session.CreateQuery（from SearchName where contains（Name，：keywords））
 .SetString（keywords，john）
 .List（）; 
  

并且生成的SQL是完美的：

  select searchname0_.Id as Id4_，
 searchname0_.Name as Name4_ $ b $ from search_Name searchname0_ 
 where contains（searchname0_.Name，'john'/ * @ p0 * /）
  

下一个挑战是实现Linq到HQL生成器：

  public class MyLinqtoHqlGeneratorsRegistry：
 DefaultLinqToHqlGeneratorsRegistry 
 {
 public MyLinqtoHqlGeneratorsRegistry（）
 {
 this.Merge（new ContainsGenerator（））; 
 
 
 $ b $ public class ContainsGenerator：BaseHqlGeneratorForMethod 
 {
 public ContainsGenerator（）
 {
 SupportedMethods = new [] {
 ReflectionHelper.GetMethodDefinition< SearchName>（d => d.Name.Contains（String.Empty））
}; 
} 
 
 public override HqlTreeNode BuildHql（MethodInfo method，
 System.Linq.Expressions.Expression targetObject，
 ReadOnlyCollection< System.Linq.Expressions.Expression> arguments， 
 HqlTreeBuilder treeBuilder，IHqlExpressionVisitor visitor）
 {
 return treeBuilder.MethodCall（contains，
 visitor.Visit（targetObject）.AsExpression（），
 visitor.Visit （arguments [0]）。AsExpression（）
）; 
 
 
 
 
 
 $ b 
调用这样的方法： 
  var namesLinq = Session.Query< SearchName>（）。其中​​（x => x.Name.Contains（john ））ToList（）; 
  
不幸的是，这似乎并没有覆盖内置的 Contains 方法，并且生成的SQL是错误的： 
 
 
  select searchname0_.Id as Id4_，
 searchname0_.Name作为Name4_ 
 from Search_Name searchname0_ 
 where searchname0_.Name like（'％'+'john'/ * @ p0 * / +'％'）
  
是否可以重写默认的 Contains 方法，还是我只是做了一个愚蠢的错误？
 
 
 
 PS  - 我正在使用NHibernate 3.3.1.4000  
 
解决方案
 < 
 
 
 
首先，我设法从我的配置中删除注册码：
  ... 
 .ExposeConfiguration（cfg => 
 {
 cfg.LinqToHqlGeneratorsRegistry< MyLinqtoHqlGeneratorsRegistry>（）; 
 ... 
} 
  
其次，不要尝试覆盖现有的Linq行为。我将Contains扩展方法移到了全文类中。
第三，正确地构建Hql树。
 
 
 
对于试图实现SQL 2008自由文本的其他人包含搜索，下面是完整的实现： 
 
 
  public static class DialectExtensions 
 {
 public static bool Contains（this SearchName sn，string searchString）
 {
 //这只是方法info的占位符。 
 //它没有其他问题。 
返回false; 
 
 
 $ b $ public class MyLinqtoHqlGeneratorsRegistry：DefaultLinqToHqlGeneratorsRegistry 
 {
 public MyLinqtoHqlGeneratorsRegistry（）
：base（）
 {
 RegisterGenerator（ReflectionHelper.GetMethod（（）=> 
 DialectExtensions.Contains（null，null）），
 new ContainsGenerator（））; 
 
 
 
 public class ContainsGenerator：BaseHqlGeneratorForMethod 
 {
 string fullTextFieldName =Name; 
 $ b $ public ContainsGenerator（）
：base（）
 {
 SupportedMethods = new [] {
 ReflectionHelper.GetMethodDefinition（（）=> 
 DialectExtensions.Contains（null，null））
}; 
} 
 
 public override HqlTreeNode BuildHql（MethodInfo method，
 System.Linq.Expressions.Expression targetObject，
 ReadOnlyCollection< System.Linq.Expressions.Expression> arguments， 
 HqlTreeBuilder treeBuilder，IHqlExpressionVisitor visitor）
 {
 //无法弄清楚如何询问模型类以获取
 //任意字段名称... 
 / /也许上面的RegisterGenerator（）调用可用于将
 //属性名称传递给ContainsGenerator构造函数？ 
 //在我们的例子中，我们只有一个全文搜索类，它的
 //全文搜索字段是Name
 HqlExpression [] args = new HqlExpression [2] {
 treeBuilder.Ident（fullTextFieldName）.AsExpression（），
 visitor.Visit（arguments [1]）。AsExpression（）
}; 
返回treeBuilder.BooleanMethodCall（contains，args）; 
 
 
 
 
 
 
 $ b 
为了达到上述目的，你必须声明并且使用您的自定义方言： 
 
 
  public class CustomMsSql2008Dialect：NHibernate.Dialect.MsSql2008Dialect 
 {
 public CustomMsSql2008Dialect ）
 {
 RegisterFunction（
contains，
 new StandardSQLFunction（contains，null）
）; 
 
 
 
 
 
 
 $ b然后你可以使用你的新的包含搜索这种方式： 
  var namesLinq = Session.Query< SearchName>（）。其中​​（x => x.Contains（john）） ）.ToList（）; 
  
 ...结果SQL非常完美！ （至少如果你只有一张桌子，你正在执行全文搜索）
 
 
 
 编辑：更新实施支持多于一个全文'包含' SEARCH PER QUERY。 
 
 
 
以下是修改后的版本： 
 
 
  public static class DialectExtensions 
 {
 public static bool FullTextContains（this string source，string pattern）
 {
 return false; 
 
 
 $ b $ public class MyLinqtoHqlGeneratorsRegistry：DefaultLinqToHqlGeneratorsRegistry 
 {
 public MyLinqtoHqlGeneratorsRegistry（）
：base（）
 {
 RegisterGenerator（ReflectionHelper.GetMethod（（）=> DialectExtensions.FullTextContains（null，null）），
 new FullTextContainsGenerator（））; 
 
 
 
 public class FullTextContainsGenerator：BaseHqlGeneratorForMethod 
 {
 public FullTextContainsGenerator（）
 {
 SupportedMethods = new [] {ReflectionHelper.GetMethod（（）=> DialectExtensions.FullTextContains（null，null））}; 
} 
 
 public override HqlTreeNode BuildHql（MethodInfo method，
 System.Linq.Expressions.Expression targetObject，
 ReadOnlyCollection< System.Linq.Expressions.Expression> arguments， 
 HqlTreeBuilder treeBuilder，IHqlExpressionVisitor visitor）
 {
 HqlExpression [] args = new HqlExpression [2] {
 visitor.Visit（arguments [0]）。AsExpression（），
 visitor.Visit（arguments [1]）。AsExpression（）
}; 
返回treeBuilder.BooleanMethodCall（contains，args）; 
 
 
 
 
 
 
 
要使用修订版本，语法略有不同：
  var namesLinq = Session.Query< SearchName>（）。其中​​（x => x.Name.FullTextContains（约翰））ToList（）; 
  
 
I think I'm missing something fundamental when implementing a LinqToHql generator class.

I've successfully registered the SQL Server 2008 contains query using a custom dialect with this registration:
RegisterFunction("contains", new StandardSQLFunction("contains", null));
I have only one class with a full text index to be queried:
public class SearchName
{
  public virtual Guid Id {get; set;}
  public virtual string Name {get; set;} // this is the search field
}
The contains function works properly in HQL:
var names = Session.CreateQuery("from SearchName where contains(Name,:keywords)")
                    .SetString("keywords", "john")
                    .List();
and the generated SQL is perfect:
select searchname0_.Id   as Id4_,
       searchname0_.Name as Name4_
from   Search_Name searchname0_
where  contains(searchname0_.Name, 'john' /* @p0 */)
The next challenge was to implement the Linq to HQL generator:
 public class MyLinqtoHqlGeneratorsRegistry :
    DefaultLinqToHqlGeneratorsRegistry
    {
        public MyLinqtoHqlGeneratorsRegistry()
        {
            this.Merge(new ContainsGenerator());
        }
    }

    public class ContainsGenerator : BaseHqlGeneratorForMethod
    {
        public ContainsGenerator()
        {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition<SearchName>(d => d.Name.Contains(String.Empty))
          };
        }

        public override HqlTreeNode BuildHql(MethodInfo method,
          System.Linq.Expressions.Expression targetObject,
          ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
          HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            return treeBuilder.MethodCall("contains",
                    visitor.Visit(targetObject).AsExpression(),
                    visitor.Visit(arguments[0]).AsExpression()
                );
        }
    }
}
Calling the method like this:
var namesLinq = Session.Query<SearchName>().Where(x=> x.Name.Contains("john")).ToList();
Unfortunately, this doesn't seem to override the built-in Contains method, and the generated SQL is wrong:
select searchname0_.Id   as Id4_,
       searchname0_.Name as Name4_
from   Search_Name searchname0_
where  searchname0_.Name like ('%' + 'john' /* @p0 */ + '%')
Is it not possible to override the default Contains method, or have I just made a silly mistake?

PS - I'm using NHibernate 3.3.1.4000
解决方案
OK, I've finally figured it out!

First, I managed to delete the registration code from my configuration:
...
.ExposeConfiguration(cfg =>
     {
        cfg.LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry>();
        ...
     }
Second, don't try to override the existing Linq behaviors.  I moved my Contains extension method to the full-text class.

Third, build the Hql tree correctly.

For others trying to implement a SQL 2008 Free-text contains search, here's the complete implementation:
public static class DialectExtensions
    {
        public static bool Contains(this SearchName sn, string searchString)
        {
            // this is just a placeholder for the method info.  
            // It does not otherwise matter.
            return false;
        }
    }

    public class MyLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
    {
        public MyLinqtoHqlGeneratorsRegistry()
            : base()
        {
            RegisterGenerator(ReflectionHelper.GetMethod(() =>
                 DialectExtensions.Contains(null, null)),
                 new ContainsGenerator());
        }
    }

    public class ContainsGenerator : BaseHqlGeneratorForMethod
    {
        string fullTextFieldName = "Name";

        public ContainsGenerator()
            : base()
        {
            SupportedMethods = new[] {
                ReflectionHelper.GetMethodDefinition(() =>
                DialectExtensions.Contains(null, null))
          };
        }

        public override HqlTreeNode BuildHql(MethodInfo method,
          System.Linq.Expressions.Expression targetObject,
          ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
          HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            // cannot figure out how to interrogate the model class to get an 
            // arbitrary field name...
            // perhaps the RegisterGenerator() call above could be used to pass a
            // property name to the ContainsGenerator constructor?
            // in our case, we only have one full text searchable class, and its
            // full-text searchable field is "Name"
            HqlExpression[] args = new HqlExpression[2] {
                 treeBuilder.Ident(fullTextFieldName).AsExpression(),
                 visitor.Visit(arguments[1]).AsExpression() 
                 };
            return treeBuilder.BooleanMethodCall("contains", args);
        }
    }
For the above to work, you must have declared and used your custom dialect:
public class CustomMsSql2008Dialect : NHibernate.Dialect.MsSql2008Dialect
{
    public CustomMsSql2008Dialect()
    {
        RegisterFunction(
            "contains",
            new StandardSQLFunction("contains", null)
            );
    }
}
Then you can use your new contains search this way:
var namesLinq = Session.Query<SearchName>().Where(x => x.Contains("john")).ToList();
... and the resulting SQL is perfect! (at least if you only have one table you're performing full-text searches on)

EDIT: UPDATED IMPLEMENTATION TO SUPPORT MORE THAN ONE FULLTEXT 'Contains' SEARCH PER QUERY.

Here's the revised version:
public static class DialectExtensions
    {
        public static bool FullTextContains(this string source, string pattern)
        {
            return false;
        }
    }

    public class MyLinqtoHqlGeneratorsRegistry : DefaultLinqToHqlGeneratorsRegistry
    {
        public MyLinqtoHqlGeneratorsRegistry()
            : base()
        {
            RegisterGenerator(ReflectionHelper.GetMethod(() => DialectExtensions.FullTextContains(null, null)),
                          new FullTextContainsGenerator());
        }
    }

    public class FullTextContainsGenerator : BaseHqlGeneratorForMethod
    {
        public FullTextContainsGenerator()
        {
            SupportedMethods = new[] { ReflectionHelper.GetMethod(() => DialectExtensions.FullTextContains(null, null)) };
        }

        public override HqlTreeNode BuildHql(MethodInfo method,
          System.Linq.Expressions.Expression targetObject,
          ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
          HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
        {
            HqlExpression[] args = new HqlExpression[2] { 
                visitor.Visit(arguments[0]).AsExpression(),
                visitor.Visit(arguments[1]).AsExpression() 
            };
            return treeBuilder.BooleanMethodCall("contains", args);
        }
    }
To use the revised version, the syntax is slightly different:
var namesLinq = Session.Query<SearchName>().Where(x => x.Name.FullTextContains("john")).ToList();


                        
这篇关于NHibernate LinqToHqlGenerator for SQL Server 2008全文索引'Containing'关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！