Is there any way to set Nhibernate not to parameterize a query?

Question

Problem

I have an appointment with Linq Nhibernate in which subquery I need to concatenate the results with a separator to compare it with the query is similar to the following:

IQueryable<string> queryList = (from t1 in Session.Query<Table1>()
                                where (from t2 in Session.Query<Table2>()
                                        where t2.Table1 == t1
                                        select t2.Table1.Id + "#").Contains(t1.Id + "#")
                                select t1.Nome);
// É possivel de alguma forma informar o NHibernate para não parametrizar o "#"? Já que no Firebird esse modo está me trazendo problemas.
IEnumerable<string> list = queryList.ToList();

That would result in a query similar to this in SQL:

select T1.ID, T1.NOME
from TABLE1 T1
where (T1.ID || @ P0) in (select (T2.TABLE1_ID  || @P1)
                          from TABLE2 T2
                          where T2.TABLE1_ID = T1.ID) 

This query executed in a Firebird database, generates the following error: Dynamic SQL Error.

This problem of concatenating parameters in the Firebird select, I’m already trying to deal with here.

Question

Then I wonder if there is some way to configure Nhibernate so that in this query it does not parameterize the parameters but concatenate them in the query (In the mode SQL Injection == ON, hehe), SQL being similar to this:

select T1.ID, T1.NOME
from TABLE1 T1
where (T1.ID || '#') in (select (T2.TABLE1_ID  || '#')
                          from TABLE2 T2
                          where T2.TABLE1_ID = T1.ID) 

Is there any way to set this up in Nhibernate?

Answer 1

I’ve needed that, too, and as far as I’m concerned, there’s no direct way to solve it.

What you can try in this case is to create a custom extension function for use in Linq. I did not test this idea - anyway, follow:

First, you "invent" a new extension function that will be used in your LINQ’s in Nhibernate when you need to concatenate with SQL constants.

public static class MinhasFuncoesLinq
{
    // Declara a função customizada com uma implementação pura para .NET
    public static string Concatena(this string str1, string str2)
    {
        return String.Concat(str1,str2)
    }
}

Next, you need to register the "converter" of your custom method for HQL when it is used in Linq’s of Nhibernate.

public class ConcatenaGenerator : BaseHqlGeneratorForMethod
{
    public ConcatenaGenerator()
    {
        SupportedMethods = new[] {ReflectionHelper.GetMethod(() => MinhasFuncoesLinq.Concatena(null, null))};
    }

    public override HqlTreeNode BuildHql(MethodInfo method, Expression targetObject, 
        ReadOnlyCollection<Expression> arguments, HqlTreeBuilder treeBuilder, IHqlExpressionVisitor visitor)
    {
        // O segredo estaria aqui! 
        // A idéia é você "driblar" a geração padrão do HQL, extraindo das 
        // expressões seus valores e forçando a concatenação com constantes...
        HqlExpression exp1 = visitor.Visit(arguments[0]).AsExpression();
        HqlExpression exp2 = visitor.Visit(arguments[1]).AsExpression();
        if (arguments[0] is ConstantExpression) {
            exp1 = treeBuilder.Constant(((ConstantExpression)arguments[0]).Value);
        }
        if (arguments[1] is ConstantExpression) {
            exp2 = treeBuilder.Constant(((ConstantExpression)arguments[1]).Value);
        }

        return treeBuilder.Concat(exp1, exp2);
    }
}

Once this is done, you need to create a "logger" that will map the custom function (MinhasFuncoesLinq.Concatena) with your respective translator (CustomGenerator):

public class CustomLinqToHqlGeneratorsRegistry: DefaultLinqToHqlGeneratorsRegistry
{
    public CustomLinqToHqlGeneratorsRegistry():base()
    {
        RegisterGenerator(ReflectionHelper.GetMethod(() => MinhasFuncoesLinq.Concatena(null, null)),
                          new ConcatenaGenerator());
    }
}

Lastly (Ufa), before you mount your SessionFactory, put this register above in Nhibernate settings:

(...)
nhConfig.Properties[Environment.LinqToHqlGeneratorsRegistry] = typeof(CustomLinqToHqlGeneratorsRegistry).AssemblyQualifiedName
(...)

Made these customizations with their respective settings to run, you can run:

IQueryable<string> queryList = (from t1 in Session.Query<Table1>()
                                where t1.Id.Concatena("#") == "123#"
                                select t1.Nome);

I know this feature (including custom methods) works, but I confess that I don’t know how Nhibernate will render this new form of concatenation in specific. I believe it’s an alternative for you to experience possibilities.

Example taken from:

• http://fabiomaulo.blogspot.com.br/2010/07/nhibernate-linq-provider-extension.html