Linq to NHibernate multiple OrderBy calls

Question

I'm having trouble ordering by more than one field in my Linq to NHibernate query. Does anyone either know what might be wrong or if there is a work around?

Code:

IQueryable<AgendaItem> items = _agendaRepository.GetAgendaItems(location)
   .Where(item => item.Minutes.Contains(query) || item.Description.Contains(query));

int total = items.Count();

var results = items
   .OrderBy(item => item.Agenda.Date)
   .ThenBy(item => item.OutcomeType)
   .ThenBy(item => item.OutcomeNumber)
   .Skip((page - 1)*pageSize)
   .Take(pageSize)
   .ToArray();

return new SearchResult(query, total, results);

I've tried replacing ThenBy with multiple OrderBy calls. Same result. The method works great if I comment out the two ThenBy calls.

Error I'm receiving:

    [SqlException (0x80131904): Invalid column name '__hibernate_sort_expr_0____hibernate_sort_expr_1__'.
    Invalid column name '__hibernate_sort_expr_0____hibernate_sort_expr_1__'.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392

    [ADOException: could not execute query
    [ SELECT this_.Id as Id5_2_, this_.AgendaId as AgendaId5_2_, this_.Description as Descript3_5_2_, this_.OutcomeType as OutcomeT4_5_2_, this_.OutcomeNumber as OutcomeN5_5_2_, this_.Minutes as Minutes5_2_, agenda1_.Id as Id2_0_, agenda1_.LocationId as LocationId2_0_, agenda1_.Date as Date2_0_, location2_.Id as Id7_1_, location2_.Name as Name7_1_ FROM AgendaItem this_ left outer join Agenda agenda1_ on this_.AgendaId=agenda1_.Id left outer join Location location2_ on agenda1_.LocationId=location2_.Id WHERE location2_.Id = ? and (this_.Minutes like ? or this_.Description like ?) ORDER BY agenda1_.Date asc, this_.OutcomeType asc, this_.OutcomeNumber asc ]
    Positional parameters:  #0>1 #0>%Core% #0>%Core%
    [SQL: SELECT this_.Id as Id5_2_, this_.AgendaId as AgendaId5_2_, this_.Description as Descript3_5_2_, this_.OutcomeType as OutcomeT4_5_2_, this_.OutcomeNumber as OutcomeN5_5_2_, this_.Minutes as Minutes5_2_, agenda1_.Id as Id2_0_, agenda1_.LocationId as LocationId2_0_, agenda1_.Date as Date2_0_, location2_.Id as Id7_1_, location2_.Name as Name7_1_ FROM AgendaItem this_ left outer join Agenda agenda1_ on this_.AgendaId=agenda1_.Id left outer join Location location2_ on agenda1_.LocationId=location2_.Id WHERE location2_.Id = ? and (this_.Minutes like ? or this_.Description like ?) ORDER BY agenda1_.Date asc, this_.OutcomeType asc, this_.OutcomeNumber asc]]
       NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) +258
       NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) +18
       NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) +87
       NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results) +342
       NHibernate.Impl.CriteriaImpl.List(IList results) +41
       NHibernate.Impl.CriteriaImpl.List() +35
       NHibernate.Linq.CriteriaResultReader`1.List() in C:\home\dev\tools\NHibernate\NHibernateContribSrc\src\NHibernate.Linq\src\NHibernate.Linq\CriteriaResultReader.cs:22
       NHibernate.Linq.d__0.MoveNext() in C:\home\dev\tools\NHibernate\NHibernateContribSrc\src\NHibernate.Linq\src\NHibernate.Linq\CriteriaResultReader.cs:27

Rube

Answer 1

This looks to me like a bug with Linq to NHybernate. One possible workaround is to convert to an array before sorting. A potentially big downside is that you can't limit the results using Skip() and Take() before enumerating, so this may not be sufficient for you.

var results = items
   .ToArray()
   .OrderBy(item => item.Agenda.Date)
   .ThenBy(item => item.OutcomeType)
   .ThenBy(item => item.OutcomeNumber)
   .Skip((page - 1)*pageSize)
   .Take(pageSize)

Shag

Answer 2

although i dont think it'd make a difference, what happens if you do your linq like this:

(from i in items orderby i.prop1, i.prop2, i.prop3).Skip(...).Take(...).ToArray();

Noach

Yep, tried it and it ends up being parsed the same and gives the same error. – Rube

Answer 3

Turning it first into array might be an acceptable solution in case if your result set is comparatively small. But if you want to make SQL server do the job, you better do it this way:

var results = items
   .OrderBy(item => item.Agenda.Date).Asc
   .ThenBy(item => item.OutcomeType).Asc
   .ThenBy(item => item.OutcomeNumber).Asc
   .Skip((page - 1)*pageSize)
   .Take(pageSize)
   .ToArray();

You should always add Asc or Desc modifier afterwards whenever you use OrderBy or OrderByAlias method in NHibernate dialect of Linq.

Goshorn