Performing ORDER BY on DISTINCT on Linq to NHibernate(version 3)

You will received errors when doing OrderBy on Distinct result on Linq to NHibernate (NHibernate 3) at the time of this writing:

var cat = session.Query<Product>().Select(x => x.Category).Distinct().OrderBy(s => s);

Convert it to:

var cat = session.Query<Product>().Select(x => x.Category).OrderBy(s => s).Distinct();

Alternatively you can do this, which is quite neat:

var cat = 
        (from c in session.Query<Product>()
        orderby c.Category 
        select c.Category).Distinct();

Note the last two codes produces this(which is performant, heaven thanks):

select distinct category 
 from product 
 order by category asc

Not this:

select distinct category
 from 
 (select category from product
 order by category)

Be aware that if you are using Linq to SQL, the 3rd code construct cannot construct proper query (ORDER BY is omitted on generated query, silent error). Documented here: http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

He advises to move the orderby out of query to .Distinct() extension method.

var cat = (from c in session.Query<Product>()    
    select c.Category).Distinct().OrderBy(s => s);

Which leads to attaching two extension methods on the query just to make Linq to SQL emit the correct SQL. Which IMHO, renders the whole point of making Linq as query-like as possible lame.


I prefer the Linq to NHibernate approach than Linq to SQL. Not because NHibernate is database-agnostic(but it certainly adds appeal), but for the reason that it correctly informs the programmer that it cannot do something by not performing silent errors; it fail fast.

Here's the error emitted when performing OrderBy on Distinct expression:

Unhandled Exception: System.NotSupportedException: Operation is not supported.