How to order a NHibernate 3.0 Linq query based on a supplied property name

3
votes

I'm trying to order my NHibernate 3.0 Linq query dynamically, based on a column name stored in a string variable.

// The value of this variable can be the name of any property of Document.
string columnName = "column1";

var query = from n in Session.Query<Document>()
            where n.DocumentNumber == documentNumber
            // how to order by the value of columnName?
            select n;

The orderby keyword does accept a string (or variable) but when I execute the following:

var query = from n in Session.Query<Document>()
            where n.DocumentNumber == documentNumber
            orderby columnName
            select n;

I get this exception:

could not execute query

select TOP (@p0)
  accumulate0_.Id as Id9_, 
  accumulate0_.DocumentNumber as Documen10_9_
from dbo.Documents accumulate0_
where
  accumulate0_.DocumentNumber=@p1
order by @p2 desc

The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

I know there's the LINQ Dynamic Query Library which provides an overload of the .OrderBy extention method that accepts a string but that obviously only works in memory. It throws an exception when using NHibernate 3.0 however.

I'm trying to have the ORDER BY statement in the generated SQL query have the appropriate column name specified, so I need to stay in the NHibernate realm.

Using NHibernate criteria, I can order dynamically but since I'm using NHibernate Linq, I don't seem to have access to criteria features.

2
.netnhibernatelinq-to-nhibernate

2 Answers

4
votes

Dynamic LINQ works with any source, not just in memory.

That's what we are using right now.

1
votes

but since I'm using NHibernate Linq, I don't seem to have access to criteria features.

Surely Session. should bring up CreateCriteria? NHibernate Linq and CreateCriteria should live quite happier together.

Not sure if is it possible using linq alone, also I don't think it is possible using QueryOver but one way may be to use CreateCriteria as you have pointed out.

var query = Session
    .CreateCriteria<Document>()
    .Add(Restrictions.Eq("DocumentNumber", documentNumber))
    .AddOrder(Order.Asc("column1"))
    .List<Document>();

Another way would be to use HQL.