sql-server-2008 - NHibernate LinqToHqlGenerator for SQL Server 2008 全文索引 'Containing' 关键字

Question

我想我在实现 LinqToHql 生成器类时缺少一些基本的东西。

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

RegisterFunction("contains", new StandardSQLFunction("contains", null));

我只有一个带有全文索引的类要查询:

public class SearchName
{
  public virtual Guid Id {get; set;}
  public virtual string Name {get; set;} // this is the search field
}

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_
from   Search_Name searchname0_
where  contains(searchname0_.Name, 'john' /* @p0 */)

下一个挑战是实现 Linq to HQL 生成器:

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

像这样调用方法:

var namesLinq = Session.Query<SearchName>().Where(x=> x.Name.Contains("john")).ToList();

不幸的是，这似乎并没有覆盖内置 Contains方法，并且生成的SQL是错误的:

select searchname0_.Id   as Id4_,
       searchname0_.Name as Name4_
from   Search_Name searchname0_
where  searchname0_.Name like ('%' + 'john' /* @p0 */ + '%')

是否无法覆盖默认值 Contains方法，还是我犯了一个愚蠢的错误？

PS - 我正在使用 NHibernate 3.3.1.4000

Answer 1

好吧，我终于想通了!

首先，我设法从我的配置中删除了注册码:

...
.ExposeConfiguration(cfg =>
     {
        cfg.LinqToHqlGeneratorsRegistry<MyLinqtoHqlGeneratorsRegistry>();
        ...
     }

其次，不要试图覆盖现有的 Linq 行为。我将我的 Contains 扩展方法移动到全文类。

第三，正确构建Hql树。

对于尝试实现 SQL 2008 自由文本包含搜索的其他人，这里是完整的实现:

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

为了使上述工作，您必须声明并使用您的自定义方言:

public class CustomMsSql2008Dialect : NHibernate.Dialect.MsSql2008Dialect
{
    public CustomMsSql2008Dialect()
    {
        RegisterFunction(
            "contains",
            new StandardSQLFunction("contains", null)
            );
    }
}

然后你可以这样使用你的新 contains 搜索:

var namesLinq = Session.Query<SearchName>().Where(x => x.Contains("john")).ToList();

...生成的 SQL 是完美的! (至少如果你只有一张表，你正在执行全文搜索)

编辑:更新的实现支持多个全文“包含”每个查询的搜索。

这是修改后的版本:

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

要使用修订版，语法略有不同:

var namesLinq = Session.Query<SearchName>().Where(x => x.Name.FullTextContains("john")).ToList();