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

Linq to SQL is not a perfect match for reporting needs

Is clean (or performant) SQL achievable in Linq to Sql?

I wanted Linq to Sql produce this code:

SELECT C.CustomerID, COUNT(O.CustomerID) AS N
FROM Customers C
LEFT JOIN Orders O ON O.CustomerID = C.CustomerID
GROUP BY C.CustomerID

Here's my first attempt:

var q = 
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y
select new { y.Key, N = y.Count(t => t.CustomerID != null) };

First, my beef with Linq is it cannot allow the natural way to write a query. For comparison expressions, it is best to put on the left side the value/expression that is varying much, the less varying ones(e.g. constants) should be on the right side. To convey the point:

int customerId = 7;
bool found = false;
for(int i = 0; i < orders.Length; ++i)
{
    if(orders[i].CustomerId == customerId)
    {
        found = true;
        break;
    }
}

Linq doesn't allow this kind of statement:

var q = from c in db.Customers
        join o in db.Orders on o.CustomerID equals c.CustomerID into sr

Writing query on this form: from c in db.Customers join o in db.Orders on c.CustomerID equals o.CustomerID into sr is akin to doing this code:

int customerId = 7;
bool found = false;
for(int i = 0; i < orders.Length; ++i)
{
    if(customerId == orders[i].CustomerId)
    {
        found = true;
        break;
    }
}

Well that code just doesn't feel natural, isn't it?


Back to the gist of this topic, is performant code achievable in Linq to SQL?

The Linq to SQL code above produces:

SELECT [t2].[CustomerID] AS [Key], (
    SELECT COUNT(*)
    FROM [Customers] AS [t3]
    LEFT OUTER JOIN [Orders] AS [t4] ON [t3].[CustomerID] = [t4].[CustomerID]
    WHERE ([t4].[CustomerID] IS NOT NULL) AND ((([t2].[CustomerID] IS NULL) AND ([t3].[CustomerID] IS NULL)) OR (([t2].[CustomerID] IS NOT NULL) AND ([t3].[CustomerID] IS NOT NULL) AND ([t2].[CustomerID] = [t3].[CustomerID])))
    ) AS [N]
FROM (
    SELECT [t0].[CustomerID]
    FROM [Customers] AS [t0]
    LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    GROUP BY [t0].[CustomerID]
    ) AS [t2]

Well, that is far cry from the simple thing we want to achieve; that is, Linq's Count(expression here) should not produce COUNT(*), it should produce COUNT(O.CustomerId), but since we cannot feed non-lambda operation on Linq's Count's parameter, we must do: select new { y.Key, N = y.Count(t => t.CustomerID != null) };. And that code produces inefficient query, look at the Sql output above.

Here's my second attempt...

var q = 
from c in db.Customers
join o in db.Orders on c.CustomerID equals o.CustomerID into sr
from x in sr.DefaultIfEmpty()
group x by c.CustomerID into y                                        
select new { y.Key, N = y.Sum(t => t.CustomerID != null ? 1 : 0 )};

...that code produces:

SELECT SUM(
    (CASE
        WHEN [t1].[CustomerID] IS NOT NULL THEN @p0
        ELSE @p1
     END)) AS [N], [t0].[CustomerID] AS [Key]
FROM [Customers] AS [t0]
LEFT OUTER JOIN [Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
GROUP BY [t0].[CustomerID]

Though cleaner and performance-wise is passable, it still leave much to be desired, it doesn't inline constants; parameters might be the way of Linq to Sql for passing values or expressions to query(be it a variable or constant), however it still doesn't have a natural feel into it. As far as I know, there's no way to make Linq-to-Sql produce this SQL code: COUNT(O.CustomerId)


Linq to Sql is good as an ORM, storage persistence, fetching data, CRUD operations; but for reporting needs, I wouldn't bet my farm on it though. I would continue using plain old combo of SP,functions,views and ADO.NET for reporting needs.