NHibernate order by using CASE

I have run into a big issue on sorting using nhibernate hql. I have a table LICENSE which has a property USER (corresponding to a USER class) that links it to another table USERPROFILE. The USERPROFILE table has a property UserName which is nvarchar (string in USER class). This USER property of LICENSE can be null depending upon wheather the LICENSE is assigned or not.

I have to obtain a filtered list of licenses sorted on the USER property. I want all licenses with null users to be at the bottom of the list and all other license sorted alphabetically on their USER's USERNAME property. I have used following query for this:

from License license where <various filters> order by CASE WHEN User IS NULL THEN '0' ELSE User.UserName END"

All I get out of this query is list of only assigned licenses. All licenses with null USER are not returned. Can somebody please help me out here.

Also, when I use "order by CASE WHEN User IS NULL THEN '0' ELSE User.UserName END", then only those results with non null users come up, but when i use "order by CASE WHEN Us开发者_运维问答er IS NULL THEN '0' ELSE User.UserProfileId END" or simply "order by User" all users come up but their sorting order is based upon UserProfileId and not userName (because UserProfileId is the foreign key). I have used latest versions of nhibernate but without any success.

I am wondering if there even exists any way to do what i want to accomplish...

try downloading the latest version of NHibernate and the last version of fluentnhibernate (if you are using it) then use the following query: from License license where order by User.UserName END the new version of NHibernate is doing left join when using order