How to implement "Firebird" "Similar to" with linq nhibernate in c#

Question

How to implement "Firebird" "Similar to" keyword with LINQ to NHibernate. I need it to do search with Regular expression. Thanks.

In fact I want to do this kind of things

from entity in Session.Query<Entity>()
where entity.field.SimilarTo("[0-9]*")

to find for example numeric rows. SQL generated :

select ....
from entity e0
where e0.field similar to '[0-9]*'

So, being a member for over a year, I expect that you have read the stackoverflow guidelines? Such as this http://stackoverflow.com/help/mcve — Callum Linington, Aug 17 '16 at 08:11
What did you try? in which case show it, otherwise, go try.. come back when you've tried and got a specific question — BugFinder, Aug 17 '16 at 08:17

score 1 · Accepted Answer · edited May 23 '17 at 12:17

I found a very helpful example on stackoverflow How would I alter the SQL that Linq-to-Nhibernate generates for specific columns?

public class CustomFirebirdSQLDialect : FirebirdDialect
{
    public CustomFirebirdSQLDialect()
    {
        this.RegisterFunction("similar to",
            new SQLFunctionTemplate(
                NHibernateUtil.Boolean,
                "?1 similar to ?2"));
    }
}

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

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

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

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

public class MyLinqToHqlRegistry : DefaultLinqToHqlGeneratorsRegistry
{
    public MyLinqToHqlRegistry()
    {
        RegisterGenerator(typeof(LinqExtensions).GetMethod("SimilarTo"), new SimilarToGenerator());
    }
}

and add this to nhibernate config

config.DataBaseIntegration(db =>
{
    db.Dialect<CustomFirebirdSQLDialect>();
})
.LinqToHqlGeneratorsRegistry<MyLinqToHqlRegistry>();

Hope this help some body else.

How to implement "Firebird" "Similar to" with linq nhibernate in c#

1 Answers1