Eager fetch on key-many-to-one relation adds inner joins to the query
Description
Environment
Attachments
is related to
Activity
Boris Drajer August 7, 2015 at 10:23 AM
Got it: attachment file KeyManyToOneInnerJoinFetchFixture-v2.cs (if anyone can delete the other one, please do). Let me know if there's anything else I can do.
Alex Zaytsev August 7, 2015 at 4:39 AM
the newly added inner join reduces the number of records returned and it doesn't initialize empty bags - both of which it did before.
can you please add tests that demonstrate this behavior?
Boris Drajer August 4, 2015 at 8:45 AM
Alexander, thank you for your time.
I myself stumbled upon and had to employ workarounds several times, so that fix is more than welcome. I don't know enough on NHibernate inner logic and can't comment on how it is supposed to behave, but it should be noted that changed the way the query works in this case: the newly added inner join reduces the number of records returned and it doesn't initialize empty bags - both of which it did before. It seems to me that its intention was different than the one achieved:
First LEFT JOIN Association INNER JOIN First INNER JOIN Other
is interpreted by SQL server as -
(First LEFT JOIN Association) INNER JOIN First INNER JOIN Other
while the idea was to do this:
First LEFT JOIN (Association INNER JOIN First INNER JOIN Other)
If these parentheses could somehow be added to the join, that would seem to me the cleanest solution (no idea, though, if that is at all supported by NH... First of all, the joins become nested this way because the ON part now has to go after the last parenthesis).
Also note that this seems to happen only for non-lazy classes and probably properties (mapped with lazy=false), so a possible workaround for anyone having this problem (and the one I'm going to employ ) is to make them lazy.
Alex Zaytsev August 4, 2015 at 2:08 AM
Thanks for so much details,
Ok, this is not a bug, but a feature (bearing in mind that is specifically asked for these inner joins to be added). So, I lowered priority to trivial and changed type to improvement. The improvement will be to add these inner joins only when needed, and do not add them if not needed.
I would like to accept the pull request.
If a class has a one-to-many reference to a second class that has key-many-to-one back reference to it, and I try to eager fetch this relation, inner joins are added to the query for all key-many-to-one references of the second class.
Mappings (shortened for brevity):
Now, a query of this type -
produces SQL like this:
Note the last two inner joins, IMO they shouldn't be there... I tried the same thing with NH 3.4.1 and there are no inner joins, so this seems to be new. If I change the second class not to have a composite key but an identity single key, the inner joins disappear.