Linq to NHibernate multiple OrderBy calls

Question

I am having problems ordering more than one field in my Linq to NHibernate request. Does anyone know what might be wrong or if there is work around?

the 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 tried replacing ThenBy with several OrderBy calls. The same result. The method works fine if I comment on two ThenBy calls.

The error I get is:

  [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_5_2_, this_.Minutes_Id1_1_1_1_1 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 =?  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_1_1_1_1_1_j 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 = WH .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

Answer 1

It looks like a bug with Linq in NHybernate. One possible way to solve the problem is to convert to an array before sorting. A potentially big drawback is that you cannot limit the results using Skip () and Take () before listing, so this may not be enough 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) 

Answer 2

although I don’t think this would make a difference what would happen if you execute your linq as follows:

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