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

You will receive 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):

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 build a 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 remove the orderby out of Linq and move it after of .Distinct() extension method.

var cat = (from c in db.Products   
    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 on my book, renders the whole exercise of making Linq as query-like as possible a pointless one.

And that valid Linq to SQL code is invalid in NHibernate's Linq(produces error, see below). And funny as it is, the valid NHibernate Linq is invalid in Linq to SQL, and vice versa; though Linq to SQL doesn't produce any exceptions, it's just as quite annoyance as it is, it's a silent error, the code goes to production and it has error yet you don't know it, Linq to SQL doesn't fail fast.


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 dutifully informs the programmer that if it cannot do something it won't do silent errors. It fail fast.

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

Unhandled Exception: System.NotSupportedException: Operation is not supported.
  at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.GetClassName (IASTNode querySource) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.PolymorphicQuerySourceDetector.Process (IASTNode tree) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process () [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.AstPolymorphicProcessor.Process (IASTNode ast, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators (IASTNode ast, System.String queryIdentifier, System.String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators (System.String queryIdentifier, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan.CreateTranslators (System.String expressionStr, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor (System.String expressionStr, IQueryExpression queryExpression, System.String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLExpressionQueryPlan..ctor (System.String expressionStr, IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) [0x00000] in <filename unknown>:0 
  at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan (IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) [0x00000] in <filename unknown>:0 
  at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan (IQueryExpression queryExpression, Boolean shallow) [0x00000] in <filename unknown>:0 
  at NHibernate.Impl.AbstractSessionImpl.CreateQuery (IQueryExpression queryExpression) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.PrepareQuery (System.Linq.Expressions.Expression expression, IQuery& query, NHibernate.Linq.NhLinqExpression& nhQuery) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.Execute (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at NHibernate.Linq.NhQueryProvider.Execute[IEnumerable`1] (System.Linq.Expressions.Expression expression) [0x00000] in <filename unknown>:0 
  at Remotion.Data.Linq.QueryableBase`1[System.String].GetEnumerator () [0x00000] in <filename unknown>:0 
  at System.Collections.Generic.List`1[System.String].AddEnumerable (IEnumerable`1 enumerable) [0x00000] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/corlib/System.Collections.Generic/List.cs:126 
  at System.Collections.Generic.List`1[System.String]..ctor (IEnumerable`1 collection) [0x0002f] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/corlib/System.Collections.Generic/List.cs:63 
  at System.Linq.Enumerable.ToList[String] (IEnumerable`1 source) [0x00006] in /private/tmp/monobuild/build/BUILD/mono-2.8.1/mcs/class/System.Core/System.Linq/Enumerable.cs:2851 
  at TestTwoTable.Program.PhoneListUsingLinq () [0x000ae] in /Volumes/SHARED/SharedTests/TestTwoTable/TestTwoTable/Program.cs:138 
  at TestTwoTable.Program.Main (System.String[] args) [0x00000] in /Volumes/SHARED/SharedTests/TestTwoTable/TestTwoTable/Program.cs:20 

Keyword search: nhibernate linq select distinct