LINQ/HQL problems with CollectionFetchMode.Subselect with Skip and Take


This seems similar to NH-2296. Imagine an entity with a set of strings (think photo with a set of tags). If you set CollectionFetchMode.Subselect and then try to query using LINQ or HQL while including Skip and Take, this will result in SQL that is both illegal (references identifiers by incorrect aliases and throws an exception when executed) as well as somewhat incomplete at a higher level (does not include the TOP part as explained in NH-2296).

Show me the code


Correct queries produced by CreateCriteria

Incorrect queries produced by LINQ / HQL

Sample project

Please find the attached sample project configured to restore NHibernate from nuget and use a local database named Playground.

In closing

Did some poking around but are not familiar enough with the way NHibernate works internally to offer a good guess regarding the reason or have a shot at making a patch. All I do know is that SubselectClauseExtractor removes the TOP(2) part from the initial select, and SubselectFetch.ToSubselectString is not able to discern the alias given to photo0_.Id, but most likely the real reason is somewhere up the execution order...

Will keep looking though, and post here in case I come across something interesing.

If you think I should offer a unit test in addition to the sample project, I could help get the ball rolling and do at least that.


Microsoft SQL Server 2012




Dawid Ciecierski


Affects versions