NHibernate HQL distinct and order by

I've recently upgraded from NHibernate 1.2 to 3.1 in an old code base. I've fixed most problems b开发者_如何学运维ut I'm stuck on this one. (I can't change from HQL to another access method without lots of changes to the codebase).

This wasn't an issue in 1.2, but after the upgrade I'm getting the following problem.

I have the following HQL:

select distinct c.OwnerUser from Film c order by c.OwnerUser.UserName

which results in the error:

[SQL: select distinct user1_.Id as Id33_, user1_.ApplicationId as Applicat2_33_, user1_.UserName as UserName33_, user1_.LoweredUserName as LoweredU4_33_, user1_.MobileAlias as MobileAl5_33_, user1_.IsAnonymous as IsAnonym6_33_, user1_.LastActivityDate as LastActi7_33_, user1_.CreateDate as CreateDate33_, user1_.CountryCode as CountryC9_33_, user1_.PreferredEditionId as Preferr10_33_ from dbo.tbl_Content film0_ inner join dbo.vw_aspnet_Users_With_Id user1_ on film0_.OwnerUserId=user1_.Id, dbo.vw_aspnet_Users_With_Id user2_ where film0_.discriminator in ('film', 'webcamfilm', 'slideshow') and film0_.OwnerUserId=user2_.Id order by user2_.UserName] ---> System.Data.SqlClient.SqlException: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

This code works fine, just with a duplicate entry I don't want.

select c.OwnerUser from Film c order by c.OwnerUser.UserName

How do I write the HQL to get distinct results?

Try specifying a join

select distinct owner from Film c join c.OwnerUser owner order by owner.UserName

The error in this sql is clause order by user2_.UserName, but user2_.UserName can't be found in the select list, which happens to be a constraint for select distinct query.

Try

select distinct c.OwnerUser, c.OwnerUser.UserName from Film c order by c.OwnerUser.UserName

then iterate through resulting list and get OwnerUser as (OwnerUser)list[i][0] or similar

Assuming that the owner user has a unique id, you can use the following:

select c.OwnerUser from Film c where c.OwnerUser.id in ( select distinct c.OwnerUser.id from Film c) order by c.OwnerUser.UserName