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

Question

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

Navigation

#1 by (1 votes)

2

Problem

I have a Linq query with NHibernate in which I need to concatenate the results with a separator to compare it with those of the% main_coq, is something similar to the one shown below:

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 Firebird select, I'm already trying to treat here .

Question

So I would like to know if there is any way to configure NHibernate so that in this query it does not parameterize the parameters and concatenate them in the query (In mode SQL Injection == ON, hehe), SQL getting 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?

nhibernate fluent-nhibernate

asked by anonymous 03.09.2014 / 15:04

1 answer

Is it advisable to write automated tests for the user interface? Controlling to prevent the window from opening again in MDIForm

score 1 · Accepted Answer

I also needed this, and in my view there is no direct way to resolve 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, it goes:

First, you "invent" a new extension function that will be used in your NHibernate LINQs 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 to HQL when it is used in Linq's 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);
    }
}

When you do this, you need to create a "logger" that will map the custom function ( MinhasFuncoesLinq.Concatena ) to its respective translator ( CustomGenerator ):

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

Finally (ufa), before you mount your SessionFactory , put this register up in the NHibernate settings:

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

Having made these customizations with their respective settings to take effect, you can perform:

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

I know this feature (to include custom methods) works, but I confess I do not know how NHibernate will render this new form of concatenation in specific. I think it's an alternative for you to try out possibilities.

Example taken from:

link