我将如何更改 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-nhibernate 以更改它在我使用时生成的 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屋！