如何更改Linq-to-Nhibernate为特定列生成的SQL? [英] How would I alter the SQL that Linq-to-Nhibernate generates for specific columns?

问题描述

要在MariaDB 10上利用全文索引，我需要在sql字符串中使用这种新的"MATCH AGAINST"语法.

To take advantage of Full text indexing on MariaDB 10, I need to use this new "MATCH AGAINST" syntax in the sql string.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html#function_match

我认为，如果仅对于某些列，我可以重写linq-to-hhibernate来更改它在使用时生成的sql，那真的很酷

I think it would be really cool if, for certain columns only, I could override linq-to-nhibernate to change the sql it generates when I use

.Where(x => FullTextIndexedStringProperty.Contains("Some word")).ToList().

谁可以给我一些入门的一般指导?

Who can give me some general directions on how to get started?

推荐答案

这将为您提供一个非常简单的MATCH ... AGAINST子句.如果您想变得更复杂(更多参数，指定搜索修饰符)，则必须进行一些较大的更改.希望这会帮助您入门:

This will get you a very simple MATCH ... AGAINST clause. If you want to get more complex (more arguments, specifying the search modifier), you'll have to make some bigger changes. Hopefully this will get you started though:

1. 创建一个新的方言并注册一个简单的MATCH (...) AGAINST (...)函数:

public class CustomMySQLDialect : MySQLDialect
{
    public CustomMySQLDialect()
    {
        this.RegisterFunction(
            "matchagainst",
            new SQLFunctionTemplate(
                NHibernateUtil.Boolean,
                "match (?1) against (?2)"));
    }
}

在string上创建将在LINQ语句中使用的静态扩展方法:

Create a static extension method on string that you'll use in LINQ statements:

public static class LinqExtensions
{
    public static bool MatchAgainst(this string source, string against)
    {
        throw new NotImplementedException();
    }
}

创建一个新的LINQ to HQL生成器类，该类将方法与我们在自定义方言中注册的SQL函数相关联:

Create a new LINQ to HQL generator class that associates the method with the SQL function we registered in the custom dialect:

public class MatchAgainstGenerator : BaseHqlGeneratorForMethod
{
    public MatchAgainstGenerator()
    {
        this.SupportedMethods = new[]
        {
            ReflectionHelper.GetMethod(() => LinqExtensions.MatchAgainst(null, null))
        };
    }

    public override HqlTreeNode BuildHql(
        MethodInfo method,
        System.Linq.Expressions.Expression targetObject,
        ReadOnlyCollection<System.Linq.Expressions.Expression> arguments,
        HqlTreeBuilder treeBuilder,
        IHqlExpressionVisitor visitor)
    {

        return treeBuilder.BooleanMethodCall(
            "matchagainst",
            arguments.Select(visitor.Visit).Cast<HqlExpression>());
    }
}

创建自定义LinqToHqlGeneratorsRegistry:

Create a custom LinqToHqlGeneratorsRegistry:

public class MyLinqToHqlRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public MyLinqToHqlRegistry()
    {
       var generator = new MatchAgainstGenerator();
       RegisterGenerator(typeof(LinqExtensions).GetMethod("MatchAgainst"), generator);
    }
}

使用您的自定义方言以及cfg.xml文件或代码中的Linq to HQL注册表:

Use your custom dialect, and Linq to HQL registry either in your cfg.xml file or in code:

var cfg = new Configuration()
    .DataBaseIntegration(db =>
{
    db.Dialect<CustomMySQLDialect>();
})
.LinqToHqlGeneratorsRegistry<MyLinqToHqlRegistry>();

最后，在LINQ-to-NHibernate查询中使用扩展方法:

Finally, use your extension method in a LINQ-to-NHibernate query:

session.Query<Article>()
    .Where(a => a.Body.MatchAgainst("configured"))
    .ToList()
    .Dump();

这将生成如下所示的SQL:

This will generate SQL that looks like this:

select 
    userquery_0_.Id as Id51_, 
    userquery_0_.Title as Title51_, 
    userquery_0_.Body as Body51_ 
from 
    articles userquery_0_ 
where 
    match (userquery_0_.Body) against ('configured');

同样，如果您有更复杂的要求，这将无济于事.但是希望这至少是一个很好的起点.

Again, this won't help if you have more complicated requirements. But hopefully this is at least a good starting point.

如果有人对如何使这种支持变得更加复杂感到好奇，这是我认为您会遇到的问题:

In case anyone is curious about how to make this support more complex scenarios, here are the problems I think you'd run into:

• 将MATCH的参数与AGAINST的参数分开.
• 向NHibernate注册一个自定义SQL函数，该函数可以在不同位置使用任意数量的参数
• 即使解决了上述两个问题，也要创建正确的HQL.

• Separating the arguments to MATCH from those to AGAINST.
• Registering a custom SQL function with NHibernate that can take an arbitrary number of arguments in different places
• Creating the correct HQL even after solving the two issues above.

这篇关于如何更改Linq-to-Nhibernate为特定列生成的SQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！