0

I have a problem with Nhibernate - it puts right join in the middle of other joins. Here is my code:

private DetachedCriteria GetBaseCriteria()
{
    return DetachedCriteria.For<Foo>("foo")
        .CreateAlias("foo.Bar", "bar")
        .CreateAlias("bar.Baz", "baz", JoinType.InnerJoin);
}

public void Method()
{
    var criteria = GetBaseCriteria();

    criteria.CreateAlias("foo.Something", "something", JoinType.RightOuterJoin);

    ...
}

And the resulting SQL is

SELECT *
FROM foo
INNER JOIN bar ON ...
RIGHT OUTER JOIN something s ON ...
INNER JOIN baz ON ...       
WHERE ...

Unfortunately, result of that query is different from the desired

SELECT *
FROM foo
INNER JOIN bar ON ...
INNER JOIN baz ON ...
RIGHT OUTER JOIN something s ON ...     
WHERE ...

I have tried to change the order of the aliases in the GetBaseCriteria - but with no luck...

JleruOHeP
  • 10,106
  • 3
  • 45
  • 71
  • Why you desire the second SQL? If you have a specific problem that need the query as you desire, maybe the Native Query is the way to go. – Dherik Apr 14 '15 at 01:21
  • The restriction is that we want to reuse that BaseCriteria but sometime we want to produce report-like result, and in my example I`m going to group by Somenthing and project Count. While as the same code is used for the plain query - without that right join. Unfortunately, those queries way too complicated for plain SQL. – JleruOHeP Apr 14 '15 at 02:05
  • Hi, Were you able to figure out how to do this? – Garuda Nov 24 '17 at 10:01
  • Hey @Garuda, if I remember correctly - unfortunately no. I think we went with a simplified query and additional logic in code – JleruOHeP Nov 26 '17 at 22:31
  • Hi @JleruOHeP, Thank you for replying to my comment. I could figure out a way to do it. Its as I have posted the answer. – Garuda Nov 27 '17 at 11:11

1 Answers1

0

In my case, I could change the order of the query formed by NHibernate just by simply rearranging relationships defined in the ***.hbm.xml file.

For e.g. if foo.hbm.xml is defined as 

<class name="***, ***" table="foo" >
...
<many-to-one name="bar">...</many-to-one>
<many-to-one name="something">...</many-to-one>
<many-to-one name="baz" >...</many-to-one>

Simply change the order of bar, something and baz as below

<class name="***, ***" table="foo" >
...
<many-to-one name="bar">...</many-to-one>
<many-to-one name="baz" >...</many-to-one>
<many-to-one name="something">...</many-to-one>
Garuda
  • 385
  • 5
  • 17