Nhibernate join filtering

Question

I have a question about joins in NHIBERNATE. We had an issue with our sql query that was generated but nhibernate. Our db developer optimized the raw sql so it works as we need, but we need to change the nhibernate code to make generated sql look like optimized.

the part of the original part of the query is:

FROM   PERSON_VISIT this_
                     inner join PERSON_Basic per2_
                       on this_.PERSON_ID = per2_.PERSON_ID
                     left outer join PERSONC_QUESTIONS perint10_
                       on per2_.PERSON_ID = perint10_.PERSON_ID
                     left outer join TELEPHONE_QUESTIONS intaudit13_
                       on perint10_.PP_QUESTIONS_ID = intaudit13_.PP_QUESTIONS_ID
                     inner join C_QUESTIONS intdef14_
                       on perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and perint10_.QUESTIONS_CODE = intdef14_.QUESTIONS_CODE
                          and perint10_.QUESTION_ID = intdef14_.QUESTION_ID

The optimized one is :

FROM   PERSON_VISIT this_
                     inner join PERSON_Basic per2_
                       on this_.PERSON_ID = per2_.PERSON_ID
                     left outer join PERSONC_QUESTIONS perint10_
                       on per2_.PERSON_ID = perint10_.PERSON_ID
                     left outer join TELEPHONE_QUESTIONS intaudit13_
                       on perint10_.PP_QUESTIONS_ID = intaudit13_.PP_QUESTIONS_ID
                     left outer join C_QUESTIONS intdef14_
                       on perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and perint10_.QUESTIONS_CODE = intdef14_.QUESTIONS_CODE
                          and perint10_.QUESTION_ID = intdef14_.QUESTION_ID
                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

To change query from inner join to left outer join is easy, i changed only one line of code:

        .CreateAlias("PersonInt.QuestionEntity", "IntDef", JoinType.LeftOuterJoin)

But how I can add

                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

using nhibernate code?

There is an option to add reference from PERSON_VISIT definition to C_QUESTIONS, but the problem is that PERSON_VISIT is used everywhere and I don't want this change to possibly break other queries, I just wnat to add only one line of code to add, how I can do that? Is there any way to have access to the raw join to change it? Or some other way to add this

                          and intdef14_.DISCIPLINE_CODE = this_.DISCIPLINE_CODE

To the query? I know that somebody will say that we can add a restriction to the query through criteria.Add, but it is not an option cause db developer optimized our query taking this restriction from WHERE clause to the join.

How I can do that quickly without changing the models definitions? Just changing only this one query without changing the whole model?

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

2

It is possible using HQL and the Criteria API's.

This question gives you the answer: Adding conditionals to outer joins with nhibernate

Something like this may solve your issue.

.CreateAlias("PersonInt.QuestionEntity", "IntDef", JoinType.LeftOuterJoin, 
      Restrictions.EqProperty("DISCIPLINE_CODE", "IntDef.DISCIPLINE_CODE"))

edited May 23 '17 at 12:04

Community

1
1

answered Jul 01 '11 at 13:18

Mark Perry

1,705
10
12

this answer is great. The problem is we're using 2.0.0.4 version of nHibernate and I don't have possibility to use this approach and also the project is too big to change the version without testing all of the code, and we need quick solution for this. Maybe you know how I can do this in 2.0.0.4? Is there a possibility to manipulate raw sql that nHibernate has generated? Cause other only solution is to rewrite method using sql. – Andriy Kizym Jul 04 '11 at 15:03
Well perhaps to get the execution plan to work you could move that extra join clause and add it to the WHERE clause. Hopefully SQL server will be clever enough to promote it from the where to the join in the internal plan or give you the optimised plan you are looking for. `.Add(Restrictions.EqProperty("DISCIPLINE_CODE", "IntDef.DISCIPLINE_CODE"))` – Mark Perry Jul 04 '11 at 15:28
as I said earlier this is our performance issue with .Add(Restrictions.EqProperty("DISCIPLINE_CODE", "IntDef.DISCIPLINE_CODE")) in where clause. Don't know why but it works MUCH more faster with this restriction in join than in where clause. Two times faster. I have fixed this by writing Interceptor and manipulating with raw sql. Thanks. – Andriy Kizym Jul 04 '11 at 16:52
Sorry I can't be of more help. =( – Mark Perry Jul 04 '11 at 17:01

score 0 · Accepted Answer · answered Jul 04 '11 at 17:15

Thanks for answers. We use 2.0 version of NHibernate in our project so we didn't have a chance to use new methods of .CreateAlias with restrictions.

I have fixed an issue using Interceptors:

public class SqlInterceptor : EmptyInterceptor, IInterceptor
{
    SqlString IInterceptor.OnPrepareStatement(SqlString sql)
    {
        //manipulating with the sql

        return sql;
    }
}

than

        var factory = Session.SessionFactory;
        var session = factory.OpenSession(new SqlInterceptor());

And use my query without a change.

Nhibernate join filtering

2 Answers2